Lookup & Reference
XLOOKUP match any column: search Primary SKU, Legacy ID, or Barcode at once
Find a product or contact when the user types an identifier that could live in Column B, Column C, OR Column D using Boolean OR array math.
Search across 3 identifier columns simultaneously with Boolean OR (+)
Catalog migrations and POS scanners frequently receive mixed inputs: one warehouse scans the UPC Barcode (Col D), support types the new SKU (Col B), and legacy invoices cite the old Part Number (Col C).
Adding the column comparisons (B2:B4=G2) + (C2:C4=G2) + (D2:D4=G2) produces >= 1 on any row where AT LEAST ONE identifier matches! Testing > 0 * 1 normalizes double matches safely to 1 so XLOOKUP(1, ..., ReturnCol, "Not Found") finds the row cleanly.
| Product (A) | NewSKU (B) | LegacyID (C) | Barcode (D) | Price (E) |
|---|---|---|---|---|
| Thermal Printer | TP-400 | OLD-88 | 890011 | $249 |
| Barcode Scanner | BS-210 | OLD-42 | 890025 | $139 |
| Label Roll 4x6 | LR-105 | OLD-19 | 890039 | $24 |
Multi-column OR lookup formulas
Searches NewSKU, LegacyID, and Barcode with one lookup expression without nested IFNA chains.
=IF(G2="","Not Found",XLOOKUP(1, (((B2:B4=G2) + (C2:C4=G2) + (D2:D4=G2)) > 0) * 1, A2:A4, "Not Found"))Return unit price from whichever identifier column matched
Swap return_array to E2:E4 and fallback to 0.
=IF(G2="","Not Found",XLOOKUP(1, (((B2:B4=G2) + (C2:C4=G2) + (D2:D4=G2)) > 0) * 1, E2:E4, 0))Inputs and interpretation
Use consistent text identifiers to preserve leading zeros. Comparisons are case-insensitive; if different rows share an identifier, the first matching row wins. The price fallback zero is not distinguishable from a genuine zero price. Empty search input returns Not Found rather than matching empty identifiers.
Try the example
Use the original values or change them to test your understanding.