INDEX and MATCH are two Excel functions that can be combined to build flexible lookup formulas. They are especially useful in existing workbooks where the lookup column is not on the left of the return column or where you want to separate the search logic from the returned value.
Modern Excel users should also know about XLOOKUP, which is often easier to read for new formulas. INDEX/MATCH remains worth understanding because it appears in many established spreadsheets and gives you a clear way to control the lookup and return ranges.
What INDEX and MATCH do
MATCH finds the relative position of a value in a range. INDEX returns a value from a specified position in a range.
=INDEX(return_range, row_number)
When combined, MATCH finds the row and INDEX returns the value from that row.
The basic INDEX/MATCH formula
Read it from the inside out:
- MATCH searches for E2 in A2:A100.
- The 0 requests an exact match.
- MATCH returns the position of the matching row.
- INDEX uses that position to return the corresponding value from B2:B100.
Practical example: find a customer's phone number
| Customer | Phone |
|---|---|
| Aria | 555-0101 |
| Ben | 555-0102 |
| Chloe | 555-0103 |
If names are in A2:A4, phone numbers are in B2:B4, and E2 contains Chloe:
The formula returns 555-0103.
Why INDEX/MATCH can solve a VLOOKUP limitation
VLOOKUP traditionally searches the first column of its selected table and returns a value from a column to its right. INDEX/MATCH does not require the return range to be to the right of the lookup range.
For example, if employee IDs are in column C but employee names are in column A, you can search C and return A:
This is useful when the worksheet layout cannot easily be changed.
Use exact matching deliberately
For ordinary IDs, names, product codes and similar lookups, use 0 as the MATCH match_type when you want an exact match.
Leaving the match type out changes the behavior because MATCH's default is not the same as an explicit exact match. That is a frequent source of unexpected results in older formulas.
Handle a missing match
If MATCH cannot find the lookup value, the formula can return #N/A. You can use IFERROR to present a friendlier result after verifying that the underlying lookup is correct.
Do not use IFERROR as a substitute for checking the source data. Hidden spaces, different data types and misspellings can all cause a legitimate lookup to fail.
Two-way lookup with INDEX and MATCH
When the answer depends on both a row and a column, INDEX can use two MATCH results.
Here the first MATCH finds the row and the second MATCH finds the column. This pattern is useful for tables where a person, product or region selects the row while a month or metric selects the column.
INDEX/MATCH vs XLOOKUP
| Need | INDEX/MATCH | XLOOKUP |
|---|---|---|
| Existing legacy workbook | Very common | May not be available in older versions |
| Exact lookup | Use MATCH with 0 | Exact match is the modern default |
| Return to the left | Yes | Yes |
| Formula readability | More nested | Usually simpler |
For a new workbook that supports XLOOKUP, it is often the easier starting point. For an existing workbook, replacing a working INDEX/MATCH formula simply because a newer function exists may not be necessary.
Common INDEX/MATCH mistakes
- Missing exact-match argument: Use MATCH(...,0) when you need an exact match.
- Different range sizes: The lookup and return ranges should represent the same rows.
- Hidden spaces: TRIM or CLEAN may be needed when imported text does not match visually.
- Number vs text mismatch: 123 stored as text is not necessarily the same as numeric 123 for lookup purposes.
- Duplicate lookup values: MATCH returns the first exact match, so decide whether duplicates are acceptable.
- Overcomplicated error handling: Fix the data or lookup logic before hiding errors with IFERROR.
A quick troubleshooting method
- Run the MATCH portion by itself.
- Confirm that it returns the expected row position.
- Check that the INDEX return range starts on the corresponding row.
- Compare the lookup value and source value for spaces and data type differences.
- Only then add IFERROR or additional logic.
Related Excel guides
If your workbook supports it, compare this approach with Tervilo's XLOOKUP guide. For older lookup workbooks, see VLOOKUP in Excel. Browse the Excel Guides collection for more spreadsheet workflows.
Quick answer
The common pattern is =INDEX(return_range,MATCH(lookup_value,lookup_range,0)). MATCH finds the row position; INDEX returns the value from that position. Use exact matching deliberately and check for data-type or hidden-space problems when a lookup returns #N/A.