I Use INDEX MATCH Every Day (Most Excel Users Never Learn This), by Excel Dictionary on YouTube. 2 min 38 s.
- Video length
- 2 min 38 s
- Time to do it
- 10 minutes
- Difficulty
- Easy
- Steps
- 7
What you need
Steps
Lay out your data with a column of row labels, a row of column headers, and the numbers in the middle. Add a cell where you will type the value you want to look up.
Start the INDEX part by typing =INDEX( and selecting the array you want a result from, such as the total sales column.
Build the MATCH part separately first: =MATCH(lookup_value, label_range, 0). The 0 tells Excel to find an exact match.
Combine them into =INDEX(sales_column, MATCH(lookup_value, name_column, 0)) and press Enter. Check the result against a value you already know.
For a two-way lookup, use the full data block and two MATCH functions: =INDEX(block, MATCH(row_label, row_labels, 0), MATCH(column_header, headers, 0)).
Add a dropdown with Data Validation for the column criterion, such as a quarter, so the formula recalculates when you pick a different option.
Press F4 on each range to make it absolute, then copy the formula down or across without the references shifting.
Tips and common mistakes
Always use 0 as the third MATCH argument, or you may get an approximate match and a wrong number.
The MATCH range must be a single row or column that lines up with the INDEX array, or the positions will not match.
Wrap the formula in IFERROR to show a blank or message instead of #N/A when a value is missing.
Lock ranges with F4 before copying, since relative references are the most common cause of broken lookups.
Questions
What is the difference between INDEX MATCH and VLOOKUP?
VLOOKUP can only search the leftmost column and return a column to its right. INDEX MATCH looks in any column and returns from any other column, so it works left or right.
Why does my INDEX MATCH return #N/A?
The lookup value is not found exactly in the range. Check for trailing spaces, numbers stored as text, or a range that does not cover all your rows.
Can INDEX MATCH look up two criteria at once?
Yes. Use two MATCH functions inside INDEX, one for the row position and one for the column position, which gives you a two-way lookup.
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.
