Excel INDEX/MATCH
excel-indexmatch• Watch MATCH find the position and INDEX return the value, step by step. • Unlike VLOOKUP, it can return a column to the left of the search column.
💡 Change the product code and the name updates automatically. Unlike VLOOKUP, it can return a column to the left of the search column.
How to read INDEX/MATCH
MATCH finds the position (N) of a value; INDEX returns the value at that position. Unlike VLOOKUP, it can also return a column to the left.
Wrapped in IFERROR, a missing code shows “Not found” instead of #N/A.
Why use INDEX/MATCH instead of VLOOKUP
INDEX/MATCH gives the same answer as VLOOKUP but works in more situations. MATCH finds the position of a value — which row it is in — and INDEX returns the value at that position. Because it is two steps, you choose the search column and the return column independently.
That means the value you want back can sit to the left of the one you search on. VLOOKUP simply cannot do this, so people end up reordering columns or adding a helper column. With INDEX/MATCH none of that is needed.
The difference grows the longer a sheet lives. VLOOKUP refers to the return column by number, so inserting a column shifts it and it starts returning the wrong data. INDEX/MATCH points at the column itself and keeps working. It looks longer at first, but it needs less maintenance later.
Frequently asked questions
I keep mixing up the order of INDEX and MATCH.
Memorise one shape: =INDEX(column to return, MATCH(value to find, column to search, 0)). The inner MATCH works out the position first, and INDEX takes that number and pulls the value. Watching the M step and the I step run separately in the demo above makes the order stick.
What does the 0 at the end of MATCH do?
It sets the match type. 0 finds only an exact match, while 1 and -1 look for approximate matches in a sorted table. Omit it and Excel assumes 1, which returns a nonsense position on an unsorted table, so write the 0 unless you have a specific reason not to.
My INDEX/MATCH returns #N/A.
MATCH did not find the value. Typically one side carries a stray space, or numbers and text are mixed between the lookup value and the search column. If a miss is a normal outcome in your sheet, wrap it as =IFERROR(INDEX(...),"not found") to show your own message.
Do the INDEX range and the MATCH range have to line up?
They need the same number of rows and the same starting row. MATCH returns a position within its range, so if MATCH starts at row 2 while INDEX starts at row 1, every answer is off by one. Nothing errors out, which is exactly what makes this one worth checking.
Can I look up by row and column at the same time?
Use MATCH twice: =INDEX(table, MATCH(row criteria, row headers, 0), MATCH(column criteria, column headers, 0)) returns the cell where the row and the column meet. Work that would mean editing the column index by hand in VLOOKUP fits into a single formula.
Should I use INDEX/MATCH or XLOOKUP?
XLOOKUP is shorter and lets you pass the not-found value as an argument, so it is simpler where it is available. It only exists in fairly recent versions though, so if you exchange files with older Excel, INDEX/MATCH is the one that opens everywhere.