How this INDEX formula works
MATCH searches A2:A8 and returns position 4. INDEX takes position 4 from C2:C8, selecting Alice's salary in C5.
The top row above is the whole formula. Open any argument that holds a calculation of
its own to see the values it used. Excel works out those inner parts first, because the
function around them needs their results. Edit a cell in the grid to see every value
update.
Try your own formula and data
The sample spreadsheet is fully editable. Change the formula or any cell it references.
The intermediate values recalculate in your browser.
Common mistakes with INDEX
If this formula is not doing what you expect, one of these is usually why.
Why is my INDEX MATCH returning #N/A?
MATCH could not find the lookup value, so INDEX never receives a position. Test MATCH on its own, then check for a missing name, trailing spaces, or a text-versus-number mismatch.
Why does INDEX MATCH return the wrong row?
The two ranges do not line up. MATCH counts positions inside its own range, so searching A2:A8 while returning from C1:C8 shifts every answer by one. Start both ranges on the same row and give them the same height.
Why does MATCH find something that is not there?
The third argument was left out, which makes MATCH assume the column is sorted and return the position of the closest smaller value. Add 0 as the third argument to ask for an exact match.
Why use INDEX MATCH instead of VLOOKUP?
INDEX MATCH can return a value from any column, including one to the left of the search column, and it keeps working when someone inserts a column in the middle of the table.