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.