Lookup & Reference
Two-way approximate match lookup: 2D bracket grid (INDEX & XMATCH)
Look up rates in a 2D bracket matrix (such as Order Weight × Credit Tier or LTV × FICO Score) where both row and column headers are numeric thresholds.
Why 2D rate cards need approximate matching in BOTH directions
Pricing matrices, freight zone tables, and mortgage rate sheets rarely match exact numbers—an order weighing 25 kg for a customer with a 720 credit score falls into the "10+ kg" row bracket AND the "700+ score" column bracket simultaneously.
Combining INDEX(B2:D4, XMATCH(F2, A2:A4, -1), XMATCH(G2, B1:D1, -1)) resolves both thresholds at once! Setting match_mode = -1 ("exact match or next smaller item") snaps 25 kg down to tier 10 (row 2) and 720 down to tier 700 (column 2)—even if your threshold headers are not sorted ascending!
The numbers are an invented two-axis rate card, not a lending policy or real market quote. Use unique numeric threshold headers and keep their associated rows and columns aligned. This floor lookup gives the final bracket no upper limit; add an explicit upper bound when the policy requires one.
| Min Weight kg (A) | Score 620+ (B) | Score 700+ (C) | Score 760+ (D) |
|---|---|---|---|
| 0 kg | 7.50% | 6.80% | 6.20% |
| 10 kg (25 kg lands here) | 7.10% | 6.50% (720 lands here!) | 5.90% |
| 50 kg | 6.70% | 6.10% | 5.50% |
Two-way approximate tier lookup with INDEX and XMATCH (-1)
XMATCH(..., -1) finds the largest row floor <= F2 and the largest column floor <= G2, returning #N/A cleanly if an input falls below the lowest bracket (e.g., credit score 580 < 620).
=INDEX(B2:D4, XMATCH(F2, A2:A4, -1), XMATCH(G2, B1:D1, -1))Handle inputs below the supplied thresholds
Checking ISNA(rowIndex) + ISNA(colIndex) > 0 catches inputs below either minimum qualifying floor (such as Score 580 < 620) before calling INDEX.
=LET(rowIndex, XMATCH(F2, A2:A4, -1), colIndex, XMATCH(G2, B1:D1, -1), IF(ISNA(rowIndex) + ISNA(colIndex) > 0, "Below supplied thresholds", INDEX(B2:D4, rowIndex, colIndex)))Try the example
Use the original values or change them to test your understanding.