INDEX MATCH is a flexible lookup pattern that avoids two common VLOOKUP problems: it can return values from either side of the lookup column, and it is less fragile when columns are inserted or deleted. MATCH finds the row position, and INDEX returns the value at that position.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web when VLOOKUP cannot look left, breaks after table changes, or needs a more reliable replacement.
Build a basic INDEX MATCH formula
This replaces a normal exact-match lookup.
- Click the cell where the answer should appear.
- Type =INDEX(.
- Select the column containing the value to return, such as a Price column.
- Type MATCH( and choose the lookup value cell, such as the product name to search for.
- Select the column to search, add 0 for an exact match, and close both functions.
- Use a pattern like =INDEX(C:C,MATCH(F2,A:A,0)).
- Press Enter.
Excel finds the lookup value in the search column, then returns the value from the same row in the return column. The return column can be left or right of the lookup column.
Look up with two conditions
Use this when one condition is not specific enough.
- Use a formula pattern such as =INDEX(D:D,MATCH(1,(A:A=F2)*(B:B=G2),0)).
- Use F2 and G2 for the two criteria, such as product and region.
- Use A:A and B:B as the columns being tested.
- Use D:D as the column containing the value to return.
- In older Excel, confirm as an array formula with Ctrl+Shift+Enter. In Microsoft 365, press Enter normally.
Count matches with COUNTIF
Use COUNTIF when you only need to count matching cells rather than return a related value.
- Click an empty cell.
- Type =COUNTIF(.
- Select the range to search, such as A:A.
- Add the criterion, either as quoted text or a cell reference.
- Close the formula and press Enter.
For more than one condition, use COUNTIFS with pairs of criteria ranges and criteria.
More control
Build a two-way lookup
Use two MATCH functions inside INDEX when both the row and column should be found dynamically, such as matching a product down the side and a month across the top.
Approximate matches need sorted data
Changing MATCH from 0 to 1 finds the largest value less than or equal to the lookup value. That only works correctly when the lookup column is sorted ascending.
Fix #N/A
A #N/A result usually means MATCH did not find an exact value. Check spelling, extra spaces, and mismatched source values. Wrap the formula in IFERROR only after confirming the lookup is built correctly.
Sources
- Microsoft Support – INDEX function (2025)
- Microsoft Support – MATCH function (2025)
- Microsoft Support – COUNTIF function (2025)