How to Use INDEX MATCH in Excel
INDEX MATCH is Excel's flexible lookup: MATCH finds the row, INDEX returns the value. Learn the formula, the match_type rules, and error handling.
INDEX MATCH is Excel’s flexible lookup: MATCH finds the row number of your search value, and INDEX pulls the answer from that row in whatever column you point it at. The core formula is =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). Unlike VLOOKUP, it looks in either direction and survives inserted or moved columns.
What each function does
INDEX(array, row_num, [column_num]) returns the value at a position in a range. MATCH(lookup_value, lookup_array, [match_type]) returns the position of a value. You nest MATCH inside INDEX so the position it finds becomes the row INDEX reads.
The match_type argument controls how MATCH searches:
| match_type | Behavior | Data must be |
|---|---|---|
| 0 | Exact match | Any order |
| 1 or omitted | Largest value ≤ lookup | Ascending |
| -1 | Smallest value ≥ lookup | Descending |
Use 0 for almost everything. The 1 and -1 modes suit range lookups like tax or grade brackets, and they need sorted data to be reliable.
A worked example
Say names sit in column A and salaries in column D. To find Maria’s salary:
=INDEX(D2:D100, MATCH("Maria", A2:A100, 0))
MATCH locates “Maria” in A2:A100 and returns her row position; INDEX reads that same position from D2:D100. Point the lookup at a cell instead of typed text so the formula stays reusable, and pair that cell with a drop-down list so users pick only valid entries.
Why choose it over VLOOKUP
VLOOKUP searches left to right and breaks when you insert a column, because its column number is hard-coded. INDEX MATCH references the return column directly, so rearranging columns will not shift the result. It also reads columns that sit to the left of the lookup value, which VLOOKUP cannot do. That helps when you compare two Excel columns across sheets.
Handle errors and two-way lookups
A failed match returns #N/A. Wrap it for a cleaner result: =IFERROR(INDEX(D2:D100, MATCH("Maria", A2:A100, 0)), "Not found"). For a grid, feed MATCH into both INDEX arguments: =INDEX(table, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0)).
On Microsoft 365 or Office 2021, XLOOKUP does the same job in one function and handles missing values on its own. INDEX MATCH still wins when a formula must run in every Excel version, so it stays the most portable lookup you can build.