XLOOKUP with multiple criteria in Excel

Try the two-condition lookup

The workbook has region in A2:A6, SKU in B2:B6, tier in C2:C6 and price in D2:D6. North / A-10 appears three times. The lookup fields are E2:G5.

=XLOOKUP(1,($A$2:$A$6=E2)*($B$2:$B$6=F2),$D$2:$D$6,"No match")

With E2=North and F2=A-10, H2 returns 12, the first matching price. Adding tier tests the third condition:

=XLOOKUP(1,($A$2:$A$6=E2)*($B$2:$B$6=F2)*($C$2:$C$6=G2),$D$2:$D$6,"No match")

With G2=Wholesale, I2 returns 10. The duplicated North / A-10 / Retail row later in the source does not replace the first match; I5 still returns 12. West / A-10 returns “No match”, rather than an unexplained error.

Why the multiplication works

Each comparison produces TRUE or FALSE per row. Multiplication converts a row to 1 only when every condition is true. Keep all source ranges exactly the same height. To prefer a later match, use XLOOKUP search mode -1 and explain that change to readers of the result.

If a numeric-looking ID fails, diagnose its storage type in the number-as-text example. For returning several matching rows rather than the first, use FILTER with multiple criteria.