The formula
Excel & Google SheetsExcelGoogle Sheets
=INDEX(B2:E5,MATCH(B7,A2:A5,0),MATCH(B8,B1:E1,0))In H2 → 80
Excel 365 (nested XLOOKUP)Excel
=XLOOKUP(B7,A2:A5,XLOOKUP(B8,B1:E1,B2:E5))In H3 → 80
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Product | Q1 | Q2 | Q3 | Q4 | |||
| 2 | Pens | 120 | 135 | 150 | 160 | 80 | ||
| 3 | Paper | 180 | 175 | 190 | 210 | 80 | ||
| 4 | Ink | 75 | 80 | 70 | 90 | |||
| 5 | Folders | 40 | 55 | 60 | 65 | |||
| 6 | ||||||||
| 7 | Product | Ink | ||||||
| 8 | Quarter | Q2 |
How it works
- B2:E5
- The block of numbers only, without headers.
- MATCH(B7,A2:A5,0)
- Row number of the product inside the block.
- MATCH(B8,B1:E1,0)
- Column number of the quarter inside the block.
- Nested XLOOKUP
- The inner XLOOKUP returns the whole Q2 column; the outer one picks the Ink row from it.
Common errors and fixes
Why does the formula return #REF!?
The row or column number is bigger than the block, usually because the header range is wider than B2:E5. Make B1:E1 match the block width.
Why does the formula return #N/A?
The header text doesn't match exactly (e.g. "Q2 " with a space).
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.