Cellwise Start free

Excel guide · 4 min read

VLOOKUP vs INDEX MATCH

Quick answerBoth find a value in one column and return a value from the same row. VLOOKUP is shorter but only returns columns to the right of the lookup column, and its column number breaks when columns are inserted. INDEX MATCH can look in any direction and keeps working when the layout changes. In Excel 365 or 2021, XLOOKUP does both jobs in one function.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1ProductSKUCategoryPriceStock
2Desk lampSK-1041Lighting3458
3Monitor armSK-1042Desk5922
4Standing deskSK-1043Desk3499
5Desk chairSK-1044Seating12914
6FootrestSK-1045Seating2740
7Cable traySK-1046Desk1875
8Floor lampSK-1047Lighting7911
9WebcamSK-1048Tech6431

Download this sheet as an Excel practice file (.xlsx)

The same lookup, both ways

You want the price of the Desk chair. VLOOKUP searches the first column of A2:E9 for "Desk chair" and returns the 4th column of that range. The FALSE at the end means exact match.

INDEX MATCH splits the job in two. MATCH("Desk chair",A2:A9,0) finds the position, 4, and INDEX(D2:D9,4) returns the 4th price: =INDEX(D2:D9,MATCH("Desk chair",A2:A9,0)). Both give 129. The 0 in MATCH means exact match, like FALSE in VLOOKUP.

=VLOOKUP("Desk chair",A2:E9,4,FALSE)

Result: 129

129: the Desk chair's price, the same as the INDEX MATCH version.

Difference 1: looking to the left

A supplier sends SKUs and you need product names. The SKU is in column B, the name is in column A, to its left. VLOOKUP can't do it: it always searches the first column of its range and returns something to the right. You'd have to move columns or bolt on a workaround.

INDEX MATCH doesn't care which side the answer is on. MATCH finds the SKU's row in column B, INDEX returns column A from that row.

=INDEX(A2:A9,MATCH("SK-1047",B2:B9,0))

Result: Floor lamp

Floor lamp: the product to the left of SKU SK-1047, which VLOOKUP can't return.

Difference 2: inserting or moving columns

VLOOKUP's column number is typed in. Insert a Supplier column between Category and Price, and Price becomes column 5, but the formula still says 4 and quietly returns the Category instead. No error, just a wrong answer on every row.

INDEX MATCH points at the Price column itself, so Excel updates the reference when columns move. If you stay with VLOOKUP, let MATCH find the column number from the headers, so it follows the header instead of a fixed position.

=VLOOKUP("Desk chair",A2:E9,MATCH("Stock",A1:E1,0),FALSE)

Result: 14

14: the Desk chair's stock, with the column number found from the header "Stock" instead of typed in.

Difference 3: two-way lookups

For a lookup by row and column (any product, any field picked from a drop-down), use INDEX with two MATCH functions: one for the row, one for the column. It reads well once you've seen it, and it's the standard pattern for grids.

VLOOKUP with MATCH for the column number does the same as long as the lookup column stays first. The two-way lookup guide has more examples.

=INDEX(A2:E9,MATCH("Webcam",A2:A9,0),MATCH("Stock",A1:E1,0))

Result: 31

31: the Webcam's stock, found by product row and by the Stock header column.

Difference 4: multiple criteria

Which Lighting product has fewer than 20 in stock, and what does it cost? VLOOKUP needs a helper column that joins the criteria. INDEX MATCH handles it in one formula by matching 1 against a test that multiplies the conditions: each row is 1 only when both are true.

In Excel 2019 and older this needs Ctrl+Shift+Enter; in Excel 365 and 2021 a plain Enter works. The index match multiple criteria guide explains the pattern step by step.

=INDEX(D2:D9,MATCH(1,(C2:C9="Lighting")*(E2:E9<20),0))

Result: 79

79: the Floor lamp's price, the only Lighting product with fewer than 20 in stock.

The trap they share, and which to pick

Leave out VLOOKUP's last argument and it does an approximate match, which on an unsorted list can return a wrong row with no error. MATCH has the same default: leave out its 0 and it assumes a sorted list. Always type FALSE or 0 for exact lookups. Both are also case-insensitive, so "desk chair" finds "Desk chair".

Speed isn't a reason to choose in current Excel: on everyday lists of a few thousand rows you won't notice a difference.

Pick VLOOKUP for a quick lookup in a table where the key is in the first column and the layout won't change. Pick INDEX MATCH when you look left, the layout may change, you need a two-way lookup, or the file has to work in older Excel. Pick XLOOKUP when everyone who opens the file has Excel 365, 2021 or later: it looks in any direction, defaults to exact match and has a built-in if-not-found value.

Common mistakes

Leaving out FALSE in VLOOKUP or 0 in MATCH

Both default to approximate match. Type FALSE or 0 for exact lookups.

Hard-coding VLOOKUP's column number

Use MATCH on the headers, or switch to INDEX MATCH so inserted columns don't break it.

INDEX and MATCH ranges of different sizes

Start and end both ranges on the same rows, such as D2:D9 and A2:A9, or the result comes from the wrong row.

Not locking ranges before copying down

Use $A$2:$E$9 so the lookup range doesn't slide when you fill the formula.

FAQ

Is INDEX MATCH better than VLOOKUP?

It's more flexible: it can look left and doesn't break when columns are inserted. VLOOKUP is shorter and fine for simple lookups where the key is in the first column.

Is INDEX MATCH faster than VLOOKUP?

On everyday lists in current Excel the difference isn't noticeable. Choose based on flexibility and who will open the file, not speed.

Should I learn VLOOKUP or INDEX MATCH?

Learn to read both, because you'll meet them in other people's files. For new work in Excel 365 or 2021, XLOOKUP is easier than either.

Why does VLOOKUP return the wrong value?

Usually the last argument is missing, so it does an approximate match, or a column was inserted and the column number now points at the wrong column.

Functions used

Related guides

Updated