Two-Way Lookup with Nested XLOOKUP

Excel Formulas › Lookup

365 / 2021XLOOKUP

Find the value at the intersection of a row and a column — sales for “East” in “Q2.” Nest one XLOOKUP inside another: the inner one picks the column, the outer one picks the row.

Quick formula: row key in G1, column key in G2, over a labeled grid:
=XLOOKUP(G1, rowLabels, XLOOKUP(G2, colLabels, dataGrid))
The inner XLOOKUP returns the matching column; the outer one then picks the row from it — a clean two-way lookup.

Functions used (tap for the full reference guide):

XLOOKUP

The example

Sales for East / Q2 from a grid.

ABC
1Region\QQ1Q2
2East20,00022,000

The formula

Nest column lookup inside row lookup:

=XLOOKUP(G1, rowLabels, XLOOKUP(G2, colLabels, grid)) // East × Q2 → 22,000

How it works

Two lookups compose into an intersection:

  1. The inner XLOOKUP(colKey, colLabels, grid) returns the whole column for that key.
  2. The outer XLOOKUP(rowKey, rowLabels, thatColumn) picks the row from it.
  3. The result is the single cell where the chosen row and column meet.
  4. It reads naturally and, unlike INDEX/MATCH/MATCH, needs no separate row/column index arithmetic.

Classic equivalent: =INDEX(grid, MATCH(rowKey, rows, 0), MATCH(colKey, cols, 0)) does the same in any version. The nested-XLOOKUP form is the modern, more readable take.

Try it: interactive demo

Live demo

Pick region and quarter.

Value:

Variations

INDEX/MATCH version

Any version:

=INDEX(grid, MATCH(r,rows,0), MATCH(c,cols,0))

With not-found

Default on the outer:

=XLOOKUP(r, rows, XLOOKUP(c, cols, grid), "n/a")

SUMIFS alternative

If keys are columns of data:

=SUMIFS(sales, region, r, quarter, c)

Pitfalls & errors

Grid must align with labels. The inner lookup’s return grid and the column labels must span the same columns; likewise rows for the outer.

Exact keys. Both keys match exactly — watch for trailing spaces or case differences in labels.

365/2021 only. Use INDEX/MATCH/MATCH otherwise.

Practice workbook

📊
Download the free Two-Way Lookup with Nested XLOOKUP practice workbook
Two-way nested-XLOOKUP examples (formula text + result) with INDEX/MATCH and SUMIFS variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I do a two-way lookup with XLOOKUP?
Nest them: =XLOOKUP(rowKey, rowLabels, XLOOKUP(colKey, colLabels, grid)). The inner returns the column, the outer picks the row. Requires Excel 365/2021.
What's the INDEX/MATCH equivalent?
=INDEX(grid, MATCH(rowKey, rows, 0), MATCH(colKey, cols, 0)) does a two-way lookup in any Excel version.
Can I use SUMIFS instead?
If your data is a flat list (not a grid), =SUMIFS(values, field1, key1, field2, key2) gives the intersection total.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Index-match 2D · Two-way approx lookup · Left lookup

Function references: XLOOKUP