How to Use INDEX MATCH in Excel (Step by Step)
· 7 min read
To use INDEX MATCH, let MATCH find the row number of the value you are looking for, then let INDEX return the cell at that row from the column you want. Always put 0 as MATCH's last argument so it looks for an exact match:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))It works in every version of Excel, which is why it is still everywhere in shared workbooks. The one rule that matters most: the return range and the lookup range must start and end on the same rows.
The example table
A kitchen supplies stock list. Codes are in column A, stock counts in column C. Column F holds every formula from the steps below, plus the AI mistake from the end of this guide.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | In stock | Formula | Result | |
| 2 | K-310 | Coffee beans 1kg | 42 | Step 1: MATCH | 4 | |
| 3 | K-311 | Paper cups | 120 | Step 2: INDEX | 8 | |
| 4 | K-312 | Sugar sticks | 300 | Step 3: INDEX MATCH | 8 | |
| 5 | K-313 | Kettle | 8 | Looking left | K-313 | |
| 6 | K-314 | Tea bags | 75 | AI's formula | 75 | |
| 7 | Fixed | 8 |
How to use INDEX MATCH, one function at a time
The combination is easier to trust once you have seen each half on its own.
Step 1: MATCH finds the position
=MATCH("K-313", A2:A6, 0)Result: 4. K-313 is the 4th cell of A2:A6. Note that MATCH returns a position inside the range, not a row number on the sheet. The 4th cell of A2:A6 is on sheet row 5.
The third argument is the match type. 0 means exact. If you leave it out, MATCH uses 1, which assumes the list is sorted and returns the closest smaller value. On codes and names that gives wrong answers without any error.
Step 2: INDEX returns the nth cell
=INDEX(C2:C6, 4)Result: 8, the 4th cell of C2:C6.
Step 3: put MATCH inside INDEX
=INDEX(C2:C6, MATCH("K-313", A2:A6, 0))Result: 8. MATCH works out the 4, INDEX uses it. In a real sheet you would point at a cell instead of typing the code, for example MATCH(H2, A2:A6, 0).
Filling it down
If you fill the formula down a list of codes, lock both ranges with $ so they do not slide:
=INDEX($C$2:$C$6, MATCH(H2, $A$2:$A$6, 0))H2 moves as you fill, the stock list stays fixed. See absolute references if the $ signs are new to you.
Looking to the left
You know the product name and want the code. The code is in column A, to the left of the names. Just swap which range you search and which you return:
=INDEX(A2:A6, MATCH("Kettle", B2:B6, 0))Result: K-313. This is the classic reason analysts learned INDEX MATCH: VLOOKUP can only return columns to the right of the one it searches.
Two-way lookup: row and column
INDEX can take a row number and a column number. Give it two MATCH functions and you can look up a value in a grid. Units sold by region and month, with the region and month to find in F2 and G2:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | Region to find | Month to find | Units | |
| 2 | North | 120 | 135 | 128 | East | Feb | 150 | |
| 3 | South | 98 | 102 | 110 | ||||
| 4 | East | 143 | 150 | 139 | ||||
| 5 | West | 110 | 118 | 125 |
To get East in February:
=INDEX(B2:D5, MATCH("East", A2:A5, 0), MATCH("Feb", B1:D1, 0))Result: 150. The first MATCH finds East as the 3rd row of the grid, the second finds Feb as the 2nd column. Change either label and the answer follows.
INDEX MATCH multiple criteria
Sometimes one column is not enough to find the row. Here each account lead covers one region and one category:
| A: Region | B: Category | C: Lead | |
|---|---|---|---|
| 2 | North | Office | Priya |
| 3 | North | Kitchen | Tom |
| 4 | West | Office | Ana |
| 5 | West | Kitchen | Marco |
| 6 | South | Office | Lena |
Who leads West Kitchen?
=INDEX(C2:C6, MATCH(1, (A2:A6="West")*(B2:B6="Kitchen"), 0))Result: Marco. Each comparison gives TRUE or FALSE for every row. Multiplying them turns TRUE into 1 and FALSE into 0, so only a row where both are true becomes 1. MATCH then finds the position of that 1, which is the 4th row.
In Microsoft 365 and Excel 2021 or later you press Enter as usual. In Excel 2019 and older this is an array formula, so confirm it with Ctrl+Shift+Enter. If you skip that in an older version, you get a wrong result or an error.
If two rows meet every condition, MATCH returns the first one. Check that your criteria really pick out one row.
INDEX MATCH vs VLOOKUP
Short version: INDEX MATCH can return columns to the left, does not break when someone inserts a column, and makes you pick exact match explicitly. VLOOKUP is shorter to type. If everyone has a recent version, XLOOKUP does the same job in one function. We compare all three side by side in VLOOKUP vs XLOOKUP vs INDEX MATCH, and XLOOKUP in Excel covers the newer function on its own.
Common mistakes
Ranges that start on different rows. This is the mistake to look for when an AI assistant writes INDEX MATCH for you. It looks like this:
=INDEX(C3:C7, MATCH("K-313", A2:A6, 0))There is no error. MATCH still says "4th", but the 4th cell of C3:C7 is C6, so the formula returns 75, the stock for Tea bags, instead of 8. Click F6 and then F7 in the stock sheet at the top: the blue cells show the shifted range next to the fixed one. Every row of a filled-down column is off by one in the same direction, so the numbers look believable. To spot it, read the two ranges in the formula bar and check that the row numbers match: C2:C6 with A2:A6.
Leaving out the 0. MATCH(E2, A2:A6) defaults to approximate match. On an unsorted list or a missing code it can return a neighbour's value instead of #N/A.
Ranges of different sizes. If the lookup range has 5 rows and the return range has 6, results can still look right for the first rows. Make them the same size so the positions line up.
Hiding #N/A too early. Wrapping everything in IFERROR(..., "") turns a missing code into an empty cell. Use IFNA, which only catches "not found", and only after you have checked why values are missing. See IFERROR in Excel.
Values that look the same but are not. A trailing space or a number stored as text will not match. If one row fails and its neighbours work, compare =LEN() of the two values.
FAQ
How do I use INDEX MATCH in Excel?
Write =INDEX(column_to_return, MATCH(value, column_to_search, 0)). MATCH finds the position of the value, INDEX returns the cell at that position. Keep both columns on the same rows.
How do I do INDEX MATCH with multiple criteria?
Multiply the conditions and match the number 1: =INDEX(C2:C6, MATCH(1, (A2:A6="West")*(B2:B6="Kitchen"), 0)). In Excel 2019 or older, confirm with Ctrl+Shift+Enter.
Is INDEX MATCH better than VLOOKUP?
For anything you will keep and share, usually yes. It can look left, survives inserted columns and does not depend on a typed column number.
Why does my INDEX MATCH return the wrong value?
Most often the INDEX range and the MATCH range start on different rows, so every result is shifted. The second most common cause is a missing 0 in MATCH.
Should I use XLOOKUP or INDEX MATCH?
Use XLOOKUP if everyone who opens the file has Excel 2021, Microsoft 365 or Excel for the web. Use INDEX MATCH if someone may open it in an older version.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Every price is the wrong one” →