How to Use INDEX and MATCH in Excel, by Ashok Subedi on YouTube. 9 min 12 s.
- Video length
- 9 min 12 s
- Time to do it
- 10 minutes
- Difficulty
- Easy
- Steps
- 8
What you need
Steps
Put your lookup value in one column and the value you want back in another. With INDEX and MATCH the return column can sit to the left of the lookup column.
Type =MATCH(lookup_value, lookup_column, 0) in an empty cell. The lookup value is the item you are searching for, and the 0 tells Excel you want an exact match.
Press Enter and check the number MATCH returns. It is the position of your item inside the range, counting from the first cell, so confirm it points at the right row.
Now write =INDEX(return_column, MATCH(lookup_value, lookup_column, 0)). INDEX jumps to that row number in the return column and hands back the value it finds.
Lock the ranges with dollar signs, for example $B$2:$B$100, before you copy the formula down. Absolute references stop the ranges from sliding as you fill.
Wrap the formula in IFERROR so a missing item shows a friendly message instead of #N/A. Put your INDEX and MATCH formula inside IFERROR and add the fallback text you want.
For a two-way lookup, add a second MATCH for the column heading. Use =INDEX(data_range, MATCH(row_value, row_headers, 0), MATCH(column_value, column_headers, 0)) to pull the cell where both meet.
Copy the formula down the column and spot-check a few results against the source rows. If a result looks wrong, check that the lookup and return ranges start on the same row.
Tips and common mistakes
Always use 0 as the third argument in MATCH. Anything else gives an approximate match that can return the wrong row.
INDEX and MATCH keeps working when you insert or delete columns, while VLOOKUP often breaks.
Keep ranges tight instead of selecting whole columns, because big ranges slow large workbooks down.
Text must match exactly, including stray spaces, or MATCH returns #N/A.
Questions
Can INDEX and MATCH look to the left of the lookup column?
Yes. That is the main reason to use it instead of VLOOKUP, which can only return values from columns to the right of the lookup column.
Why do I get #N/A with my INDEX MATCH formula?
Usually the lookup value is not in the list, has a trailing space, or is stored as text in one place and as a number in the other.
Do I need XLOOKUP instead?
No. XLOOKUP is simpler in newer versions of Excel, but INDEX and MATCH works in every version and is still worth knowing.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
