INDEX MATCH in Excel: The Complete Guide
INDEX MATCH Formula is a Excel function that match finds the row position of your lookup value, and index returns the value at that position from your result column. Formula Genius generates and validates this formula automatically from a plain-English prompt.
INDEX-MATCH is the most powerful lookup formula in Excel. It's faster, more flexible, and more reliable than VLOOKUP.
The Formula
"Look up the price of a product by its name in a table"
=INDEX(B2:B100,MATCH(D2,A2:A100,0))MATCH finds the row position of your lookup value, and INDEX returns the value at that position from your result column. Together they form the most versatile lookup in Excel.
Step-by-Step Breakdown
- MATCH(D2, A2:A100, 0) searches column A for the value in D2 and returns its row number
- The third argument 0 means exact match (most common)
- INDEX(B2:B100, ...) returns the value from column B at the matched row
- Unlike VLOOKUP, the result column can be to the left of the lookup column
Edge Cases & Warnings
- Returns #N/A if the lookup value isn't found
- Returns the first match if duplicates exist
- Empty cells in the lookup range are skipped
- Trailing spaces can prevent matches — use TRIM() to clean data
Examples
"Product="Widget A""
Returns the price from column B for Widget A"Product="Widget Z" (not in list)"
Returns #N/A — wrap with IFERROR for a fallbackFrequently Asked Questions
Why use INDEX-MATCH instead of VLOOKUP?
INDEX-MATCH can look left (not just right), is faster on large datasets, and doesn't break when you insert columns. It's the professional standard.
Is INDEX-MATCH hard to learn?
It takes 5 minutes to learn. MATCH finds the row, INDEX grabs the value. That's it.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.