Two-Way Lookup

Excel Formulas › Lookup

Excel 365Excel 2021+XLOOKUPINDEX/MATCH alt

A two-way lookup finds a value at the intersection of a row and a column — like pulling the sales figure for a specific month and a specific region out of a grid. The cleanest modern way is to nest one XLOOKUP inside another; the classic way is INDEX with two MATCHes.

Quick formula: to find the value where row label H2 meets column label H3:
=XLOOKUP(H2, A2:A6, XLOOKUP(H3, B1:D1, B2:D6))
The inner XLOOKUP picks the right column; the outer one finds the right row within it.

Functions used (tap for the full reference guide):

XLOOKUPINDEXMATCH

The example

A small sales grid: months down column A, regions across row 1. We want March / West.

ABCD
1MonthEastWestSouth
2Jan$310$280$190
3Feb$330$300$210
4Mar$360$420$240
5Apr$300$390$260
6May$345$405$275

The formula

With the lookups in H2 (month) and H3 (region):

=XLOOKUP(H2, A2:A6, XLOOKUP(H3, B1:D1, B2:D6)) // Mar / West → $420

How it works

Read it from the inside out:

  1. The inner XLOOKUP(H3, B1:D1, B2:D6) finds the region "West" in the header row B1:D1 and returns the entire matching column of numbers B2:B6 — the whole West column.
  2. That column becomes the return_array for the outer XLOOKUP.
  3. The outer XLOOKUP(H2, A2:A6, …) finds "Mar" in the month list and returns the value from the West column on that row — $420.
  4. Change either input and the intersection updates instantly, in either direction.

Try it: interactive demo

Live demo

Choose a month and a region; the nested XLOOKUP returns the cell where they cross.

Intersection:

Variations

The classic INDEX / MATCH / MATCH

Works in every version of Excel, including 2019 and older:

=INDEX(B2:D6, MATCH(H2, A2:A6, 0), MATCH(H3, B1:D1, 0)) // row position, column position

Two-way lookup with approximate match

For a rate card where the row or column is a numeric break point, switch the relevant XLOOKUP to match_mode -1 (exact or next smaller).

Pitfalls & errors

#N/A. The label you typed isn’t found exactly — usually a trailing space or a typo. Confirm with =H2=A4; if it returns FALSE, clean the text with TRIM.

Header and data ranges must line up. The inner lookup’s header range (B1:D1) and its return range (B2:D6) must cover the same columns, or you’ll read the wrong column.

INDEX/MATCH needs 0 for exact match. Leaving off the third MATCH argument defaults to approximate match and can return a silently wrong cell on unsorted labels.

Practice workbook

📊
Download the free Two-Way Lookup practice workbook
The sales grid with the live nested-XLOOKUP and the INDEX/MATCH/MATCH equivalent side by side, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

Is nested XLOOKUP better than INDEX/MATCH for a two-way lookup?
In Excel 365 and 2021 it is easier to read and write. INDEX/MATCH/MATCH is the better choice only when the file must work in Excel 2019 or older, where XLOOKUP is unavailable.
Can I do a two-way lookup with VLOOKUP?
You can combine VLOOKUP with MATCH for the column number, e.g. =VLOOKUP(H2, A2:D6, MATCH(H3, A1:D1, 0), FALSE), but nested XLOOKUP or INDEX/MATCH/MATCH is cleaner and survives inserted columns.
Why use two MATCH functions?
One MATCH finds the row position of the label, the other finds the column position; INDEX then returns the value at that row-and-column intersection.

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: Lookup with multiple criteria · Tax-bracket lookup · Get the most recent match

Function references: XLOOKUP · INDEX · MATCH