INDEX and MATCH combination in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This table lists names in column A and their scores in column B. Cell D2 contains a name to look up, and we want to find the corresponding score using INDEX and MATCH.

Cell	Value
A1	Name
B1	Score
A2	Alice
B2	85
A3	Bob
B3	92
A4	Charlie
B4	78
A5	Diana
B5	90
D1	Lookup Name
D2	Bob
E1	Score Found

Formula Trace

=INDEX(B2:B5, MATCH(D2, A2:A5, 0))

Step 1: MATCH(D2, A2:A5, 0)

Step 2: INDEX(B2:B5, 2)

Step 3: =INDEX(B2:B5, MATCH(D2, A2:A5, 0))

Cell Reference Map

    A       B       C       D       E
1 | Name  | Score |       | Lookup | Score
  |       |       |       | Name   | Found
2 | Alice | 85    |       | Bob    | 
3 | Bob   | 92    |       |        | 
4 | Charlie| 78   |       |        | 
5 | Diana | 90    |       |        | 

Arrows:
D2 --> MATCH lookup in A2:A5
MATCH result --> INDEX row number in B2:B5

The formula uses D2 as the lookup value to find its position in A2:A5 with MATCH. The position number is then used by INDEX to get the corresponding score from B2:B5.

Result

    A       B       C       D       E
1 | Name  | Score |       | Lookup | Score
  |       |       |       | Name   | Found
2 | Alice | 85    |       | Bob    | 92
3 | Bob   | 92    |       |        | 
4 | Charlie| 78   |       |        | 
5 | Diana | 90    |       |        | 

The formula in E2 shows 92, which is the score for 'Bob' found by the INDEX and MATCH combination.

Sheet Trace Quiz - 3 Questions

What does the MATCH function return in this formula?

A The position of 'Bob' in A2:A5

B The score of 'Bob'

C The value in D2

D The range B2:B5

Key Result

INDEX(range_to_return_value_from, MATCH(lookup_value, lookup_range, 0)) finds the position of lookup_value and returns corresponding value from another range.