INDEX/MATCH
Two functions used together — the classic professional alternative to VLOOKUP. More flexible, works in any direction, handles complex scenarios that VLOOKUP can't. Still the flexibility champion for advanced Excel work in 2026.
MATCH finds the position of a value in a list. INDEX returns the value at a specific position in a range. Combined, they perform any-direction lookups with more flexibility than VLOOKUP.
What INDEX/MATCH does
INDEX and MATCH are two separate functions that Excel pros combine to build flexible lookups. Neither one alone does what VLOOKUP does, but together they surpass VLOOKUP in every meaningful way — before XLOOKUP existed, INDEX/MATCH was how serious analysts avoided VLOOKUP's limitations.
The idea is simple: MATCH finds WHERE a value is (returning a position number), and INDEX returns WHAT is at a specific position. Composed together, they say "find this value's position, then return whatever is at that same position in this other range." Because the "find" and "return" ranges are independent, they can be in any direction — left, right, up, down — and can even be on different sheets.
💡 Why still learn INDEX/MATCH in the XLOOKUP era?
Three reasons: (1) You'll find INDEX/MATCH in millions of existing workbooks and need to read it. (2) Some scenarios — especially two-way matrix lookups and complex nested criteria — are more elegant in INDEX/MATCH than XLOOKUP. (3) INDEX/MATCH works in every Excel version, including Excel 2019 and earlier where XLOOKUP doesn't exist.
How INDEX works alone
Syntax: =INDEX(array, row_num, [column_num])
INDEX returns the value at a specific row and column position in a range. Give it a range plus row 3 and column 2, and it returns whatever is in the 3rd row, 2nd column of that range. Row and column numbers count from the top-left of the range, not from A1.
Example: =INDEX(A2:E100, 5, 3) returns the value in the 5th row, 3rd column of A2:E100 — which is cell C6 in absolute terms.
How MATCH works alone
Syntax: =MATCH(lookup_value, lookup_array, [match_type])
MATCH finds a value in a range and returns its position (1 for first row/column, 2 for second, etc.). It doesn't return the value itself — just the position number.
Example: =MATCH("Widget-42", A2:A100, 0) returns 7 if "Widget-42" is the 7th value in the range. The 0 means exact match — always use 0 unless you specifically want approximate match on sorted data.
Combining them — the classic pattern
The classic combination: MATCH finds the row position of your lookup value, then INDEX uses that position to return the corresponding value from a different column.
=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))
MATCH finds where the lookup_value appears in lookup_column. INDEX then returns whatever is at that same position in return_column. Both columns can be anywhere on the sheet.
=INDEX(matrix, MATCH(row_key, row_headers, 0), MATCH(col_key, col_headers, 0))
Use two MATCHes — one for row, one for column — to look up a value at the intersection of a row and column in a matrix table.
5 real-world examples
Find a product's price by ID
Products in A2:A100, prices in B2:B100. Product ID in E2.
Same result as =VLOOKUP(E2, A2:B100, 2, FALSE). INDEX/MATCH takes more characters but adapts if columns are inserted.
Return a value left of the lookup column
Customer IDs in column A, customer names in column B. Given a name in E2, find their ID.
Impossible with VLOOKUP (which can only return values to the right of the lookup column). INDEX/MATCH doesn't care about direction.
Find a value at the intersection of a row and column
You have a sales matrix with products in A2:A20 (rows) and months in B1:M1 (columns). Given a product in E2 and a month in F2, find the sales figure.
The killer INDEX/MATCH use case. Two-way lookups are natural here but require nested XLOOKUPs in the modern approach.
Match on two conditions at once
You have a data table with region in column A, product in column B, and revenue in column C. Find the revenue for the specific combination of region "West" and product "Widget".
The multiplied conditions create an array of 1s and 0s. MATCH finds the first 1 (both conditions true). In Excel 365, this works directly. In older versions, you need to enter with Ctrl+Shift+Enter as an array formula.
Return a fallback value when no match
Wrap in IFERROR so missing values return a friendly message instead of #N/A.
Standard defensive pattern. In XLOOKUP the fallback is built-in; in INDEX/MATCH you need to wrap manually.
Common errors and how to fix them
MATCH couldn't find the lookup value
Same top causes as VLOOKUP #N/A — trailing spaces, casing mismatches, numbers stored as text vs actual numbers.
INDEX row_num or column_num exceeds range size
You wrote =INDEX(A2:C10, 15) — but the range only has 9 rows. Row 15 doesn't exist.
MATCH match_type left blank on unsorted data
Default match_type is 1 (approximate) which requires ascending sort. On unsorted data, MATCH returns wrong positions or #N/A.
=MATCH(value, range, 0) — unless you specifically need bracket lookup.Two-criteria formula not entered as array in old Excel
The multi-criteria pattern =INDEX(C:C, MATCH(1, (A:A=x)*(B:B=y), 0)) requires Ctrl+Shift+Enter in Excel 2019 and earlier. In Excel 365 it works normally.
For more diagnosis, see our Lookup Errors guide.
When to use INDEX/MATCH vs VLOOKUP vs XLOOKUP
Version compatibility
Related functions
📥 Download the practice workbook
All 5 examples above in a working .xlsx file with the two-way matrix scenario ready to modify.
Frequently asked questions
Is INDEX/MATCH still worth learning in 2026?
Yes — for two reasons. First, it's in millions of existing workbooks you'll need to read. Second, some scenarios (two-way matrix lookups, complex multi-criteria) are more elegant in INDEX/MATCH than XLOOKUP. XLOOKUP is the default for new work, but INDEX/MATCH still has real advantages.
Is INDEX/MATCH actually faster than VLOOKUP?
On modern Excel with modern computers, the speed difference is negligible for most workbooks. The old "INDEX/MATCH is faster" advice was more valid on old hardware. What INDEX/MATCH still wins on is flexibility, not speed.
What's the difference between INDEX/MATCH and XMATCH?
XMATCH is MATCH with better defaults — exact match by default, includes search direction option. Some people use INDEX/XMATCH instead of INDEX/MATCH. Both work; XMATCH is cleaner if you're on Excel 365 or 2021+.
How do I do INDEX/MATCH with multiple criteria?
Multiply the conditions inside MATCH: =INDEX(return_col, MATCH(1, (col1=x)*(col2=y), 0)). In Excel 365 this works normally. In older versions, enter with Ctrl+Shift+Enter as an array formula.
Can INDEX return an entire row or column?
Yes — leave one of the position arguments blank or set to 0. =INDEX(A2:D100, 5, 0) returns the entire 5th row as an array. Useful for combining with SUM: =SUM(INDEX(A2:D100, 5, 0)).
Why do people say INDEX/MATCH is "more robust" than VLOOKUP?
Two big reasons: (1) inserting a column between the lookup column and return column doesn't break INDEX/MATCH (it breaks VLOOKUP's col_index_num); (2) INDEX/MATCH doesn't require the lookup column to be leftmost. Both matter in long-lived workbooks that get modified over time.
How do I make the MATCH range absolute so it doesn't drift when copying?
Use $ signs: =INDEX($B$2:$B$100, MATCH(E2, $A$2:$A$100, 0)). The $ before column letter and row number locks that reference so copying the formula down or across preserves the ranges.
Complex INDEX/MATCH generated instantly.
Describe your lookup — "get profit at intersection of region and quarter from the matrix" — and the Add-in writes the exact INDEX/MATCH combo.
Get the Add-in →