How to do a two-way lookup by row and column in Excel

Q: What if the row labels are numbers in bands?

Use an approximate MATCH for that side: MATCH(H1,A2:A5,1) with the bands sorted from smallest to largest.

Formula (Excel and Google Sheets)

=INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0))

Use INDEX with one MATCH for the row and one for the column: =INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0)).

Result on the example: $229 in H3
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in H3. Change any input and the result updates, or edit the formula to experiment.

How it works

The first MATCH finds the row of the room type in A2:A5 (Deluxe is 2nd) and the second finds the column of the season in B1:E1 (High is 3rd). INDEX returns the cell where that row and column cross in B2:E5.

=INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0))

Result229

B2:E54 rows × 4 cols: $129, $149, $189, $229, …
A range: 4 rows × 4 columns.
H1"Deluxe"
The value in H1.
A2:A54 rows × 1 col: "Standard", "Deluxe", "Suite", "Family"
A range: 4 cells in one column.
MATCH(H1,A2:A5,0)2
MATCH: Position of a value in a list. 0 = exact match.
- lookup_valueH1"Deluxe"
- lookup_arrayA2:A54 rows × 1 col: "Standard", "Deluxe", "Suite", "Family"
- match_type0
H2"High"
The value in H2.
B1:E11 row × 4 cols: "Low", "Shoulder", "High", "Holiday"
A range: 4 cells in one row.
MATCH(H2,B1:E1,0)3
MATCH: Position of a value in a list. 0 = exact match.
- lookup_valueH2"High"
- lookup_arrayB1:E11 row × 4 cols: "Low", "Shoulder", "High", "Holiday"
- match_type0
INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0))229
INDEX: Value at a given row and column of a range.
- arrayB2:E54 rows × 4 cols: $129, $149, $189, $229, …
- row_numMATCH(H1,A2:A5,0)2
- column_numMATCH(H2,B1:E1,0)3

In Google Sheets

The same formula works in Google Sheets: =INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0)). We ran it in Google Sheets mode on the same data and it returns $229.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=XLOOKUP(H1,A2:A5,XLOOKUP(H2,B1:E1,B2:E5))	$229	The inner XLOOKUP returns the whole season column and the outer one picks the room from it. Excel 2021, Microsoft 365 and Google Sheets.
=VLOOKUP(H1,A2:E5,MATCH(H2,A1:E1,0),FALSE)	$229	VLOOKUP with MATCH supplying the column number. Note that the header range starts at column A, like the table.

Common mistakes

Each MATCH range must line up with the INDEX range: rows A2:A5 with B2:E5, columns B1:E1 with B2:E5.
Both MATCHes need 0 for an exact match.
If the labels in H1 and H2 come from typing, a trailing space gives #N/A. A data validation list avoids that.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Do a two-way lookup by row and column

In H3, return the nightly rate for the room type in H1 and the season in H2.

Questions

What if the row labels are numbers in bands?

Use an approximate MATCH for that side: MATCH(H1,A2:A5,1) with the bands sorted from smallest to largest.

Does this work in Google Sheets?

Yes, INDEX and MATCH work the same way, and so does the nested XLOOKUP.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.