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) — 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.
Result
| 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 | Slower on large datasets (searches the entire row width) | Faster — only searches the lookup column |
| 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 Category AND Size? 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.
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.
Start a Project