Lookup & Dynamic Arrays
XMATCH with Multiple Criteria: Locate Compound-Key Row Positions Cleanly
Find the exact 1-based row index where three columns match simultaneously using XMATCH(1, (colA=x)*(colB=y)*(colC=z)) and retrieve target attributes with INDEX.
Match compound rate-card keys without concatenating helper columns
Cloud support and freight rate cards are keyed by three attributes at once — Region, Plan Tier, and Response SLA. Multiplying three boolean comparisons (A2:A6 = B8) * (B2:B6 = B9) * (C2:C6 = B10) produces an array of 1s (all three match) and 0s (at least one differs).
Searching for 1 inside that product array with XMATCH(1, ...) returns the exact 1-based row offset automatically — without needing legacy Ctrl+Shift+Enter array entry — and pairs directly with INDEX(D2:D6, pos) to return the unit price.
| Table Row | Region | Tier | SLA | Monthly Fee | Boolean Product (EMEA*Pro*24x7) |
|---|---|---|---|---|---|
| 1 | NA | Pro | 24x7 | $450 | 0 * 1 * 1 = 0 |
| 2 | EMEA | Starter | 8x5 | $150 | 1 * 0 * 0 = 0 |
| 3 | EMEA | Pro | 8x5 | $320 | 1 * 1 * 0 = 0 |
| 4 | EMEA | Pro | 24x7 | $490 | 1 * 1 * 1 = 1 (Match Pos 4!) |
| 5 | APAC | Pro | 24x7 | $510 | 0 * 1 * 1 = 0 |
Compound-Key Row Position via XMATCH
Returns the 1-based position inside A2:C6 matching B8, B9, and B10, or 0 when unmatched.
=IFNA(XMATCH(1, (A2:A6 = B8) * (B2:B6 = B9) * (C2:C6 = B10)), 0)Compound-Key Fee Lookup via INDEX + XMATCH
Checks whether XMATCH found a positive row position before indexing D2:D6.
=LET(pos, IFNA(XMATCH(1, (A2:A6 = B8) * (B2:B6 = B9) * (C2:C6 = B10)), 0), IF(pos = 0, "Combination Not Offered", INDEX(D2:D6, pos)))Try the example
Use the original values or change them to test your understanding.