Quick Answer & Formula
Excel
Sheets
Intermediate
XLOOKUP: =XLOOKUP(Row_Val, Row_Headers, XLOOKUP(Col_Val, Col_Headers, Table_Data)) | INDEX/MATCH: =INDEX(Table_Data, MATCH(Row_Val, Row_Headers, 0), MATCH(Col_Val, Col_Headers, 0)) To find the value at the intersection of a specific row and column: =INDEX(Data_Grid, MATCH(Target_Row, Row_Labels, 0), MATCH(Target_Col, Col_Headers, 0)).
How to Perform a Two-Way Matrix Lookup in Excel & Google Sheets
When data is structured as a two-dimensional grid (such as Monthly Revenue by Region or Shipping Cost by Weight and Zone), you need to look up across both the X-axis (columns) and Y-axis (rows) simultaneously.
1. Method 1: The Modern Nested XLOOKUP
=XLOOKUP(Target_Product, A2:A6, XLOOKUP(Target_Month, B1:E1, B2:E6))2. Method 2: The Classic INDEX & Double MATCH
=INDEX(B2:E6, MATCH(Target_Product, A2:A6, 0), MATCH(Target_Month, B1:E1, 0))3. Shipping Zone Rate Matrix Example
| Weight Tier (Col A) | Zone 1 (Col B) | Zone 2 (Col C) | Zone 3 (Col D) | Zone 4 (Col E) |
|---|---|---|---|---|
| Up to 1 lb | $5.50 | $6.25 | $7.10 | $8.50 |
| Up to 5 lbs | $9.00 | $10.50 | $12.75 | $15.00 |
| Up to 10 lbs | $14.00 | $16.50 | $19.80 | $24.00 |
To find rate for Up to 5 lbs in Zone 3:
=INDEX(B2:E4, MATCH("Up to 5 lbs", A2:A4, 0), MATCH("Zone 3", B1:E1, 0))- Returns:
$12.75
?
Frequently Asked Questions
Which is faster: Nested XLOOKUP or INDEX/MATCH?
Both have near-identical execution speeds. Nested XLOOKUP is slightly cleaner to read, while INDEX/MATCH is universally compatible with older Excel versions.