Excel INDEX/MATCH tutorial: better than VLOOKUP for real analysis work

every Excel beginner learns VLOOKUP first, and every Excel intermediate eventually realizes VLOOKUP is the wrong tool for half the problems they use it on.

VLOOKUP can only look right. it breaks the moment you insert a column. it cannot handle a lookup where the answer sits to the left of the key. and once you start chaining VLOOKUPs across sheets, the formula becomes unreadable to anyone who inherits the workbook.

INDEX/MATCH solves all of that. it is older than VLOOKUP, more flexible, faster on large datasets, and once you write it five times the syntax becomes muscle memory. this guide walks you through the function, the common patterns, and the specific business problems where INDEX/MATCH saves you hours.

we will use a realistic dataset throughout: a 500-row sales tracker with columns for Order ID, Date, Salesperson, Region, Product, Units, and Revenue. every example you see will run on this kind of data.

Excel INDEX/MATCH is a two-function lookup combo that returns a value from any column based on a match in any other column. INDEX(array, row_num) returns a cell from a range. MATCH(lookup_value, lookup_array, 0) returns the position of a value. Combine them as =INDEX(return_column, MATCH(lookup_value, lookup_column, 0)) and you get a lookup that works in any direction, survives column inserts, and outperforms VLOOKUP on datasets above 10,000 rows.

why VLOOKUP fails on real spreadsheets

VLOOKUP has four hard limits that show up the moment your work gets serious.

1. it only looks right. VLOOKUP needs the lookup column to be the first column in your range, and it returns a value from a column to the right. if your sales tracker has Salesperson in column C and Order ID in column A, you cannot look up the order ID by salesperson name with a clean VLOOKUP.

2. it breaks when you insert a column. the third argument is a hardcoded number: column 3 means “return the third column in the range.” insert a column anywhere in the middle and VLOOKUP now returns the wrong field, silently. no error, just bad data.

3. it does exact match only by default if you remember the FALSE. if you forget the fourth argument, VLOOKUP defaults to approximate match and returns whatever the closest sorted match is. on unsorted data, that means random wrong answers.

4. it slows down on big sheets. every VLOOKUP scans the lookup column from top to bottom. on a 100,000-row tracker with 50 VLOOKUP columns, recalculation can take 30 seconds. INDEX/MATCH is faster because it only scans once per MATCH.

INDEX and MATCH explained separately

before we combine them, understand what each function does on its own.

INDEX returns a cell from a range

=INDEX(array, row_num, [column_num])

array is the range you want to look inside
row_num is the position from the top
column_num is optional, used only if array is two-dimensional

example: =INDEX(F2:F501, 25) returns the 25th value in the Revenue column.

INDEX by itself is not useful for lookups. you have to know the row number already. that is where MATCH comes in.

MATCH returns the position of a value

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value is what you are searching for
lookup_array is the column you are searching in
match_type is 0 for exact match (always use 0 for lookups)

example: =MATCH("Sarah Lee", C2:C501, 0) returns 25 if Sarah Lee is the 25th name in the Salesperson column.

MATCH gives you a position number. INDEX uses position numbers. now combine them.

the INDEX/MATCH formula in one line

=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))

example: find the revenue for Order ID ORD-1042.

=INDEX(G2:G501, MATCH("ORD-1042", A2:A501, 0))

read it inside-out: MATCH finds where ORD-1042 sits in column A, returns the row number. INDEX takes that row number and returns the value in column G (Revenue).

[SCREENSHOT: Excel formula bar showing =INDEX(G2:G501, MATCH(“ORD-1042”, A2:A501, 0)) and the result $4,200 highlighted in the cell]

INDEX/MATCH vs VLOOKUP at a glance

capability	VLOOKUP	INDEX/MATCH	XLOOKUP
look right	yes	yes	yes
look left	no	yes	yes
survives column insert	no	yes	yes
handles two-way lookup	no	yes	partial
recalc speed on 100k rows	slow	fast	fast
works in older Excel (2016 and below)	yes	yes	no
syntax simplicity	medium	harder	easiest
handles error gracefully	needs IFERROR wrap	needs IFERROR wrap	built-in `if_not_found`

XLOOKUP is the modern winner for new workbooks, but INDEX/MATCH still wins when you share files with people on Excel 2016, 2013, or older. and INDEX/MATCH multiple-criteria patterns are still cleaner than XLOOKUP equivalents.

five INDEX/MATCH patterns you will reuse forever

pattern 1: simple right-to-left lookup

problem: given a salesperson name, return their region. region is in column D, salesperson in column C.

=INDEX(D2:D501, MATCH("Sarah Lee", C2:C501, 0))

works in either direction. swap the column references and the formula stays clean.

pattern 2: looking up to the left of the key

problem: given an Order ID in column A, return the date in column B. simple right-to-left, but here is the version where the key is to the right of the answer:

problem: given a Region in column D, return the first Order ID in column A.

=INDEX(A2:A501, MATCH("APAC", D2:D501, 0))

VLOOKUP cannot do this without rearranging columns. INDEX/MATCH does not care about column order.

pattern 3: two-way lookup (row + column)

problem: you have a pivot-style summary with Months as rows and Products as columns. given a month and a product, return the revenue at that intersection.

=INDEX(B2:F13, MATCH("March", A2:A13, 0), MATCH("Widget Pro", B1:F1, 0))

INDEX accepts two MATCH calls — one for the row, one for the column. impossible to do cleanly with VLOOKUP.

pattern 4: multi-criteria lookup

problem: find revenue where Salesperson is “Sarah Lee” AND Region is “APAC” AND Product is “Widget Pro.”

option A: array formula with & concatenation.

=INDEX(G2:G501, MATCH("Sarah Lee" & "APAC" & "Widget Pro", C2:C501 & D2:D501 & E2:E501, 0))

press Ctrl+Shift+Enter in older Excel. in Excel 365 and Excel 2021, just press Enter — dynamic arrays handle it.

option B: SUMPRODUCT (cleaner if there is only one matching row).

=SUMPRODUCT((C2:C501="Sarah Lee")*(D2:D501="APAC")*(E2:E501="Widget Pro")*G2:G501)

pattern 5: dynamic column lookup

problem: you have a dashboard cell where the user types a column header (“Revenue”, “Units”, “Region”) and the formula returns the matching value for the selected order.

=INDEX($A$2:$G$501, MATCH("ORD-1042", $A$2:$A$501, 0), MATCH(H1, $A$1:$G$1, 0))

the second MATCH finds which column in the header row matches the user input. swap H1 to a different header and the formula returns a different field — no edits needed.

numbered walkthrough: build your first INDEX/MATCH

we will build pattern 1 from scratch on the sales tracker.

open your data. confirm Salesperson is in column C, Region in column D, rows 2-501.
click into an empty cell. type =MATCH(.
for lookup_value, click into the cell holding the salesperson name you want to find (say H2 = “Sarah Lee”). type a comma.
for lookup_array, select C2:C501. type a comma.
for match_type, type 0). press Enter. you should see a number — the row position of Sarah Lee.
now wrap that MATCH inside INDEX. delete the cell, type =INDEX(.
for array, select D2:D501 (the Region column). type a comma.
paste the MATCH formula from step 5 as the second argument: MATCH(H2, C2:C501, 0).
close the parenthesis. press Enter. you see the region for Sarah Lee.
drag the formula down to apply to a list of names in column H.

[SCREENSHOT: Excel sheet with column H listing salesperson names, column I showing INDEX/MATCH results, formula bar showing =INDEX($D$2:$D$501, MATCH(H2, $C$2:$C$501, 0))]

note the dollar signs on the ranges. without them, dragging the formula will shift the lookup ranges and break results from row 2 onward.

handling errors: #N/A, #REF!, #VALUE!

INDEX/MATCH throws three errors regularly. here is what each means and the fix.

#N/A means MATCH could not find the lookup value. either the value does not exist in the lookup column, or there is a hidden formatting difference (trailing space, text vs number, different capitalization).

fix: wrap with IFERROR and a helpful default.

=IFERROR(INDEX(D2:D501, MATCH(H2, C2:C501, 0)), "Not found")

to debug, check if the value really exists with =COUNTIF(C2:C501, H2). if it returns 0, the value is not there or has a formatting mismatch.

#REF! means INDEX is being asked to return a position that does not exist in the array. usually because you sized return_column smaller than the position MATCH returns.

fix: make sure your return column has the same row range as your lookup column.

#VALUE! in older Excel means the array formula is missing Ctrl+Shift+Enter. in Excel 365 it usually means the lookup_value or lookup_array is the wrong type (a range where a single cell is expected).

fix: confirm the function arguments match the function signature.

INDEX/MATCH on Tables instead of ranges

if you converted your data to a Table (Ctrl+T), the formula becomes self-documenting:

=INDEX(SalesData[Region], MATCH(H2, SalesData[Salesperson], 0))

structured references replace the range syntax. the formula auto-expands when you add rows, and a future reader can tell what each argument does without opening the source sheet.

every analysis tutorial on this site recommends Tables for the same reason. see how to analyze data in Excel for the full Tables walkthrough.

when to pick INDEX/MATCH vs XLOOKUP vs VLOOKUP

situation	best choice	why
Excel 365, 2021, or web	XLOOKUP	simpler syntax, built-in error handling
sharing with users on Excel 2016 or older	INDEX/MATCH	XLOOKUP not available
two-way lookup (row and column)	INDEX/MATCH	XLOOKUP cannot do this in one cell
simple right-side lookup, beginner audience	VLOOKUP	wider familiarity, OK for tutorials
100,000+ rows, recalc speed matters	INDEX/MATCH or XLOOKUP	both faster than VLOOKUP
multi-criteria lookup	INDEX/MATCH with `&`	most flexible pattern
Power Query or Power Pivot model	none of these	use a relationship instead

if your stack is moving toward Power Pivot, see the Excel Power Pivot tutorial for the proper replacement: relationships and DAX.

conclusion: INDEX/MATCH earns its keep on real spreadsheets

VLOOKUP is fine for tutorials. INDEX/MATCH is what you reach for when the workbook matters: client deliverables, finance models, ops dashboards, anything where a column insert next quarter would silently corrupt the numbers.

start with pattern 1 — the simple right-to-left lookup — and reach for pattern 3 the next time you need a two-way matrix lookup. once those two are muscle memory, multi-criteria and dynamic-column lookups become natural extensions.

if you are on Excel 365, learn XLOOKUP next (Excel XLOOKUP complete guide) and use INDEX/MATCH only for two-way and legacy-compatibility cases. and if your data lives in Google Sheets, the QUERY function (Google Sheets QUERY function complete guide) replaces both VLOOKUP and INDEX/MATCH for most tasks.

next action: open a real workbook you have built, find the most complex VLOOKUP in it, and rewrite it as INDEX/MATCH. you will spot at least one bug in the original.

Excel INDEX/MATCH Tutorial: Better Than VLOOKUP in 2026