MATCH finds which row your lookup value sits in, and INDEX returns the value from that row in the column you want. Put them together as =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). The pair works even when the lookup column isn't on the left of your data, which is exactly where VLOOKUP gives up.
If you've ever built a VLOOKUP and had it refuse to find a value you can see on the sheet, this is usually why: the column you're searching sits to the right of the one you want back.
What INDEX MATCH is and what it is not
It's two functions doing one job. VLOOKUP can only look up a value from left to right, so the lookup column has to be left of the column you return. When your sheet isn't laid out that way, Microsoft's lookup guide recommends using INDEX and MATCH together instead. MATCH finds the position; INDEX fetches the value at that position.
It isn't a separate function you'll find in a menu, and it isn't a fix for every lookup. Each half does something simple on its own. The combination works because MATCH's answer, a row number, is exactly what INDEX needs to be told.
Writing the formula, one piece at a time
- Write MATCH to find where your value sits, with 0 as its last argument.
- Wrap it in INDEX, using the MATCH result as the row argument and the range holding the values you want returned.
- Add a column number if the INDEX range is wider than one column.
Seeing MATCH on its own
For example, MATCH finds Chicago in B1:B11 at row 4. So MATCH hands back a position, never the value itself.
Handing the position to INDEX
INDEX then uses that number as its row argument and returns what sits in that row of the column you want. Its syntax is INDEX(array, row_num, [column_num]).
Choosing the column number
The column number is the optional third part of the syntax. In the worked example below the range runs A to C, and a 1 returns the first of those columns. Change that one number and the same formula returns a different field from the matched row.
Building it in two stages
If long formulas make you nervous, split the job. Write the MATCH part on its own in a spare cell and check it returns the row you expect. Once it does, copy it inside INDEX as the row argument and delete the spare cell. You'll know straight away which half is wrong if the answer looks off.
The full worked example
Put together, the formula reads =INDEX(A2:C33,MATCH("Atlanta",B2:B33,0),1). MATCH finds Atlanta in column B, INDEX takes that row from the table running A to C, and the final 1 tells it to return the first column.
The #REF! error and what's behind it
This is the catch. If the row or column number you feed INDEX points outside the range, INDEX returns a #REF! error. As the INDEX function reference puts it, both numbers must point to a cell within the array.
In practice that tends to happen when the INDEX range and the MATCH range don't line up. If MATCH searches a longer range than the one INDEX returns from, a match near the bottom gives a row number INDEX can't reach. Make both ranges start and end on the same rows.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
A quick test when you see #REF!: put the MATCH part in a spare cell on its own. If the number it returns is bigger than the number of rows in your INDEX range, you've found the problem.
A column number bigger than the width of your INDEX range does the same thing. A range running A to C has three columns, so a 4 there has nowhere to point.
When this is the wrong fix
- Lookup column already on the left? Plain VLOOKUP is simpler.
- You already know the row number you want: INDEX on its own returns it, with no MATCH needed.
- You're getting #REF! and haven't checked your ranges: fix the range sizes first, not the formula style.
If your version of Excel has it, it's worth learning how XLOOKUP works too.
Frequently asked questions
Why does my INDEX MATCH return the wrong row?
Check that the INDEX range and the MATCH range start on the same row. If they're offset, the position MATCH returns lands on a different row inside INDEX.
Do I need the column number in INDEX?
It's optional in the syntax. With a single-column return range you can leave it out; with a wider range it picks which column comes back.
Is there a newer alternative to INDEX and MATCH?
Yes. Microsoft points to XLOOKUP and XMATCH as improved versions of these older functions.
What does INDEX return when I give it both a row and a column?
The value at the intersection of that row and that column within the range.
Does INDEX work without MATCH?
Yes. On its own it returns whatever sits at the row and column numbers you hand it.
