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

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))`.

**Excel:** `=INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Room type | Low | Shoulder | High | Holiday |  | Room | Deluxe |
| Standard | 129 | 149 | 189 | 229 |  | Season | High |
| Deluxe | 159 | 179 | 229 | 279 |  | Nightly rate |  |
| Suite | 249 | 279 | 339 | 409 |  |  |  |
| Family | 189 | 209 | 259 | 309 |  |  |  |

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

Result: $229

## 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`.

## Other ways

- `=XLOOKUP(H1,A2:A5,XLOOKUP(H2,B1:E1,B2:E5))`: 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)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/two-way-lookup