INDEX-MATCH combination in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to find the value in column B that matches the lookup value in cell E2 using MATCH.

Excel

=INDEX(B2:B10, [1](E2, A2:A10, 0))

Drag options to blanks, or click blank then click option'

ALOOKUP

BVLOOKUP

CHLOOKUP

DMATCH

Attempts:

3 left

💡 Hint

Common Mistakes

Using VLOOKUP instead of MATCH inside INDEX.

Forgetting the match_type argument in MATCH.

The MATCH function finds the position of the lookup value in the range A2:A10, which INDEX then uses to return the corresponding value from B2:B10.

2fill in blank

medium

Complete the formula to find the row number of the lookup value in A2:A20 using MATCH with exact match.

Excel

=MATCH([1], A2:A20, 0)

Drag options to blanks, or click blank then click option'

AC2

BB2

CA2

DD2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the lookup value.

Omitting the match_type argument.

The lookup value is in cell B2, so MATCH uses B2 to find its position in A2:A20.

3fill in blank

hard

Fix the error in the formula to correctly return the value from column C matching the lookup value in E3.

Excel

=INDEX(C2:C15, MATCH(E3, [1], 0))

Drag options to blanks, or click blank then click option'

AB2:B15

BD2:D15

CA2:A15

DC2:C15

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column range in MATCH.

Mixing up the lookup and return columns.

The MATCH function should search the lookup value E3 in the key column A2:A15, not in column C.

4fill in blank

hard

Fill both blanks to create a formula that returns the value from column D where the lookup value in F2 matches column A.

Excel

=INDEX([1], MATCH(F2, [2], 0))

Drag options to blanks, or click blank then click option'

AD2:D20

BA2:A20

CB2:B20

DC2:C20

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the ranges for INDEX and MATCH.

Using the wrong columns for lookup or return.

INDEX returns a value from D2:D20, and MATCH finds the position of F2 in A2:A20.

5fill in blank

hard

Fill all three blanks to create a formula that returns the value from column C where the lookup value in G3 matches column B, using exact match.

Excel

=INDEX([1], MATCH([2], [3], 0))

Drag options to blanks, or click blank then click option'

AC2:C25

BG3

CB2:B25

DA2:A25

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong ranges for INDEX or MATCH.

Forgetting the exact match argument in MATCH.

INDEX returns from C2:C25, MATCH looks for G3 in B2:B25 with exact match (0).

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