Answers / Question 10

XLOOKUP with two criteria returns #N/A: the formula and the usual hidden cause

The multiply-the-conditions XLOOKUP pattern for two criteria, how to tell when numbers stored as text are breaking it, and a Google Sheets equivalent.

The problem

You need to look up a value using two conditions at once, such as an order number and a product code. Your attempt either errors or comes back with #N/A, even though you can see the matching row.

Inspired by a public question on Stack Overflow. The problem above is described in our own words and the solution below is ours.

The formula

Multiply the two tests. Each comparison gives an array of TRUE/FALSE, multiplying turns them into 1s and 0s, and the row where both are true becomes 1. Then look up the 1:

Excel 365: match B against G2 and C against G3, return DFormula

=XLOOKUP(1, (B2:B100=G2)*(C2:C100=G3), D2:D100, "No match")

Still no match? Check for invisible differences

Most of the time the values differ in a way you can't see: a number stored as text ("101" is not 101) or a trailing space. Test one row you expect to match:

TRUE means they really matchFormula

=B5=G2

TRUE means a real number, FALSE means textFormula

=ISNUMBER(B5)

To compare as text on both sides and ignore stray spaces:

Text-safe versionFormula

=XLOOKUP(1, (TRIM(B2:B100)=TRIM(G2))*(TRIM(C2:C100)=TRIM(G3)), D2:D100, "No match")

Google Sheets

First match on both conditionsFormula

=IFERROR(INDEX(FILTER(D2:D, B2:B=G2, C2:C=G3), 1), "No match")

Good to know

Keep all ranges the same size (B2:B100, C2:C100 and D2:D100), or XLOOKUP returns #VALUE!.
If several rows match, XLOOKUP returns the first. Use -1 as the 6th argument to get the last one instead.

Formulas follow documented Google Sheets / Excel behavior; they aren't machine-tested, so try them on a copy first. Always try new automations on a copy of your sheet first.