# How to look up a value that could be in any of several columns

Test each column and look for the row where any test is true: `=XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code")`.

**Excel:** `=XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code")` (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 |
| --- | --- | --- | --- | --- | --- | --- |
| Product | SKU | Old SKU | Supplier code |  | Code | AC-77 |
| Desk lamp | P-101 | DL-1 | AC-12 |  | Product |  |
| Office chair | P-102 | OC-4 | AC-31 |  |  |  |
| Monitor arm | P-103 | MA-2 | AC-77 |  |  |  |
| Standing desk | P-104 | SD-9 | AC-40 |  |  |  |
| Keyboard | P-105 | KB-3 | AC-58 |  |  |  |
| Webcam | P-106 | WC-5 | AC-63 |  |  |  |

Formula in G2: `=XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code")`

Result: Monitor arm

## How it works

Each comparison checks one column. Adding them gives a count per row of how many columns match, and `>0` turns that into TRUE for any row where at least one does. XLOOKUP returns the product beside the first TRUE.

## Other ways

- `=INDEX(A2:A7,SUMPRODUCT((B2:D7=G1)*(ROW(B2:D7)-ROW(B2)+1)))`: Compares the whole block at once and returns the row number of the match. Works in every version when each code appears once.
- `=FILTER(A2:A7,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0)`: Returns every product with that code, if a code could be reused.

## Common mistakes

- Without `>0`, looking up 1 misses rows where the code appears in two columns, because their sum is 2.
- The SUMPRODUCT version adds row numbers, so a code that appears in two rows returns a wrong product.

## Questions

**How do I search many columns without writing a test for each?**

Compare the block and add across each row with MMULT: `=XLOOKUP(TRUE,MMULT(--(B2:D7=G1),{1;1;1})>0,A2:A7)`.

**How do I return which column matched?**

Use the header row: `=TEXTJOIN(", ",TRUE,IF(B2:D7=G1,B1:D1,""))`.

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/lookup-in-any-of-several-columns