=INDEX(C2:C6,MATCH(D3,A2:A6,0)) reads an employee ID from D3, finds that ID in A2:A6, and returns the salary in the same row of C2:C6. With E-103 in D3 the answer is 88000. Two functions, one answer, and no column counting.
Syntax
=INDEX(array, row_num, [column_num]) and =MATCH(lookup_value, lookup_array, [match_type])
MATCHreturns a position:MATCH("E-103",A2:A6,0)returns 3 because the ID is the third value in that range.INDEXreturns what is at a position:INDEX(C2:C6,3)returns the third salary in that range, 88000.- The
0in MATCH means exact match, and it should always be written.
Nesting them is the point. MATCH produces the number, INDEX consumes it, and neither function needs to know how wide the table is. VLOOKUP needs a column count; this pair does not, which is why a column inserted in the middle of a sheet breaks a VLOOKUP and leaves an INDEX+MATCH alone.
Examples
Example 1: an ID to a salary
Column A holds five employee IDs from A2 to A6, column B holds names, column C holds salaries. The ID to look up sits in D3.
=INDEX(C2:C6,MATCH(D3,A2:A6,0))
MATCH walks down A2:A6 and finds E-103 at position 3. INDEX then reads position 3 of C2:C6, which is 88000. Two ranges, each in one place, and no count of how many columns sit between the ID and the salary.
Example 2: looking left
A price list has products in A2:A5 and prices in B2:B5, and someone asks which product costs 3.25:
=INDEX(A2:A5,MATCH(3.25,B2:B5,0))
MATCH searches the price column and returns 2, and INDEX reads the second product, Junction box. The return range sits to the left of the search range, which is the arrangement VLOOKUP cannot handle at all. That is why the pair is worth learning even in workbooks that use XLOOKUP.
Example 3: a two-way lookup
INDEX also takes a column number, which turns it into a grid reader. With months across the top row and product names down column A:
=INDEX(B2:M20,MATCH(J2,A2:A20,0),MATCH(K2,B1:M1,0))
The first MATCH finds the product's row, the second finds the month's column, and INDEX returns the cell where they meet. One formula reads any cell of the table, and the report needs two cells of input rather than a rebuilt formula per month.
What goes wrong
| Error | What it means | The fix |
|---|---|---|
#N/A | MATCH did not find the value | Check for spaces and numbers stored as text |
#REF! | The position from MATCH is bigger than the range in INDEX | Make both ranges the same height |
#VALUE! | MATCH or INDEX was given too few arguments | MATCH needs three, INDEX needs two |
| A value from the wrong row | Two ranges start on different rows | Start both on the same row |
#NAME? | The name is misspelled, or quotes are missing | Let Excel complete the name |
MATCH returns a position inside its own range, and INDEX tries to use that position inside a different range. If the search range is taller than the return range, a match near the bottom produces a position INDEX cannot reach. The result is #REF! rather than a wrong number, which is a good failure: it points straight at the mismatch.
#N/A is the quieter one. A trailing space in the ID, or one table where IDs are text and another where they are numbers, is enough. TRIM on the criteria and EXACT on two cells side by side will show you which of the two it is.
Alternatives
=XLOOKUP(A2, A2:A6, C2:C6) replaces the nested pair with one function, and it is in Excel 2021 and Microsoft 365. It also takes a fourth argument for the missing case, so =XLOOKUP(A2, A2:A6, C2:C6, "not on file") removes the =IFERROR( wrapper that INDEX and MATCH need.
=VLOOKUP(A2, A2:C6, 3, FALSE) is the older single-function route, and it stays fine while the return column sits to the right of the key. The pair is still worth knowing because it looks left, survives an inserted column, and runs in Excel 2019 and older, where XLOOKUP does not exist.
Other apps
Google Sheets supports INDEX and MATCH with the same zero for an exact match, so =INDEX(C2:C6, MATCH(A2, A2:A6, 0)) moves between the two products unchanged. Sheets also accepts a whole range as the MATCH column, which is how a two-way lookup is usually written there.
VBA has no INDEX method that reads well. The usual route is WorksheetFunction.Match for the position and Cells(row, column) for the value, or WorksheetFunction.Index when both arguments are built as ranges.
Practice
The exercise below shows the directory and asks for a salary by ID. Type the INDEX around the MATCH, with the 0 as the third argument of MATCH, then press Check. A #N/A message means the value was not found in the range you named.
Try it: exercise 1 of 5
In E3, return the salary for the ID in D3 with INDEX and MATCH instead of VLOOKUP.
88000 is Cy's salary, on row 4 of the sheet and third in C2:C6. Check which column INDEX reads and which one MATCH searches.
Show the answer
Cell E3 holds 88000Open this exercise in the lesson (INDEX + MATCH: lookup either way)
Try it: exercise 2 of 5
In B7, read the units at the row in B5 and the column in B6, counting from the first product and from Q1.
Three rows down from B2 is row 4, Gizmo. Two columns across is Q2, and the figure there is 160.
Show the answer
Cell B7 holds 160Open this exercise in the lesson (INDEX: a value at a row and column)
Try it: exercise 3 of 5
In B7, find the position of the month in B6 within A2:A5, counting the first row as 1.
Jan is 1, Feb is 2, Mar is 3. Counting starts at the top of A2:A5, not the top of the sheet.
Show the answer
Cell B7 holds 3Open this exercise in the lesson (MATCH: find a value's position)
Try it: exercise 4 of 5
In B7, return the product name in A2:A5 for the SKU in B6 with INDEX and MATCH.
3.25 is the second price down in B2:B5, so the answer is the second product name, Junction box.
Show the answer
Cell B7 holds Junction boxOpen this exercise in the lesson (INDEX MATCH: look left of the key)
Try it: exercise 5 of 5
In B7, read the units for the region in B5 and the quarter in B6 from the grid B2:D4.
South is the second region down and Q3 is the third column across, so INDEX reads 130.
Show the answer
Cell B7 holds 130Questions
Why not just use VLOOKUP?
Because VLOOKUP counts columns inside a block and only searches the first column of it. Add a column to the table and the count is wrong; put the key to the right of the value and VLOOKUP cannot reach it. INDEX+MATCH reads each range by name and starts from its own first cell, so both problems disappear.
Should I use XLOOKUP instead?
In a new workbook, yes if your version of Excel has it. XLOOKUP does the same job with two ranges and no nesting: =XLOOKUP(D3,A2:A6,C2:C6). INDEX+MATCH is still worth knowing because it works in every version, including files that older colleagues open and web versions that lag behind.
What does the 0 in MATCH do?
It asks for an exact match and nothing else. Leave it out and MATCH assumes the search column is sorted ascending and returns the nearest value below, so the cell shows a wrong answer, not an error. 1 and -1 are for sorted bands and are rarely what you want.
Why do the two ranges need to start on the same row?
Because MATCH answers with a position and INDEX spends it. If the search range starts on row 2 and the return range on row 3, position 1 points at different rows, and every answer belongs to a neighbour. Keep the heights equal too. A shorter return range leaves INDEX positions it cannot reach, which is the usual cause of #REF!.