INDEX MATCH is the lookup combination that Excel professionals reach for when VLOOKUP cannot do the job. It can look left, right, up, or down. It does not break when you insert columns. It handles two-dimensional lookups. Once you understand how the two functions work together, you will use this combination constantly.
What Does INDEX Do?
INDEX returns the value of a cell at a specific row and column position within a range. Syntax: =INDEX(array, rownum, [colnum])- array — the range to pull from
- row_num — which row in that range
- col_num — which column (optional if array is a single column)
The power is not in INDEX alone — it is in replacing that hardcoded 3 with something dynamic.
What Does MATCH Do?
MATCH searches for a value in a range and returns its position number. Syntax: =MATCH(lookupvalue, lookuparray, match_type)- lookup_value — what you are searching for
- lookup_array — a single row or column to search in
- match_type — use 0 for exact match (almost always what you want)
Combining INDEX and MATCH
The magic happens when you nest MATCH inside INDEX to replace the hardcoded row number:
=INDEX(returnrange, MATCH(lookupvalue, lookup_array, 0))
Suppose you have a product table:
| A (Product) | B (Price) |
| Apple | $1.20 |
| Banana | $0.50 |
| Cherry | $3.00 |
=INDEX(B1:B3, MATCH("Banana", A1:A3, 0))
- MATCH("Banana", A1:A3, 0) scans A1:A3 and returns 2 (Banana is in row 2).
- INDEX(B1:B3, 2) returns the second value in B1:B3, which is $0.50.
Why INDEX MATCH Beats VLOOKUP
1. Left Lookup
VLOOKUP can only look to the right. The lookup column must be the leftmost column in the table. INDEX MATCH has no such restriction — the return column can be to the left, right, or anywhere.
Left lookup example:| A (Price) | B (Product) |
| $1.20 | Apple |
| $0.50 | Banana |
| $3.00 | Cherry |
=INDEX(B1:B3, MATCH(0.5, A1:A3, 0))
VLOOKUP cannot do this. INDEX MATCH handles it without rearranging the table.
2. Dynamic Column Selection
With VLOOKUP, you hardcode the column number: =VLOOKUP(value, table, 3, 0). If someone inserts a column in the table, column 3 shifts and the formula pulls wrong data silently.
With INDEX MATCH, you reference the return column by its actual range address. Inserting columns does not affect it — the range reference updates automatically.
3. No Table Size Limit Impact
VLOOKUP scans the entire table array from left to right. INDEX MATCH only scans the lookup column, then retrieves a single cell. On very large datasets this can be meaningfully faster.
4. Easier to Audit
In =INDEX(B1:B3, MATCH("Banana", A1:A3, 0)), you can see exactly what range is being searched (A1:A3) and exactly what range the result comes from (B1:B3). In =VLOOKUP("Banana", A1:C10, 2, 0), the 2 is an arbitrary number — you have to count columns to understand it.
Right-to-Left Lookup (Classic Left Lookup)
Full example with a real data layout. You have a table where Employee ID is in column C and Employee Name is in column A:
| A (Name) | B (Department) | C (ID) |
| Sarah | Sales | 1001 |
| James | IT | 1002 |
| Maria | HR | 1003 |
=INDEX(A2:A4, MATCH(1002, C2:C4, 0))
Returns "James". VLOOKUP cannot do this without restructuring the table.
Two-Criteria Match
To look up based on two conditions, use an array formula. Wrap both MATCH conditions using multiplication (which acts as AND):
=INDEX(C2:C10, MATCH(1, (A2:A10="Sales")*(B2:B10="Manager"), 0))
This formula finds the first row where column A is "Sales" AND column B is "Manager", then returns the corresponding value from column C.
Alternative using MATCH on concatenated values:=INDEX(C2:C10, MATCH(Alookup&Blookup, A2:A10&B2:B10, 0))
Enter with Ctrl+Shift+Enter in older Excel. This concatenates both criteria and searches a concatenated lookup array.
INDEX MATCH MATCH: Two-Dimensional Lookup
INDEX can take both a row and column number, letting you look up both dimensions dynamically.
Syntax:=INDEX(table, MATCH(rowvalue, rowheaders, 0), MATCH(colvalue, colheaders, 0))
| Q1 | Q2 | Q3 | Q4 | |
| North | 100 | 120 | 90 | 110 |
| South | 80 | 95 | 105 | 88 |
| East | 130 | 115 | 125 | 140 |
To look up the Q3 value for South:
=INDEX(B2:E4, MATCH("South", A2:A4, 0), MATCH("Q3", B1:E1, 0))
- MATCH("South", A2:A4, 0) returns 2
- MATCH("Q3", B1:E1, 0) returns 3
- INDEX(B2:E4, 2, 3) returns the value at row 2, column 3 of the table — 105
INDEX MATCH vs. XLOOKUP
Excel 365 introduced XLOOKUP, which handles most INDEX MATCH use cases with simpler syntax:
=XLOOKUP("Banana", A1:A3, B1:B3)
XLOOKUP can also look left, handle arrays, and use approximate or exact matching. For two-dimensional lookups, you still need INDEX MATCH MATCH or a nested XLOOKUP. XLOOKUP is not available in Excel 2019 or earlier.
VLOOKUP vs. INDEX MATCH: Comparison Table
| Feature | VLOOKUP | INDEX MATCH |
| Look left | No | Yes |
| Look right | Yes | Yes |
| Breaks on column insert | Yes | No |
| Two-dimensional lookup | No | Yes (MATCH MATCH) |
| Easier syntax | Yes | Slightly more complex |
| Available in all Excel versions | Yes | Yes |
| Speed on large datasets | Slower | Faster |
| Available in Excel 365 alternative | XLOOKUP | XLOOKUP |
Tips and Best Practices
Lock your ranges. Use absolute references in INDEX MATCH formulas so you can copy them down a column: =INDEX($B$1:$B$100, MATCH(D2, $A$1:$A$100, 0)). Always use 0 for exact match. The third argument of MATCH controls match type. 0 means exact, 1 means less than or equal (requires sorted data), -1 means greater than or equal. For most lookups, 0 is correct. Handle errors with IFERROR. If the lookup value is not found, MATCH returns a #N/A error. Wrap the formula: =IFERROR(INDEX($B$1:$B$100, MATCH(D2, $A$1:$A$100, 0)), "Not found"). Named ranges make INDEX MATCH readable. If you name your lookup column "Products" and your return column "Prices", the formula =INDEX(Prices, MATCH(D2, Products, 0)) is self-documenting.Frequently Asked Questions
When should I use INDEX MATCH instead of VLOOKUP?Use INDEX MATCH whenever your return column is to the left of your lookup column, whenever columns might be inserted into your table later, whenever you need a two-dimensional lookup, or whenever you are working with large datasets where performance matters. For simple right-looking lookups in a stable table, VLOOKUP works fine.
What does the 0 mean in MATCH?The third argument of MATCH is the match type. 0 specifies exact match — MATCH only returns a position if it finds the exact lookup value. 1 finds the largest value less than or equal to the lookup value (requires ascending sort). -1 finds the smallest value greater than or equal (requires descending sort). Always use 0 unless you specifically need approximate matching on sorted data.
Why does my INDEX MATCH return the wrong value?The most common cause is a range mismatch: the lookup array and the return array are different sizes or start at different rows. Make sure =INDEX(B2:B100, MATCH(..., A2:A100, 0)) has both ranges starting at row 2 and ending at row 100. If they are offset by even one row, every result will be wrong.
Can INDEX MATCH handle wildcards?Yes. Use or ? wildcards in the lookup value with MATCH. For example, =MATCH("Ban", A1:A10, 0) finds the first cell starting with "Ban". Note that wildcard matching only works with match type 0.
How is INDEX MATCH different from XLOOKUP?XLOOKUP (Excel 365/2021 only) achieves what INDEX MATCH does with simpler syntax for most cases. XLOOKUP handles left lookups, approximate matches, and multiple return columns natively. INDEX MATCH MATCH still has an advantage for true two-dimensional grid lookups where both row and column are dynamic. INDEX MATCH also works in all Excel versions back to Excel 2003.
Why am I getting a #VALUE! error?A #VALUE! error in INDEX MATCH usually means the MATCH lookup array is not a single row or column — it must be one-dimensional. Check that your lookup array (the second argument of MATCH) is either a single column reference like A1:A100 or a single row reference like A1:Z1, not a multi-column table.