How to look up a value that could be in any of several columns
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code")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").
- Result on the example
- Monitor arm 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
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.
=XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code")Result"Monitor arm"
B2:B76 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …A range: 6 cells in one column.
G1"AC-77"The value in G1.
B2:B7=G1{6 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
C2:C76 rows × 1 col: "DL-1", "OC-4", "MA-2", "SD-9", …A range: 6 cells in one column.
C2:C7=G1{6 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
B2:B7=G1)+(C2:C7=G1{6 rows × 1 col} 0, 0, 0, 0, …The + operator adds.
D2:D76 rows × 1 col: "AC-12", "AC-31", "AC-77", "AC-40", …A range: 6 cells in one column.
D2:D7=G1{6 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1{6 rows × 1 col} 0, 0, 1, 0, …The + operator adds.
B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0{6 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The > operator tests greater than.
A2:A76 rows × 1 col: "Desk lamp", "Office chair", "Monitor arm", "Standing desk", …A range: 6 cells in one column.
XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code")"Monitor arm"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0{6 rows × 1 col} FALSE, FALSE, TRUE, FALSE, … - return_array
A2:A76 rows × 1 col: "Desk lamp", "Office chair", "Monitor arm", "Standing desk", … - if_not_found
"Unknown code"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code"). We ran it in Google Sheets mode on the same data and it returns Monitor arm.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A7,SUMPRODUCT((B2:D7=G1)*(ROW(B2:D7)-ROW(B2)+1))) | Monitor arm | 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) | Monitor arm | 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.
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.
Look up a value that could be in any of several columns
G2, return the product for the code in G1, whichever column it is in.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,"")).
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.