Cellwise

Excel guide · 2 min read

How to use INDEX MATCH in Excel

Write =INDEX(return_range,MATCH(lookup_value,lookup_range,0)). MATCH finds the row number of your value in the lookup column, and INDEX returns the item in that row of the column you want. The 0 forces an exact match.

Sample data

Every formula on this page was calculated live against this sheet.

ABCDEFG
1ProductSKUPriceStockFind SKUHUB-07
2Desk lampLMP-1034.99120
3Monitor armARM-228945
4USB-C hubHUB-0724.5300
5WebcamCAM-1559.950
6KeyboardKEY-314988

How the two parts work

MATCH answers "which row is it in?". =MATCH("HUB-07",B2:B6,0) returns 3, because HUB-07 is the third item in that range.

INDEX answers "what is in row 3 of this column?". =INDEX(C2:C6,3) returns 24.5. Put MATCH inside INDEX and you have a lookup.

fx=MATCH(G1,B2:B6,0)

Result: 3

Returns 3, the position of HUB-07 in the SKU column.

A basic INDEX MATCH lookup

Here we find the price for the SKU typed in G1. Make sure the return range and the lookup range start and end on the same rows, or you get the wrong row back.

fx=INDEX(C2:C6,MATCH(G1,B2:B6,0))

Result: 24.5

Returns 24.5, the price of HUB-07.

Look up to the left

VLOOKUP can only return columns to the right of the lookup column. INDEX MATCH does not care. Here the SKU is in column B but the product name we want is in column A.

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

Result: USB-C hub

Returns "USB-C hub".

Two-way lookup: match the row and the column

Use a second MATCH to pick the column by its header. INDEX then takes a row number and a column number. Change "Stock" to "Price" and the formula switches columns with no other edits.

fx=INDEX(A2:D6,MATCH("CAM-15",B2:B6,0),MATCH("Stock",A1:D1,0))

Result: 0

Returns 0, the stock level for the webcam.

INDEX MATCH or XLOOKUP?

If you have Excel 365 or 2021, XLOOKUP does the same job in one function: =XLOOKUP(G1,B2:B6,C2:C6). It also has a built-in "not found" argument.

INDEX MATCH is still worth knowing. It works in every version, so it is the safe choice for files shared with people on Excel 2016 or 2019, and you will meet it in older workbooks constantly.

Common mistakes

Leaving out the 0 in MATCH

Without it MATCH defaults to 1, an approximate match that needs sorted data and can return the wrong row. Always write MATCH(value,range,0) for exact lookups.

Ranges of different sizes

If INDEX uses C2:C6 and MATCH uses B1:B6, the row numbers are off by one. Start and end both ranges on the same rows.

#N/A when the value looks present

Check for trailing spaces or numbers stored as text. Wrap the lookup in IFERROR or IFNA for a friendly message once the data is clean.

FAQ

Why use INDEX MATCH instead of VLOOKUP?

It can look left, it does not break when you insert columns, and it only reads the two columns it needs. VLOOKUP relies on a hard-coded column number.

How do I return "Not found" instead of #N/A?

Wrap the formula: =IFERROR(INDEX(C2:C6,MATCH(G1,B2:B6,0)),"Not found"). IFNA works too and only catches #N/A.

Can INDEX MATCH use multiple criteria?

Yes. =INDEX(C2:C6,MATCH(1,(A2:A6="Webcam")*(B2:B6="CAM-15"),0)) finds the row where both are true. In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

Is INDEX MATCH case-sensitive?

No. MATCH treats "hub-07" and "HUB-07" as the same. For a case-sensitive lookup you need EXACT inside the MATCH.

Functions used

Keep learning

Updated 2026-10-31