How to Use INDEX and MATCH in Excel

INDEX and MATCH is the classic Excel lookup. MATCH finds where a value sits in a list, and INDEX returns what's at that position in another column. Nest one inside the other and you get a lookup that works in any direction:

In words: find lookup_value in lookup_range, then return the item in the same position from return_range. The 0 means exact match.

The examples use this staff list, with headers in row 1 and data in rows 2 to 9:

The sheet also has Salary in column D and Start date in column E. They're left out of this table so the exercise below isn't spoiled, but the live sheet shows all five columns, and every range in this guide refers to it.

#The INDEX and MATCH syntax

In the pattern above, lookup_range is MATCH's lookup_array and return_range is INDEX's array.

#A step-by-step INDEX MATCH example

Which department is Lena Fischer in?

1. MATCH finds the position.

Lena is on worksheet row 8, yet MATCH returns 7, because it counts from the first cell of the range: B2 is position 1. MATCH gives a position inside the range, not a sheet row.

2. INDEX returns the item at that position.

3. Put MATCH inside INDEX, so Excel works out the 7 for you:

Both ranges start in row 2, so position 7 means the same row in each. To return another column, change only the INDEX range:

In a real workbook, point at a cell instead of typing the name: =INDEX(C2:C9, MATCH(K2, B2:B9, 0)). Press Enter as normal. These formulas don't need Ctrl+Shift+Enter in any version of Excel.

Your turn. This exercise asks for Priya Shah's salary. It accepts XLOOKUP, VLOOKUP or INDEX, and its model answer uses XLOOKUP, so try INDEX and MATCH instead: match the name in column B and return from the Salary column.

#Always use 0 for an exact match

MATCH's last argument sets how it searches:

• 0: an exact match. The list can be in any order.
• 1, or left out: the largest value less than or equal to lookup_value. The list must be sorted smallest to largest.
• -1: the smallest value greater than or equal to lookup_value. The list must be sorted largest to smallest.

Because the default is 1, forgetting the 0 gives you an approximate match. The IDs in column A happen to be sorted, so look what happens with an ID that doesn't exist:

The approximate version settles for E108 and returns a believable but wrong name. On unsorted data, like the names, it can also miss values that are there. For IDs, names and codes, always end MATCH with 0.

#Keep the ranges lined up

INDEX trusts MATCH's position completely. If the two ranges start on different rows, you get a wrong answer with no error. Here the INDEX range starts at the header:

Position 7 of C1:C9 is C7, Tom Becker's department. Make both ranges cover the same rows, such as B2:B9 and C2:C9.

#Look up a value to the left

VLOOKUP can only return columns to the right of the one it searches. INDEX and MATCH can return from any column, so finding an ID from a name is easy:

And because you point at the return column instead of counting columns, inserting a column doesn't break the formula. Excel adjusts the ranges for you.

#Lock the ranges before copying down

With names in K2:K4, put this in L2 and copy it down to get each person's department:

K2 changes to K3 and K4, but the dollar signs keep both ranges fixed. With Grace Kim, Omar Haddad and Lena Fischer in K2:K4, you get Marketing, Finance and IT. Without the dollar signs, the ranges slide down a row each time, so names near the top drop out and return #N/A.

#Two-way lookups with INDEX, MATCH and MATCH

INDEX can take a row number and a column number. Give it the whole table and let one MATCH find the row and another find the column. This finds Hannah Lee's salary by matching her name down column B and the "Salary" header across row 1:

The row MATCH returns 1 and the column MATCH returns 4, so INDEX returns row 1, column 4 of A2:E9, which is cell D2. Swap "Salary" for "Department" and it returns Sales. B2:B9 must cover the same rows as A2:E9, and A1:E1 the same columns.

This isn't the same as a lookup with multiple criteria, where two conditions must both be true in one row. That needs a different formula.

#How to fix #N/A and #REF!

#N/A means MATCH found nothing. Check these:

1. The 0. Without it, MATCH does an approximate match.
2. Extra spaces. "Lena Fischer " won't match "Lena Fischer". Clean the data with TRIM, or use =MATCH(TRIM(K2), B2:B9, 0).
3. Numbers stored as text. The number 1002 doesn't match the text "1002". Use VALUE(K2) if the column holds numbers, or K2&"" if it holds text.
4. A genuinely missing value. Show a message with IFNA (Excel 2013 and later), which still lets other errors show:

#REF! means INDEX was asked for a position outside its range, usually because the MATCH range is longer:

MATCH returns 7, but C2:C5 has only 4 cells. Use ranges of the same size.

#Duplicates and upper or lower case

MATCH returns the first match only. Sales appears twice in column C, but you only get the first person:

Diego Ruiz is in Sales too. To check for repeats, use COUNTIF: =COUNTIF(C2:C9, "Sales") returns 2. To list every match, use FILTER in Microsoft 365 or Excel 2021 and later.

MATCH also ignores case, so "lena fischer" finds Lena Fischer. A case-sensitive lookup needs a formula built on the EXACT function.

#INDEX MATCH vs XLOOKUP and VLOOKUP

INDEX and MATCH work in Excel 2016 and 2019 as well as Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. XLOOKUP isn't available in Excel 2016 or 2019. If everyone using the file has Microsoft 365 or Excel 2021 or later, XLOOKUP is shorter, exact by default and has a built-in "not found" argument:

See the XLOOKUP guide. VLOOKUP works in every version but can't look left, and the VLOOKUP guide covers it. For Microsoft's own examples, see Look up values in a list of data.

For more practice, the lookups topic has Premium exercises on MATCH on its own, INDEX and MATCH and a two-way lookup.

#Key takeaways

• MATCH returns a position inside its range, not a sheet row. INDEX returns the item at that position.
• Use =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) and don't leave out the 0.
• Keep both ranges on the same rows, and lock them with dollar signs before copying down.
• Two MATCHes give you a two-way lookup. In Microsoft 365 or Excel 2021 and later, XLOOKUP is simpler.