How to use XLOOKUP with OR logic in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None")Add the tests and check for at least one match: =XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None").
- Result on the example
- T-303 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
Adding two tests is OR: each row gets 1 if one test is true and 2 if both are. >0 turns any match into TRUE, and XLOOKUP returns the first such ticket. T-303 is escalated, which is enough even though its priority is Low.
=XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None")Result"T-303"
B2:B98 rows × 1 col: "Low", "Medium", "Low", "High", …A range: 8 cells in one column.
G1"High"The value in G1.
B2:B9=G1{8 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
C2:C98 rows × 1 col: "Open", "Open", "Escalated", "Open", …A range: 8 cells in one column.
I1"Escalated"The value in I1.
C2:C9=I1{8 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
B2:B9=G1)+(C2:C9=I1{8 rows × 1 col} 0, 0, 1, 1, …The + operator adds.
B2:B9=G1)+(C2:C9=I1)>0{8 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The > operator tests greater than.
A2:A98 rows × 1 col: "T-301", "T-302", "T-303", "T-304", …A range: 8 cells in one column.
XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None")"T-303"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
B2:B9=G1)+(C2:C9=I1)>0{8 rows × 1 col} FALSE, FALSE, TRUE, TRUE, … - return_array
A2:A98 rows × 1 col: "T-301", "T-302", "T-303", "T-304", … - if_not_found
"None"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None"). We ran it in Google Sheets mode on the same data and it returns T-303.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A9,MATCH(TRUE,INDEX((B2:B9=G1)+(C2:C9=I1)>0,0),0)) | T-303 | Every Excel version, without Ctrl+Shift+Enter. |
| =FILTER(A2:A9,(B2:B9=G1)+(C2:C9=I1)) | spills 4 × 1Different result | Lists every ticket that meets either condition. All four tickets: T-303, T-304, T-306 and T-308. |
Common mistakes
- The OR function doesn't work here:
OR(B2:B9=G1,C2:C9=I1)returns one TRUE for the whole range, not one per row. - Looking up 1 instead of TRUE in the sum misses rows where both tests are true, because they add up to 2.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Use XLOOKUP with OR logic
G2, return the first ticket that has the priority in G1 or the status in I1.Questions
How do I combine AND and OR?
Multiply for AND and add for OR, with brackets: ((B2:B9="High")+(C2:C9="Escalated")>0)*(D2:D9="Billing").
How do I match a column against one of several values?
Use COUNTIF with a list: =XLOOKUP(TRUE,COUNTIF(K2:K4,B2:B9)>0,A2:A9).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.