Lookup & Reference
INDEX and MATCH with multiple criteria: boolean array lookup without XLOOKUP
Match two or more columns simultaneously with INDEX(return_range, MATCH(1, (col1=x)*(col2=y), 0)) for universal workbook compatibility.
Multiply boolean criteria arrays to find row where all equal 1
When sharing workbooks with users on older Excel editions that lack XLOOKUP, INDEX + MATCH handles multi-column lookups cleanly using boolean multiplication.
Multiplying (A2:A5 = E1) * (B2:B5 = E2) turns TRUE*TRUE into 1 and every other row into 0. MATCH(1, ..., 0) finds the first row equal to 1, and INDEX returns the corresponding rate.
| Zone (A) | Service (B) | ShippingFee (C) | TargetZone (E1) | TargetService (E2) |
|---|---|---|---|---|
| North | Standard | 12 | South | Express |
| North | Express | 25 | ||
| South | Standard | 15 | ||
| South | Express | 30 |
Look up non-numeric text or numeric rates across two keys
Unlike SUMIFS—which can only return numbers and adds duplicate rows—INDEX + MATCH returns text or numbers from the exact first matching row (South + Express -> 30).
=INDEX(C2:C5, MATCH(1, (A2:A5=E1)*(B2:B5=E2), 0))Handle unmatched combinations cleanly with LET + ISNA
When no row produces 1, MATCH returns #N/A; capturing the position in LET(pos, MATCH(...), IF(ISNA(pos), "No rate", INDEX(C2:C5, pos))) returns a clean fallback without hiding broken cells in C2:C5.
=LET(pos, MATCH(1, (A2:A5=E1)*(B2:B5=E2), 0), IF(ISNA(pos), "No rate", INDEX(C2:C5, pos)))Try the example
Use the original values or change them to test your understanding.