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.