XLOOKUP across two identifier columns: OR matches and precedence

Beware the classic Boolean OR trap: TRUE + TRUE = 2, which misses XLOOKUP(1, ...)!

Adding two Boolean comparisons produces 0, 1 or 2. Normalize the sum to a Boolean with >0, then convert to 1/0 before looking up 1; otherwise a row matching both identifier columns can be skipped.

OR lookup chooses the first matching row in source order, regardless of which identifier column matched. The nested lookup instead searches the entire primary column before falling back to the alias column. These policies can return different records if a code appears in multiple rows; neither establishes that identifiers are unique.

Store identifiers consistently as text, especially barcodes with leading zeroes. This example rejects empty queries and numeric query values. Exact lookup is case-insensitive; wildcard characters in the query are literal. Prices are required to be numeric in all four rows, so an empty price is not silently displayed as zero.

For the leading-zero experiment, enter ="0010" in the code and query cells so their evaluated values remain text rather than being parsed as the number 10.

First matching row across either identifier column

A dual hit in one row becomes one match. Across different rows, the first match wins.

=IF(OR(COUNTBLANK(E2)>0,NOT(ISTEXT(E2)),COUNT(C2:C5)<>4),"Invalid inputs",XLOOKUP(1, --(((A2:A5 = E2) + (B2:B5 = E2)) > 0), C2:C5, "Not Found"))

Chained XLOOKUP fallback alternative (searches Col A first, then Col B)

The primary SKU column takes precedence even when an alias matched an earlier row. Choose this only when that precedence matches the application rules.

=IF(OR(COUNTBLANK(E2)>0,NOT(ISTEXT(E2)),COUNT(C2:C5)<>4),"Invalid inputs",XLOOKUP(E2, A2:A5, C2:C5, XLOOKUP(E2, B2:B5, C2:C5, "Not Found")))

Further reading

• Microsoft Support — XLOOKUP function