INDEX-MATCH combination in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INDEX-MATCH formula?

Given the table below:

  A       B
1 Name    Score
2 Alice   85
3 Bob     90
4 Carol   78
5 Dave    92

What is the result of the formula =INDEX(B2:B5, MATCH("Carol", A2:A5, 0))?

A 85

B 90

C 78

D 92

💡 Hint

Think about how MATCH finds the position of "Carol" in the list, then INDEX returns the value at that position.

The MATCH function finds "Carol" at the 3rd position in A2:A5. INDEX then returns the 3rd value in B2:B5, which is 78.

🧠 Conceptual

intermediate

Why use INDEX-MATCH instead of VLOOKUP?

Which of the following is a key advantage of using INDEX-MATCH over VLOOKUP in Excel?

A INDEX-MATCH is simpler to write than VLOOKUP

B INDEX-MATCH can look up values to the left of the lookup column

C VLOOKUP is faster than INDEX-MATCH

D VLOOKUP can only search numbers, INDEX-MATCH can search text

💡 Hint

Think about the direction of lookup in VLOOKUP compared to INDEX-MATCH.

VLOOKUP can only search in the leftmost column and return values to the right. INDEX-MATCH can look up values anywhere, including to the left.

🔧 Formula Fix

advanced

Which formula correctly uses INDEX-MATCH to find a value?

Choose the formula that correctly returns the score for "Bob" from columns A (names) and B (scores):

A =INDEX(B2:B5, MATCH("Bob", A2:A5, 0))

B =MATCH("Bob", A2:A5, 0, INDEX(B2:B5))

C =INDEX(B2:B5, MATCH(0, "Bob", A2:A5))

D =INDEX(A2:A5, MATCH(B2:B5, "Bob", 0))

💡 Hint

Remember the order of arguments for MATCH and INDEX.

Option A correctly uses MATCH to find "Bob" in A2:A5 and INDEX to return the corresponding value from B2:B5. Other options have incorrect argument orders or syntax.

❓ optimization

advanced

How to optimize INDEX-MATCH for large datasets?

You have a large dataset with 100,000 rows. Which approach optimizes performance when using INDEX-MATCH?

A Use exact match (0) in MATCH and limit ranges to only needed cells

B Use approximate match (1) in MATCH and full column references

C Use volatile functions like INDIRECT with INDEX-MATCH

D Use entire columns in INDEX and MATCH ranges with exact match

💡 Hint

Think about how Excel processes ranges and match types.

Using exact match with limited ranges reduces calculation time. Approximate match requires sorted data and full columns slow performance. Volatile functions recalc often and slow down.

🔧 Formula Fix

expert

Why does this INDEX-MATCH formula return #N/A?

Given the data:

  A       B
1 Name    Score
2 Alice   85
3 Bob     90
4 Carol   78
5 Dave    92

Why does the formula =INDEX(B2:B5, MATCH("Eve", A2:A5, 0)) return #N/A?

A Because INDEX cannot handle text lookup values

B Because the ranges B2:B5 and A2:A5 are different sizes

C Because MATCH requires approximate match for text values

D Because "Eve" is not found in the lookup range A2:A5

💡 Hint

Consider what happens when MATCH does not find the lookup value.

MATCH returns #N/A when the lookup value is not found. Since "Eve" is not in A2:A5, MATCH returns #N/A, causing INDEX to also return #N/A.

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