Cellwise Try it free

Excel guide · 3 min read

How to do a two-way lookup in Excel

Quick answerUse INDEX with two MATCH functions: =INDEX(B2:E6,MATCH(row_value,A2:A6,0),MATCH(column_value,B1:E1,0)). The first MATCH finds the row, the second finds the column, and INDEX returns the cell where they cross. In Excel 365 you can also nest two XLOOKUPs.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGH
1From lbZone 2Zone 3Zone 4Zone 5ZoneZone 4
208.48.99.610.3Weight lb7
329.7510.611.412.2
4512.113.3514.816.05
51015.917.619.4521.3
62022.525.128.431.75

Download this sheet as an Excel practice file (.xlsx)

INDEX with two MATCHes

This is a parcel rate card: weight bands down the side, delivery zones across the top. H1 holds the zone and H2 the parcel weight. INDEX takes the grid of prices, a row number and a column number.

The zone must match exactly, so that MATCH uses 0. The weight is a 7 lb parcel and the bands start at 0, 2, 5, 10 and 20 lb, so that MATCH uses 1: it finds the largest band start that is not above 7, which is the 5 lb row. Match type 1 needs the column sorted smallest to largest. It works in every version of Excel.

=INDEX(B2:E6,MATCH(H2,A2:A6,1),MATCH(H1,B1:E1,0))

Result: 14.8

14.80: a 7 lb parcel to Zone 4 is priced on the 5 lb row.

Exact match on both sides

When both labels must match exactly, use 0 in both MATCHes. This is the usual case for tables like sales by region and month.

Make sure the two lookup ranges line up with the grid: the row labels cover the same rows as the INDEX range (2 to 6) and the headers cover the same columns (B to E). If they are off by one, you get the neighbouring cell without any error.

=INDEX(B2:E6,MATCH(10,A2:A6,0),MATCH("Zone 2",B1:E1,0))

Result: 15.9

15.90, the 10 lb price for Zone 2.

VLOOKUP with MATCH for the column

If you already know VLOOKUP, swap its fixed column number for a MATCH. Search the headers from the same first column as the VLOOKUP table (A1:E1, not B1:E1), so the numbers line up. TRUE gives the same banded weight lookup as match type 1.

=VLOOKUP(H2,A2:E6,MATCH(H1,A1:E1,0),TRUE)

Result: 14.8

14.80, same answer as INDEX and MATCH.

Nested XLOOKUP (Excel 365)

XLOOKUP can return a whole row or column, so one XLOOKUP can feed another. =XLOOKUP(H1,B1:E1,XLOOKUP(H2,A2:A6,B2:E6,,-1)) first returns the 5 lb row of prices (match_mode -1 means exact or next smaller), then the outer XLOOKUP picks the Zone 4 price from that row: 14.80.

It reads well and has a built-in not-found argument. It needs Excel 365 or 2021 and later. The Cellwise practice engine cannot return a whole row from XLOOKUP, so use INDEX and MATCH to try it here.

SUMPRODUCT for exact two-way lookups

Multiply a row test, a column test and the grid. Only the crossing cell survives, and SUMPRODUCT adds it up. It works in any version and never returns #N/A.

That last point cuts both ways: no match gives 0 instead of an error, and duplicate labels are added together. Use it for exact matches on numeric tables only.

=SUMPRODUCT((A2:A6=10)*(B1:E1=H1)*B2:E6)

Result: 19.45

19.45, the 10 lb Zone 4 price.

Common mistakes

Header range not aligned with the grid

With INDEX(B2:E6,...), the column MATCH must search B1:E1. With VLOOKUP(...,A2:E6,...), it must search A1:E1. One column off returns the wrong price silently.

Approximate match on unsorted labels

Match type 1 and VLOOKUP TRUE need the row labels in ascending order. Otherwise use 0 for exact match.

#N/A from labels that look equal

"Zone 4 " with a trailing space or a number stored as text will not match. Use TRIM on the inputs or retype the header.

Hard-coding the column number

VLOOKUP(H2,A2:E6,4,TRUE) breaks when someone inserts a zone. The MATCH version finds the column by name every time.

FAQ

What is a two-way lookup in Excel?

A lookup that uses two values, one to find the row and one to find the column, and returns the cell where they meet. It is also called a matrix lookup or a 2D lookup.

Is INDEX MATCH MATCH better than VLOOKUP MATCH?

Both give the same answer. INDEX MATCH MATCH keeps working when the label column is not on the left, and it does not need a column offset, so it is easier to keep right.

Can XLOOKUP do a two-way lookup?

Yes, by nesting: the inner XLOOKUP returns a row and the outer one picks from it. It needs Excel 365 or Excel 2021 and later.

How do I return a whole row or column instead of one cell?

In INDEX, set the column number to 0 to return the whole row: =INDEX(B2:E6,MATCH(H2,A2:A6,1),0). In Excel 365 it spills across.

Functions used

Related guides

Updated