Two-Way Lookup in Excel (Row and Column)

A two-way lookup returns the value where a row and a column meet: the units sold of one product in one month, or the price of one item in one size. The classic two way lookup Excel formula is INDEX with two MATCHes, one for the row and one for the column, and it works in every version of Excel. This guide is for anyone who knows a basic lookup and now has a grid of data with labels on two sides.

What is a two-way lookup?#

A normal lookup uses one key, like a product code, and returns a value from a fixed column. A two-way lookup uses two keys: one picks the row, the other picks the column. Here is a table of units sold:

You want the cell where the Fineliner Set row crosses the Feb column. The answer is 65, but you want a formula that finds it, so it updates when someone changes the product in H1 or the month in H2.

Two way lookup Excel formula with INDEX and MATCH#

INDEX can take a row number and a column number: =INDEX(table, row_num, column_num). Give it a MATCH for each:

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

Read it in three parts:

1. B2:E6 is the block of numbers only, without the labels.
2. MATCH(H1,A2:A6,0) finds the product's row. Fineliner Set is the 5th product, so it returns 5.
3. MATCH(H2,B1:E1,0) finds the month's column. MATCH works across a row just as it does down a column. Feb is the 2nd month, so it returns 2.

INDEX then returns row 5, column 2 of B2:E6, which is 65. Change H2 to Apr and the result becomes 100. If you are new to these functions, read the INDEX function guide and the MATCH function guide first.

The one rule: line up the ranges#

The row MATCH must cover the same rows as the table, and the column MATCH the same columns. This is where most two-way lookups go wrong.

If you write MATCH(H2,A1:E1,0), the header range starts in column A, so Feb returns 3 instead of 2. INDEX then gives you March's number, 85, with no error. The order of the MATCHes matters too: row first, then column. Swap them and you get the wrong cell, or #REF! if the number is too big for the table.

Build a report grid with one formula#

You can fill a whole summary table from one formula by using mixed references: a $ on the column of the row labels and a $ on the row of the headers. Say your report has products in G10:G12 and months in H9:I9:

Type this in H10:

=INDEX($B$2:$E$6,MATCH($G10,$A$2:$A$6,0),MATCH(H$9,$B$1:$E$1,0))

• $G10 always reads column G but moves down the rows.
• H$9 always reads row 9 but moves across the columns.
• Every range that points at the data table is fully locked.

Fill it down with Ctrl+D (Mac: ⌘D) and across with Ctrl+R (Mac: ⌘R). Each cell finds its own product and month. This is a common task in Excel tests, and it appears in our list of practice tasks to try before an Excel test.

Use drop-down lists for the row and column#

A two-way lookup is often the engine of a small "pick a product, pick a month" report. Drop-down lists make it easy to use, and they stop typos that cause #N/A:

1. Select H1, the product cell.
2. Go to Data > Data Validation.
3. Under Allow, choose List. In Source, type =$A$2:$A$6, then click OK.
4. Do the same for H2, with =$B$1:$E$1 as the source.

Now H1 and H2 show an arrow, and you can only pick a product and month that exist in the table. Change either one and the INDEX MATCH result updates straight away. This is a small touch that makes a report feel finished in a job test.

Other ways to do a two-way lookup#

INDEX MATCH is the most flexible option, but there are two others you may see.

VLOOKUP with MATCH for the column#

Replace VLOOKUP's typed column number with a MATCH:

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

Here the header range must start in column A, the same column as the table, because VLOOKUP counts columns from the table's first column. Feb returns 3, and column 3 of A1:E6 is Feb. This works in every version, but it can only look to the right.

A nested XLOOKUP#

In Excel 2021 or Microsoft 365, you can put one XLOOKUP inside another:

=XLOOKUP(H1,A2:A6,XLOOKUP(H2,B1:E1,B2:E6))

The inner XLOOKUP finds Feb in the headers and returns the whole Feb column (C2:C6). The outer XLOOKUP finds Fineliner Set in the products and returns its value from that column: 65. Learn the rest of XLOOKUP in our XLOOKUP beginner's guide. Like INDEX MATCH, XLOOKUP can also look up to the left.

Why does a two-way lookup return an error?#

Each error has a likely cause:

• #N/A: one MATCH didn't find its value. Check the spelling of the product and the month, and look for extra spaces or numbers stored as text. Our guide on fixing #N/A errors covers each cause.
• #REF!: a position is bigger than the table, often because the two MATCHes are swapped.
• A wrong number with no error: a MATCH range doesn't line up with the table, or a MATCH is missing its 0.

Practise it#

Two-way lookups feel tricky until you have built one with your own hands. ExcelReady's INDEX/MATCH skill check gives you a stock report with monthly units: you run a two-way lookup for one product and month, then fill a small report grid from a single formula with mixed references, all in a live spreadsheet. Try the size-and-price exercise on this page, then take the INDEX/MATCH skill check.