INDEX MATCH in Excel: how it works, with multiple criteria

INDEX and MATCH are two small functions that, put together, do everything VLOOKUP does and a few things it can't: look up to the left, survive inserted columns, and match on more than one column. You still meet them in nearly every shared workbook. They look scary written out in one line, but each half does one simple thing, and once you've seen the two halves on a sheet the whole formula reads easily.

Every example below uses this small staff list:

What does INDEX do on its own?

INDEX takes a list and a number, and gives you the item at that number. Ask it for the 3rd name and it counts down the list:

Notice it counts from the top of the list you give it (A2 is 1), not from row 1 of the sheet. Keep that in mind: it's behind the most common INDEX MATCH mistake further down.

What does MATCH do on its own?

MATCH is the opposite. You give it a value, and it tells you which number in the list that value is:

The 0 at the end means "exact match only". Without it, MATCH guesses the nearest value and only works on sorted lists, so on anything else it quietly gives the wrong number. Always type the 0.

How do INDEX and MATCH work together?

You now have a number from MATCH (3) and a function that wants a number (INDEX). Put one inside the other: MATCH finds where S-103 is, and INDEX takes the name from that same position.

Read it from the inside out: "which position is S-103 in the IDs?" (3), then "what's the 3rd name?" (Chloe Park). That's all INDEX MATCH is.

In a real sheet you won't type S-103 into the formula. You'll point at the cell that holds the ID, and add $ signs so the lists stay put when you copy the formula down a column. With the IDs to look up in column D:

New to the $ signs? Locking the table range takes two minutes to practise.

How do you use INDEX MATCH to look to the left?

Look at the sheet again: the names in column A are to the left of the IDs in column B, and the formula had no trouble. VLOOKUP can't do that. It only searches the first column of its table and counts to the right, so it would need the IDs moved in front of the names. With INDEX and MATCH the list you search and the list you take the answer from are separate, so they can be in any order. And because there's no column number in the formula, inserting a column between them doesn't break anything.

How do you use INDEX MATCH with multiple criteria?

Sometimes one column isn't enough to find the right row. In this sales list, "North" appears three times and "Tape" twice, but only one row is North and Tape:

The middle part is the clever bit. Excel checks every row against each condition, which gives a column of TRUE and FALSE for each. Multiplying them turns TRUE into 1 and FALSE into 0, so a row only scores 1 when both are true. You never see these columns, but this is what Excel works out:

MATCH(1, ..., 0) looks for that 1, finds it at position 3, and INDEX takes the 3rd amount: 30. For a third condition, multiply in one more check the same way, such as *(D2:D6="Q1") if column D held the quarter.

In a real sheet, point at the cells that hold what you're looking for. With the region in E2 and the product in F2:

In Excel 365 and Excel 2021 press Enter as normal. In Excel 2019 and older, confirm it with Ctrl+Shift+Enter, or you get an error or a wrong value.

An easier way: a helper column

If that formula feels like too much, join the two columns into one first. Type =A2&"|"&B2 in D2 and fill it down, and every row gets a single key you can look up the normal way:

It works in every version of Excel and is easy for the next person to follow. The | in the middle stops different pairs from joining into the same text (North + East and Nor + thEast would both become NorthEast without it).

How do you do a two-way lookup with INDEX MATCH MATCH?

When a table has labels down the side and across the top, you need a row and a column. INDEX can take both, so give it one MATCH for each:

Each list lines up with the numbers table: the product names run down the same rows as B2:D4, and the months run across the same columns. Practise it in the two-way lookups lesson.

Why is INDEX MATCH not working?

The most common mistake gives an answer with no error, just from the wrong row. Here the two lists start on different rows:

MATCH says S-103 is 3rd in its list, but INDEX counts from row 1, so its 3rd item is a row higher. Make both lists start and end on the same rows. The other usual problems:

• #N/A: the value isn't in the list exactly as typed. Usually an extra space or a number stored as text, the same as with VLOOKUP: VLOOKUP not working? 9 causes shows how to spot each one.
• Wrong values, no error: the 0 is missing from MATCH, so it's guessing the nearest value.
• Right in the first row, wrong further down: the lists slid when you copied the formula. Add $ signs.
• #REF!: the INDEX list is shorter than the MATCH list, so the position runs off the end.
• The multiple criteria version gives an error in older Excel: confirm it with Ctrl+Shift+Enter, or use the helper column.

Should you use INDEX MATCH or XLOOKUP?

XLOOKUP does the same job in one function. This is the first example again:

If everyone who opens the file has Excel 365, Excel 2021 or later, or Google Sheets, use XLOOKUP (the XLOOKUP formula explained simply). If the file has to work in Excel 2019 or older, INDEX and MATCH is the safe choice. There's a full comparison in XLOOKUP vs VLOOKUP.

Try INDEX MATCH on your own data

Paste your two tables into Lookup & Merge. It matches them in your browser, then shows the INDEX/MATCH formula for your actual columns, next to the XLOOKUP and VLOOKUP versions, ready to copy into Excel. Matching on two columns works there too, under More options.

To practise writing it yourself, each of these takes a few minutes in a spreadsheet in your browser:

• MATCH: finding a position
• INDEX: picking a cell by position
• Looking left with INDEX and MATCH
• Lookups on two or more columns