INDEX-MATCH combination in Excel - Cell-by-Cell Formula Trace

Sample Data

This table lists products and their prices. We want to find the price of a product typed in D2 using INDEX-MATCH.

Cell	Value
A1	Product
B1	Price
A2	Apple
B2	1.2
A3	Banana
B3	0.5
A4	Cherry
B4	2.0
D1	Lookup Product
D2	Banana
E1	Price Found

Formula Trace

=INDEX(B2:B4, MATCH(D2, A2:A4, 0))

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

Step 2: INDEX(B2:B4, 2)

Step 3: =INDEX(B2:B4, MATCH(D2, A2:A4, 0))

Cell Reference Map

    A       B       C       D       E
1 |Product | Price |       |Lookup  |Price  
  |        |       |       |Product |Found  
2 |Apple   | 1.2   |       |Banana  |       
3 |Banana  | 0.5   |       |        |       
4 |Cherry  | 2.0   |       |        |       

Arrows:
D2 --> MATCH lookup in A2:A4
MATCH result (2) --> INDEX row number in B2:B4
INDEX returns value to E2

The formula uses D2 as lookup value, searches in A2:A4 with MATCH, then uses the position to get the price from B2:B4 with INDEX.

Result

    A       B       C       D       E
1 |Product | Price |       |Lookup  |Price  
  |        |       |       |Product |Found  
2 |Apple   | 1.2   |       |Banana  | 0.5   
3 |Banana  | 0.5   |       |        |       
4 |Cherry  | 2.0   |       |        |       

The formula in E2 shows 0.5, which is the price of 'Banana' found by the INDEX-MATCH combination.

Sheet Trace Quiz - 3 Questions

What does MATCH(D2, A2:A4, 0) return if D2 contains 'Banana'?

A 1

B 2

C 3

D 0

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.