Skip to content

All-Access: every lesson, no ads. $8.25€8.25US$8.25$4.08US$4.08$2.41US$2.41₹224.91MX$74.91 a month, billed yearly. Free to start. Cancel any time

See plans
All-Access, no ads: every lesson, course and template. $8.25€8.25US$8.25$4.08US$4.08$2.41US$2.41₹224.91MX$74.91 a month. + GST at checkoutSee the plans →

Functions

INDEX MATCH vs VLOOKUP: Which Lookup Should You Use?

By the LogicExcel Editorial Team5 min read905 wordsPractice this

Verdict: use VLOOKUP for a quick lookup when the value you search for is in the first column of the table. Use INDEX and MATCH when it is not, when columns may be inserted later, or when you need a two-way lookup. INDEX and MATCH are more flexible and work in every version of Excel; VLOOKUP is shorter to write.

Ready to practice?

Apply what you just learned with interactive exercises.

Start Lesson 23

Both approaches find a value in one column and return something from the same row. VLOOKUP does it in one function. INDEX and MATCH split the job in two: MATCH finds the row, and INDEX returns the value from that row. That split is why INDEX and MATCH can do things VLOOKUP cannot.

The short answer

  • VLOOKUP is one function, so it is faster to type and easier to read, but it searches only the first column of its table.
  • INDEX and MATCH are two functions, so the formula is longer, but the search column and the return column are separate. That lets you look left and survive inserted columns.
  • Neither is wrong. Pick by the shape of the sheet.

Side by side

VLOOKUPINDEX and MATCH
Formula=VLOOKUP(lookup, table, col_number, FALSE)=INDEX(return_range, MATCH(lookup, lookup_range, 0))
Looks left of the lookup columnNoYes
If you insert a column in the tableThe column number does not follow, so the result can changeThe ranges adjust, so the result stays right
Exact matchType FALSE (or 0) as the last argumentType 0 as the last argument of MATCH
Two-way lookup (row and column)Needs a second functionTwo MATCH functions inside one INDEX
Excel versionsAllAll

The same lookup, both ways

The same price list, with the SKU to find, A103, typed in E2.

ABC
1SKUProductPrice
2A101Notebook4.50
3A102Pen1.20
4A103Stapler8.90
5A104Folder2.10
6A105Scissors6.40
To return the price for the SKU in E2:
=VLOOKUP(E2,A2:C6,3,FALSE)
=INDEX(C2:C6,MATCH(E2,A2:A6,0))

Both return 8.90. In the INDEX and MATCH version, MATCH(E2,A2:A6,0) returns 3, the position of A103 in the SKU column, and INDEX(C2:C6,3) returns the third price.

To look left, find the SKU for the product name typed in F2:

=INDEX(A2:A6,MATCH(F2,B2:B6,0))

VLOOKUP cannot return a column to the left of the one it searches.

For a two-way lookup, find the price in a table with products down the side and months across the top:

=INDEX(B2:D6,MATCH(G1,A2:A6,0),MATCH(G2,B1:D1,0))

The first MATCH picks the row, the second picks the column, and INDEX returns the cell where they meet.

Which one should you use?

Choose VLOOKUP for a short, one-off lookup where the key is the first column and the table will not change shape.

Choose INDEX and MATCH when the key is not the first column, when you expect to insert or move columns, when you need a two-way lookup, or when you are building a model other people will edit.

Consider XLOOKUP if everyone who opens the file has Excel 2021 or later or Microsoft 365: it does the same job as INDEX and MATCH in one function. See XLOOKUP vs VLOOKUP.

Practice the difference

Type both formulas yourself. The INDEX MATCH lookup lesson is marked as you go, and the VLOOKUP lesson covers the older function. If you want a path rather than one function, the Excel courses start free, and each one ends in a marked exam. More on each function: INDEX and MATCH and VLOOKUP.

FAQ

Is INDEX MATCH better than VLOOKUP?

It is more flexible, not always better. INDEX and MATCH can look left, keep working when you insert a column and handle two-way lookups. VLOOKUP is shorter and easier to read, so it is a fine choice when the key is the first column and the table is stable.

What does the 0 in MATCH mean?

The 0 is the match type and means exact match. MATCH(E2,A2:A6,0) looks for the value in E2 and returns its position, or an error if it is not there. Leave it out and MATCH assumes a sorted list and can return a nearby position.

Does INDEX MATCH work in every version of Excel?

Yes. INDEX and MATCH are in every current version of Excel, including the ones that do not have XLOOKUP, so a file that uses them opens correctly in older versions.

Should I learn XLOOKUP instead of INDEX MATCH?

Learn XLOOKUP if your Excel has it, because it does the same job in one function. Learn INDEX and MATCH too if you share files with people on older versions or use sheets that already contain them.

Keep going

Guides people read next.