# How to use XLOOKUP with OR logic in Excel

Add the tests and check for at least one match: `=XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None")`.

**Excel:** `=XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Ticket | Priority | Status |  |  | Priority | High | Status | Escalated |
| T-301 | Low | Open |  |  | First ticket |  |  |  |
| T-302 | Medium | Open |  |  |  |  |  |  |
| T-303 | Low | Escalated |  |  |  |  |  |  |
| T-304 | High | Open |  |  |  |  |  |  |
| T-305 | Medium | Closed |  |  |  |  |  |  |
| T-306 | High | Escalated |  |  |  |  |  |  |
| T-307 | Low | Closed |  |  |  |  |  |  |
| T-308 | Medium | Escalated |  |  |  |  |  |  |

Formula in G2: `=XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None")`

Result: T-303

## 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.

## Other ways

- `=INDEX(A2:A9,MATCH(TRUE,INDEX((B2:B9=G1)+(C2:C9=I1)>0,0),0))`: Every Excel version, without Ctrl+Shift+Enter.
- `=FILTER(A2:A9,(B2:B9=G1)+(C2:C9=I1))`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/xlookup-with-or-logic