How to look up data in Excel using Index Match Match! #excel #spreadsheets #xlookup, by Excelbychris on YouTube. 1 min 10 s.
- Video length
- 1 min 10 s
- Time to do it
- 5 minutes
- Difficulty
- Easy
- Steps
- 7
What you need
Steps
Lay out your data as one clean block with row labels down the left column and column labels across the top row. Remove blank rows and columns so the ranges stay tidy.
Type your two lookup values into separate cells, for example a product name and a month. These are the row and column you want to find.
Get the row position with =MATCH(product, A2:A20, 0). The 0 forces an exact match, and the answer is where that label sits in the list.
Get the column position with =MATCH(month, B1:M1, 0). Point this at a single row of headers only, never a whole block.
Combine them as =INDEX(B2:M20, MATCH(product,A2:A20,0), MATCH(month,B1:M1,0)). The data range must leave out the header row and header column.
Press Enter and check the result against the table by eye. If it looks wrong, your ranges are probably offset by one row or column.
Wrap the whole formula in IFERROR so a missing label shows a blank or a short message instead of #N/A.
Tips and common mistakes
Always use 0 as the third MATCH argument; leaving it out gives an approximate match and the wrong row.
Press F4 to add $ signs and lock each range before you copy the formula down or across.
Keep the header range the same length as the data range, or the positions will not line up.
Name your data range with the Name Box to make the final formula much easier to read.
Questions
Why does INDEX MATCH MATCH return #N/A?
One of the labels you typed does not exactly match a header. Check for trailing spaces or numbers stored as text.
How is this different from VLOOKUP?
VLOOKUP only searches the left column and needs a column number. INDEX MATCH MATCH looks up by row and column label, so it works in any direction and survives inserted columns.
Does it work in Google Sheets?
Yes, INDEX and MATCH use the same syntax in Google Sheets, so the formula copies over unchanged.
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.
