INDEX MATCH MATCH in Excel: Two-Way Lookup

To look up a value by both its row label and column heading, use INDEX with two MATCH functions. The first MATCH finds the row, the second finds the column, and INDEX returns the value where they meet.

Suppose your report lists products down column A and months across row 1. With Pen in G2 and Mar in H2, enter this formula in I2:

=INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4,0),MATCH(H2,$B$1:$E$1,0))

For the table below, the result is 110. Both lookup choices can change; you don't have to edit the formula when you want a different product or month.

Set up a two-way lookup table

You can download the example workbook and follow along on the Lookup sheet. The Checks sheet includes expected results for the product-and-month combinations.

Enter these values in A1:E4. The month headings are plain text: type Jan, Feb, Mar, and Apr, rather than dates formatted to show a month name.

Set up the lookup inputs beside the table:

There are three ranges to keep straight. A2:A4 contains the product labels, B1:E1 contains the month headings, and B2:E4 contains the values to return. Leave the labels out of that last range.

Each product appears once, and each month appears once. That's what lets a product and month identify one cell. We'll look at repeated labels later, because they can produce a plausible answer that isn't the one you intended.

Find the product row with MATCH

Start with the product. Enter this formula in an empty cell to see what the first part does:

=MATCH(G2,$A$2:$A$4,0)

With Pen in G2, it returns 2. Notebook is the first item in A2:A4, Pen is the second, and Folder is the third.

Notice the distinction: Pen is on worksheet row 3, but its position in A2:A4 is 2. MATCH returns the position within its lookup range. That is exactly the number INDEX will need.

The final 0 tells MATCH to use an exact match. Keep it in both MATCH functions. Omitting it switches to MATCH's default approximate-match behavior, which has sorting requirements and isn't appropriate for this product list. Microsoft's MATCH documentation describes the match types and their requirements.

Find the month column with MATCH

Now use the same idea across the top of the table:

=MATCH(H2,$B$1:$E$1,0)

With Mar in H2, this returns 3. Jan is first, Feb second, and Mar third within B1:E1.

MATCH works along a single row or a single column. Here the lookup range is horizontal, so the position it returns becomes the column number for INDEX.

At this point you have the two coordinates you need:

Combine both positions with INDEX

Before putting the formula together, try those two numbers directly:

=INDEX($B$2:$E$4,2,3)

INDEX starts counting at the upper-left corner of B2:E4. Its second row is B3:E3, and its third column is D2:D4. They meet at D3, which contains 110.

Replace the fixed row number with the product MATCH and the fixed column number with the month MATCH:

=INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4,0),MATCH(H2,$B$1:$E$1,0))

Enter it in I2 and press Enter. This example doesn't require Ctrl+Shift+Enter.

Change G2 to Notebook and H2 to Jan; I2 should show 120. Then try Folder and Apr; the result should be 84. These are the first and last cells of the return range, useful checks that your ranges line up at both ends.

This is the extra step beyond a basic INDEX and MATCH lookup: a second MATCH replaces the fixed column position. Both directions can now respond to your inputs.

The formulas here use English function names and commas. If your Excel expects semicolons between arguments, use semicolons in place of the commas.

Copy the formula without shifting the table

The dollar signs keep the source ranges fixed. When you copy I2 down to I3, $B$2:$E$4, $A$2:$A$4, and $B$1:$E$1 still point to the same table.

G2 and H2 have no dollar signs, so they become G3 and H3. Enter another product and month on that row and the copied formula uses the new pair. Our guide to absolute and relative cell references explains why those references behave differently.

If you intend to keep using the original inputs wherever the formula is copied, change them to $G$2 and $H$2. Choose the reference behavior to match your worksheet; locking every reference isn't always the answer.

Fix a wrong result or #N/A

When the result looks wrong, test the two MATCH formulas separately first. You should see 2 and 3 for Pen and Mar. Then check which cell those positions select inside the INDEX range.

A value comes back, but it belongs to the wrong product or month

Check that the return range excludes the headings and labels. Using A1:E4 with the same MATCH ranges makes position 2,3 point to C2, returning 135. Excel has followed the instructions correctly; the starting point is wrong.

Use B2:E4 for the values, A2:A4 for products, and B1:E1 for months. The product range must line up with the return range's rows, and the month range must line up with its columns. Matching their sizes alone isn't enough if one starts a row too early.

MATCH returns #N/A

Check the input on the axis that failed. Pencil won't match Pen, and March won't match the text heading Mar. Check for leading or trailing spaces too; they can be difficult to spot in the grid.

A month heading can also look right while holding a different value. Select it and inspect the formula bar. A real date displayed as Mar isn't the text Mar used in this example. Use consistent text labels, or use matching underlying date values in both the headings and the lookup input.

Labels appear more than once

An exact MATCH returns the first matching position. If you have two Pen rows, this formula uses the first one; it doesn't add their amounts or return both rows. Keep the row labels and column headings unique for this setup.

Likewise, this is a lookup across two axes, not a test of two conditions against a list of records. A report with product, region, and amount in separate columns needs a different layout or formula approach.

Show a message after you've checked the formula

Once the lookup works, you can use IFERROR to show a readable message when it fails:

=IFERROR(INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4,0),MATCH(H2,$B$1:$E$1,0)),"Check product or month")

IFERROR catches other errors as well as #N/A, so the message doesn't prove that an input is missing. If it appears unexpectedly, inspect the original formula without IFERROR. It also won't catch the wrong-but-valid result from a shifted range.

Use two XLOOKUP functions instead

If your Excel supports XLOOKUP, you can use this formula with the same table and inputs:

=XLOOKUP(G2,$A$2:$A$4,XLOOKUP(H2,$B$1:$E$1,$B$2:$E$4))

Work from the inside out. The inner XLOOKUP finds Mar across B1:E1 and returns that month's column of values: 150, 110, and 90. The outer XLOOKUP finds Pen in A2:A4 and takes the corresponding value from that column: 110.

XLOOKUP uses exact matching by default. Microsoft lists support in Microsoft 365, Excel for the web, Excel 2021, and Excel 2024, including their supported Mac editions, and explicitly notes that XLOOKUP isn't available in Excel 2016 or Excel 2019. Use the INDEX MATCH MATCH version when the workbook needs to work in those older releases.