Quick answer
The INDEX MATCH formula uses MATCH to find a position and INDEX to return the value at that position.
=INDEX($G$2:$G$100,MATCH(A2,$F$2:$F$100,0))Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))When to use it
Lookup formulas connect tables together. Dynamic array formulas go further by returning lists, tables, and spill ranges that update automatically when the source data changes.
- Return a price from a product table
- Find an employee name from an ID
- Filter open orders into a report
- Create sorted or unique lists
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Lookup value such as SKU, ID, or name |
| F:G | Reference table with the match column and return column |
| B | Returned result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic INDEX MATCH example
=INDEX($G$2:$G$100,MATCH(A2,$F$2:$F$100,0))Use this as the first working version with a small sample table.
Return a friendly message when nothing is found
=IFERROR(INDEX($G$2:$G$100,MATCH(A2,$F$2:$F$100,0)),"Not found")Give users a readable result for missing records.
Clean the lookup value first
=INDEX($G$2:$G$100,MATCH(TRIM(A2),$F$2:$F$100,0))Remove extra spaces from the lookup value before matching.
Use Excel Table references
=INDEX(Products[Price],MATCH([@SKU],Products[SKU],0))Tables make lookup formulas easier to maintain as rows are added.
Return blank only for missing records
=IFNA(INDEX($G$2:$G$100,MATCH(A2,$F$2:$F$100,0)),"")Use IFNA when a missing match is expected but other errors should remain visible.
Create a dashboard label
=IFNA("Result: "&INDEX($G$2:$G$100,MATCH(A2,$F$2:$F$100,0)),"Result not found")Turn a lookup value into a readable summary cell.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- The lookup range and return range do not have matching sizes.
- The lookup value has extra spaces or different formatting than the source table.
- An approximate match is used when an exact match is required.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the INDEX MATCH formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.