INDEX/MATCH is better than VLOOKUP because it can return a value to the left of the column it searches, which VLOOKUP cannot do at all. The pattern is =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), where MATCH finds the row and INDEX returns the value from it. It also survives column insertion, because nothing depends on counting columns.
VLOOKUP is the formula most people learn first for matching data between spreadsheets. But it has a frustrating limitation: it can only search the first column of your range and return values to the right. INDEX/MATCH removes that restriction entirely, and once you learn it, you’ll never go back.
The Problem with VLOOKUP
Imagine you have a product database where the Product Name is in column A, the SKU is in column C, and you need to look up a SKU and return the Product Name. With VLOOKUP, you can’t, because VLOOKUP searches the first column and can only return columns to the right. Your SKU is to the right of the Product Name, so VLOOKUP fails.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Name | Category | SKU | Price |
| 2 | Industrial Cleaner | Chemicals | CHM-4201 | $34.50 |
| 3 | Safety Goggles | PPE | PPE-1150 | $12.99 |
| 4 | Hydraulic Pump | Machinery | MCH-3087 | $489.00 |
| 5 | Welding Rod 6011 | Welding | WLD-6011 | $28.75 |
VLOOKUP can’t do this
=VLOOKUP("MCH-3087", A:D, 1, FALSE): on the Database sheet, this won’t work. VLOOKUP searches column A (Product Name) for “MCH-3087”, which doesn’t exist there. You can’t tell VLOOKUP to search column C and return column A.
The INDEX/MATCH Solution
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
INDEX returns a value from a range at a given position | MATCH finds the position of a value in a range | 0 = exact match
Think of it as two steps: MATCH finds the row number, then INDEX grabs the value from that row in whatever column you want. The lookup column and return column can be anywhere: left, right, or in completely different sheets.
Step-by-Step Example
You have a list of SKUs and need to pull the Product Name from the database above.
Lookup Sheet
| A | B | |
|---|---|---|
| 1 | SKU | Product Name |
| 2 | MCH-3087 | |
| 3 | PPE-1150 | |
| 4 | WLD-6011 |
1Click cell B2 where you want the Product Name.
2Enter the INDEX/MATCH formula:
=INDEX(Database!A:A, MATCH(A2, Database!C:C, 0))
Database!A:A = the column to return (Product Name) | A2 = the SKU to search for | Database!C:C = the column to search in (SKU) | 0 = exact match
3Copy the formula down to rows 3 and 4.
What the INDEX/MATCH lookup returns
| A | B | |
|---|---|---|
| 1 | SKU | Product Name |
| 2 | MCH-3087 | Hydraulic Pump |
| 3 | PPE-1150 | Safety Goggles |
| 4 | WLD-6011 | Welding Rod 6011 |
How it works
MATCH(A2, Database!C:C, 0) finds “MCH-3087” in column C and returns its row position (4). Then INDEX(Database!A:A, 4) grabs the value from row 4 of column A: “Hydraulic Pump.”
VLOOKUP vs INDEX/MATCH: Side by Side
| Feature | VLOOKUP | INDEX/MATCH |
|---|---|---|
| Look up left | Cannot return columns to the left of the search column | Search and return any column in any direction |
| Column changes | Adding or deleting columns breaks the formula (hardcoded column number) | References specific columns, so inserting columns won’t break it |
| Performance | Can be slightly slower on very large datasets | Can be slightly faster on very large datasets: reads only the columns it uses |
| Multiple criteria | Not supported natively | Supports multiple criteria with array matching |
| Ease of learning | Simpler syntax, easier for beginners | Slightly more complex, but more powerful |
Handling Errors with IFERROR
Just like VLOOKUP, INDEX/MATCH returns #N/A when a match isn’t found. Wrap it in IFERROR:
=IFERROR(INDEX(Database!A:A, MATCH(A2, Database!C:C, 0)), "Not Found")
Two-Criteria Matching
Need to match on two columns at once, like finding a price based on both Product Name AND Category (columns A and B above)? INDEX/MATCH handles this with an array formula:
=INDEX(D:D, MATCH(1, (A:A=F2)*(B:B=G2), 0))
Press Ctrl+Shift+Enter instead of just Enter (this creates an array formula). Matches where column A equals F2 AND column B equals G2, then returns the value from column D.
Excel 365 users
If you’re on Microsoft 365, you don’t need Ctrl+Shift+Enter: Excel handles dynamic arrays automatically. Just press Enter normally.
Common questions
Is INDEX/MATCH actually faster than VLOOKUP?
Marginally, on large datasets, because it reads two columns rather than the whole table. On a few thousand rows you will not notice. Flexibility is the real reason to switch, not speed.
Should I rewrite my existing VLOOKUPs?
Only where they are causing problems. A working VLOOKUP is not worth the risk of a rewrite. Use INDEX/MATCH or XLOOKUP for new work instead.
Can INDEX/MATCH match on two criteria?
Yes, by multiplying the conditions: =INDEX(D:D, MATCH(1, (A:A=F2)*(B:B=G2), 0)). In older Excel this must be entered with Ctrl+Shift+Enter.
When Formulas Aren’t Enough
INDEX/MATCH is powerful, but some matching scenarios push beyond what formulas can handle cleanly:
- Fuzzy matching: “Acme Corp” vs “Acme Corporation” vs “ACME” won’t match with formulas
- Matching across dozens of files: opening and referencing 50 workbooks with formulas is impractical
- Data transformation before matching: when source data needs cleanup, reformatting, or standardization first
- Audit trail needed: formulas don’t log which records matched, which didn’t, and why
- Recurring weekly/monthly process: rebuilding formulas each time wastes hours
A custom VBA tool can handle all of this: fuzzy matching, multi-file consolidation, data cleanup, match reporting, and one-click automation for recurring tasks.
“David is the best at Excel, simple as that!”
Tommy W. · PPH
Complex Matching Eating Up Your Week?
Tell us about your data matching challenge and we’ll show you how to automate it.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.