Lookups
Two-way lookup with INDEX and MATCH: find a value by row and column
Retrieve a rate at the intersection of a row label and a column header by passing two exact MATCH positions into INDEX.
A shipping rate grid indexed by zone and box size
Rows A2:A4 list shipping zones (Domestic, EU, Overseas) and columns B1:D1 list box sizes (S, M, L). To quote an EU parcel in size L, you need row 2 and column 3 of the rate matrix B2:D4.
| Zone (A) | S (B) | M (C) | L (D) |
|---|---|---|---|
| Domestic | 6 | 9 | 14 |
| EU | 11 | 16 | 24 |
| Overseas | 19 | 28 | 42 |
Supply one MATCH for the row and one MATCH for the column
MATCH(F2, A2:A4, 0) finds the row position of EU (2), and MATCH(G2, B1:D1, 0) finds the column position of L (3). INDEX(B2:D4, 2, 3) returns the intersection, 24.
Notice B2:D4 excludes the header row and label column so row 1, column 1 of INDEX aligns with Domestic / S.
=INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(G2,B1:D1,0))Guard unknown zones or sizes explicitly
If either F2 or G2 contains a typo, its MATCH returns #N/A. Test both positions with ISNA (or wrap in IFERROR when the matrix contains only clean numbers) to display a clear message instead of an error.
=IF(OR(ISNA(MATCH(F2,A2:A4,0)),ISNA(MATCH(G2,B1:D1,0))),"Invalid zone or size",INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(G2,B1:D1,0)))Try the example
Use the original values or change them to test your understanding.