Lookup & reference
How to Use INDEX MATCH in Excel (Look Left and Right)
INDEX/MATCH is the lookup combo power users prefer over VLOOKUP. MATCH finds the position of a value, and INDEX returns whatever sits at that position in another column.
Unlike VLOOKUP, it can look to the left, does not break when you insert columns, and is built from two simple functions. This guide shows the core pattern and a two-way lookup.
INDEX/MATCH syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) | Argument | Required | What it does |
|---|---|---|
return_range | Required | The column (or row) you want a value FROM. |
lookup_value | Required | The value you are searching for. |
lookup_range | Required | The column to search in. It must be the same height as return_range. |
match_type | Optional | Use 0 for an exact match. 1 or -1 do approximate matches on sorted data. |
Note: INDEX/MATCH can return a column to the left of the lookup column, the main thing VLOOKUP cannot do. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Standard lookup
=INDEX(C2:C100, MATCH(E2, A2:A100, 0)) MATCH finds the row of E2 inside A2:A100, then INDEX returns the value at that row in C2:C100. The 0 forces an exact match.
2. Look to the LEFT (impossible with VLOOKUP)
=INDEX(A2:A100, MATCH(E2, C2:C100, 0)) Because the return range and lookup range are independent, INDEX/MATCH happily returns column A even though we searched column C.
3. Two-way lookup (row and column)
=INDEX(B2:M100, MATCH(E2, A2:A100, 0), MATCH(F1, B1:M1, 0)) A second MATCH supplies the column number, so INDEX pulls the cell at the intersection of the matched row and matched column.
How to write INDEX/MATCH step by step
- 1
Click the result cell and type =INDEX(
- 2
Select the range you want a value FROM (the return column), then a comma.
- 3
Type MATCH( then click the value you are searching for, and a comma.
- 4
Select the column to search in, type a comma, then 0 and a closing bracket ) for MATCH.
- 5
Close the INDEX bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | MATCH did not find the lookup value, often extra spaces or a number stored as text. | Use exact match (0), clean values with TRIM(), and confirm both sides are the same data type. Wrap in IFERROR for a friendly fallback. |
#REF! | MATCH returns a position outside the return_range, usually because the two ranges are different heights. | Make return_range and lookup_range start on the same row and have the same number of rows. |
Wrong value returned | The return range and lookup range begin on different rows, so positions are offset. | Align both ranges to the same start row, e.g. both from row 2. |
#VALUE! | match_type is invalid or an argument is malformed. | Use 0 for an exact match and check the brackets and commas are balanced. |
Frequently asked questions
Why use INDEX MATCH instead of VLOOKUP?
INDEX/MATCH can look left as well as right, does not break when you insert or delete columns, and can be faster on large sheets because it scans only the two columns it needs.
Does INDEX MATCH work in Google Sheets?
Yes. INDEX and MATCH work identically in Google Sheets, including the two-way lookup with a second MATCH.
Is INDEX MATCH faster than VLOOKUP?
On large workbooks it can be, because it only references the lookup column and the return column rather than the whole table. For small ranges the difference is negligible.
Should I use INDEX MATCH or XLOOKUP?
If you have Microsoft 365 or Excel 2021+, XLOOKUP is simpler and does the same job. INDEX/MATCH is the best choice in older Excel versions that lack XLOOKUP.