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.

Practice

1. What is the main advantage of using INDEX-MATCH over VLOOKUP in Excel?

easy

A. It automatically sorts the data for faster lookup.

B. It only works with numeric data.

C. It can look up values in any column, not just to the right.

D. It requires less typing than VLOOKUP.

Solution

1. Step 1: Understand VLOOKUP limitation

   VLOOKUP can only search for a value in the first column and return a value to the right.

2. Step 2: Understand INDEX-MATCH flexibility

   INDEX-MATCH can look up values anywhere in the table, left or right, because MATCH finds the position and INDEX returns the value.

3. Final Answer:

   It can look up values in any column, not just to the right. -> Option C

4. Quick Check:

   INDEX-MATCH is more flexible than VLOOKUP [OK]

Hint: INDEX-MATCH can look left or right, VLOOKUP only right [OK]

Common Mistakes:

• Thinking VLOOKUP can look left
• Believing INDEX-MATCH sorts data automatically
• Assuming INDEX-MATCH only works with numbers

2. Which of the following is the correct syntax for using INDEX and MATCH together to find a value in Excel?

easy

A. =MATCH(INDEX(A2:A10), "Apple", B2:B10)

B. =INDEX(A2:A10, MATCH("Apple", B2:B10, 0))

C. =INDEX(MATCH("Apple", A2:A10, 0), B2:B10)

D. =MATCH(A2:A10, INDEX("Apple", B2:B10, 0))

Solution

1. Step 1: Understand MATCH syntax

   MATCH("Apple", B2:B10, 0) finds the position of "Apple" in B2:B10 with exact match (0).

2. Step 2: Understand INDEX syntax

   INDEX(A2:A10, position) returns the value in A2:A10 at the position found by MATCH.

3. Final Answer:

   =INDEX(A2:A10, MATCH("Apple", B2:B10, 0)) -> Option B

4. Quick Check:

   INDEX uses MATCH position correctly [OK]

Hint: MATCH finds position, INDEX returns value at that position [OK]

Common Mistakes:

• Swapping INDEX and MATCH arguments
• Using MATCH as first argument of INDEX
• Incorrect order of parameters

3. Given the table:

  A       B
1 Fruit   Price
2 Apple   3
3 Banana  2
4 Cherry  5

What is the result of the formula =INDEX(B2:B4, MATCH("Banana", A2:A4, 0))?

medium

A. 2

B. 3

C. 5

D. #N/A

Solution

1. Step 1: Use MATCH to find position of "Banana"

   MATCH("Banana", A2:A4, 0) searches A2:A4 and finds "Banana" at position 2.

2. Step 2: Use INDEX to get price at position 2

   INDEX(B2:B4, 2) returns the value in B3, which is 2.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   Banana price = 2 [OK]

Hint: MATCH finds row, INDEX returns value from that row [OK]

Common Mistakes:

• Confusing row numbers with positions
• Using wrong ranges for INDEX or MATCH
• Forgetting exact match (0) in MATCH

4. You wrote the formula =INDEX(B2:B5, MATCH("Orange", A2:A5, 0)) but it returns #N/A. What is the most likely reason?

medium

A. You forgot to use absolute references ($).

B. The ranges B2:B5 and A2:A5 are not the same size.

C. INDEX cannot be used with MATCH.

D. "Orange" is not found in the range A2:A5.

Solution

1. Step 1: Understand MATCH error

   MATCH("Orange", A2:A5, 0) returns #N/A if "Orange" is not found in A2:A5.

2. Step 2: Check formula behavior

   Since MATCH returns #N/A, INDEX cannot return a value and the whole formula returns #N/A.

3. Final Answer:

   "Orange" is not found in the range A2:A5. -> Option D

4. Quick Check:

   Missing lookup value causes #N/A [OK]

Hint: Check if lookup value exists before using MATCH [OK]

Common Mistakes:

• Assuming #N/A is due to range size mismatch
• Thinking INDEX can't work with MATCH
• Ignoring need for exact match in MATCH

5. You have a table with columns Product (A2:A6), Category (B2:B6), and Price (C2:C6). How would you use INDEX-MATCH to find the price of the first product in the "Fruit" category?

hard

A. =INDEX(C2:C6, MATCH("Fruit", B2:B6, 0))

B. =INDEX(B2:B6, MATCH("Fruit", A2:A6, 0))

C. =MATCH("Fruit", C2:C6, 0)

D. =INDEX(C2:C6, MATCH("Fruit", C2:C6, 0))

Solution

1. Step 1: Use MATCH to find position of "Fruit" in Category

   MATCH("Fruit", B2:B6, 0) finds the first row where Category is "Fruit".

2. Step 2: Use INDEX to get Price at that position

   INDEX(C2:C6, position) returns the price of the product in that row.

3. Final Answer:

   =INDEX(C2:C6, MATCH("Fruit", B2:B6, 0)) -> Option A

4. Quick Check:

   Find category position, then get price [OK]

Hint: MATCH category, INDEX price to get correct value [OK]

Common Mistakes:

• Matching category in wrong column
• Using MATCH on price column instead of category
• Swapping INDEX and MATCH ranges