INDEX and MATCH work as a pair: MATCH finds Alice's position in the name column, then INDEX uses that position to return her salary. The walkthrough below calculates both steps live.
Find the position of a value in a range or array with Excel's MATCH function.
"Alice"The value to search for. Can be a number, text, logical value, or a cell reference.A2:A8The single row or column of cells to search in.0ExactHow to match: 0 for an exact match, 1 (default) for the largest value less than or equal to lookup_value, or -1 for the smallest value greater than or equal to it.Return the value at a given row and column position in a range with Excel's INDEX function.
C2:C8MATCH("Alice",A2:A8,0)The row position within the array to return. Counts from the first row of the array. Set it to 0 to return the entire column.MATCH searches A2:A8 and returns position 4. INDEX takes position 4 from C2:C8, selecting Alice's salary in C5.
The rows above are ordered from the innermost calculation to the final result. Excel has to finish an inner function before the function around it can use that value. Edit a cell in the grid to see that dependency chain update for real.
The sample spreadsheet is fully editable. Change the formula or any cell it references. The intermediate values recalculate in your browser.
Here is an exercise that uses INDEX. Type the formula into a real spreadsheet and we will check your answer as you go.
Find a customer's email address where email is to the LEFT of customer ID.
Open exerciseIf this formula is not doing what you expect, one of these is usually why.
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.
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.
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.
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.