VLOOKUP vs INDEX MATCH: Differences and When to Use Each

The short answer to VLOOKUP vs INDEX MATCH: use VLOOKUP for a simple lookup to the right, and use INDEX MATCH when you need to look left, expect columns to move, or need a two-way lookup. Both work in every version of Excel. This guide is for anyone who knows VLOOKUP and wants to know when INDEX MATCH is worth the extra typing, for work or for an Excel test.

VLOOKUP vs INDEX MATCH at a glance#

INDEX MATCH does everything VLOOKUP does, plus a few things VLOOKUP can't, in exchange for a longer formula.

How VLOOKUP and INDEX MATCH work#

VLOOKUP does the search and the return in one function. INDEX MATCH splits the job into two steps: MATCH finds the row, and INDEX brings back the value.

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
=INDEX(array, row_num, [column_num])
=MATCH(lookup_value, lookup_array, 0)

• VLOOKUP searches the first column of table_array and returns the value from column number col_index_num of the same row. The VLOOKUP function guide covers each part.
• INDEX returns the item at a position in a range. =INDEX(B2:B7,3) returns the 3rd name. See the INDEX function guide.
• MATCH does the opposite: it finds a value and returns its position, such as 4 for the 4th row. See the MATCH function guide.

Put MATCH inside INDEX and you get a lookup: MATCH works out the position, and INDEX reads that position from any column you choose.

The same lookup solved both ways#

The clearest comparison is the same task on the same data. Here is a small staff list:

Task 1: find a department by ID (a lookup to the right)#

With E104 in F2, both formulas return Sales:

=VLOOKUP(F2,A2:D7,3,FALSE)
=INDEX(C2:C7,MATCH(F2,A2:A7,0))

VLOOKUP finds E104 in column A and returns column 3 of the table. In the second formula, MATCH finds E104 at position 4 of A2:A7, and INDEX returns the 4th item of C2:C7. For a lookup like this, VLOOKUP is shorter and just as correct.

Task 2: find an ID by name (a lookup to the left)#

Now F2 holds Julia Santos, and you want her ID. The IDs are in column A, to the left of the names. VLOOKUP can't do this: it always searches the first column of its table, so =VLOOKUP(F2,A2:D7,1,FALSE) looks for the name among the IDs and returns #N/A. INDEX MATCH can:

=INDEX(A2:A7,MATCH(F2,B2:B7,0))

MATCH finds Julia Santos at position 5 of B2:B7, and INDEX returns the 5th ID: E105.

Task 3: a two-way lookup#

F2 holds E106 and F3 holds a column header, Salary. You want the value where that row and that column meet. Both functions can do it with MATCH finding the column number:

=VLOOKUP(F2,A2:D7,MATCH(F3,A1:D1,0),FALSE)
=INDEX(A2:D7,MATCH(F2,A2:A7,0),MATCH(F3,A1:D1,0))

Both return 57,000. MATCH(F3,A1:D1,0) finds Salary at position 4 in the header row. The INDEX version is more flexible, because its row MATCH can search any column, not only the first.

Task 4: what happens when you insert a column?#

VLOOKUP's column number is typed by hand, so it doesn't change when the table changes. INDEX MATCH points at ranges, and Excel moves ranges for you.

Say a colleague inserts a new Location column between Name and Department. Excel updates the VLOOKUP table from A2:D7 to A2:E7, but the 3 stays a 3. Column 3 is now Location, so the formula quietly returns a city instead of a department, with no error. The INDEX MATCH formula changes from C2:C7 to D2:D7 by itself and keeps returning the department.

If a column is deleted and VLOOKUP's number is now past the edge of the table, it shows #REF! instead.

When to use VLOOKUP#

Use VLOOKUP when the lookup is simple and the table is stable. It is a good choice when:

• The value you want is to the right of the column you search, as in Task 1.
• The table won't change shape, or you control it.
• A job test asks for VLOOKUP by name. Many still do; our guide on how to pass an Excel test for a job interview covers what else they check.
• Colleagues need to read the formula. One function with four parts is easier to follow than two nested ones.

When to use INDEX MATCH#

Use INDEX MATCH when you need flexibility and XLOOKUP isn't available. It is the better choice when:

• You need to look left, as in Task 2.
• Columns may be inserted or moved in a shared file.
• You need a two-way lookup that matches a row and a column header.
• You want a calculated position, such as the ID of the highest-paid employee: =INDEX(A2:A7,MATCH(MAX(D2:D7),D2:D7,0)) returns E105.
• The file must open in Excel 2019 or older, where XLOOKUP shows #NAME?.

If you have Excel 2021, Excel 2024 or Microsoft 365, XLOOKUP does all of this in one function. Our comparison of INDEX MATCH vs XLOOKUP explains when each one wins.

Common mistakes with VLOOKUP and INDEX MATCH#

Most wrong answers come from a few habits that are easy to fix:

1. Leaving out the exact-match part. VLOOKUP without FALSE and MATCH without 0 both use an approximate match. On unsorted data they can return the wrong row with no error.
2. Ranges that start on different rows. In =INDEX(A2:A7,MATCH(F2,B1:B7,0)), MATCH counts the header too, so every position is one too high. Julia Santos would return E106, Ken Ito's ID. Start both ranges on the same row.
3. Counting VLOOKUP's column from column A of the sheet. The count starts at the first column of table_array. In a table that starts at column F, column G is 2.
4. Swapping INDEX and MATCH's ranges. INDEX gets the column you want back. MATCH gets the column you search.
5. Not locking ranges before filling down. Use $A$2:$A$7 (press F4 on Windows or ⌘T on a Mac) before you fill with Ctrl+D (⌘D on a Mac).
6. Hidden spaces or numbers stored as text. "E104 " with a trailing space doesn't match "E104", so both functions return #N/A. Clean the value with TRIM first.

Which one do Excel tests and interviews ask for?#

Both. Practical tests often ask for VLOOKUP by name, and interviewers often ask you to explain the difference between VLOOKUP and INDEX MATCH. A good spoken answer is one sentence: "VLOOKUP can only look right and breaks when columns move; INDEX MATCH separates the search column from the return column, so it can look in any direction."

For more questions like this, see our list of Excel interview questions with answers you can practise. Once lookups feel easy, the same "simple vs flexible" choice comes up with SUMIF vs SUMIFS.

Our recommendation#

Learn VLOOKUP first, then learn INDEX MATCH. VLOOKUP is the quickest way to do a simple lookup to the right, and you need it for tests and for reading older files. INDEX MATCH is the tool to reach for when you need to look left, when the table may change, or when the file must work in older Excel.

In short: VLOOKUP for simple lookups to the right, INDEX MATCH for everything else when XLOOKUP isn't available.

Practise it#

Try the exercise on this page: it is a left lookup that VLOOKUP can't do. Then practise VLOOKUP exact match in a live spreadsheet and work through the INDEX and MATCH lesson, which ends with a two-way lookup.