INDEX-MATCH combination in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the INDEX function do in Excel?

The INDEX function returns the value of a cell at a specific row and column in a given range or array.

beginner

What is the purpose of the MATCH function in Excel?

MATCH finds the position of a value in a row or column and returns its relative position number.

intermediate

Why combine INDEX and MATCH instead of using VLOOKUP?

INDEX-MATCH is more flexible because it can look left or right, works with large data, and is less error-prone when columns change.

beginner

Write a simple INDEX-MATCH formula to find the price of an item named "Apple" in a list where A2:A10 has items and B2:B10 has prices.

The formula is:
=INDEX(B2:B10, MATCH("Apple", A2:A10, 0))
It finds "Apple" in A2:A10 and returns the price from B2:B10 at the same row.

beginner

What does the '0' mean in the MATCH function inside INDEX-MATCH?

The '0' tells MATCH to look for an exact match of the lookup value.

What does the MATCH function return?

A The position of the lookup value in the range

B The value found in the lookup range

C The sum of values in the range

D The average of values in the range

MATCH returns the position number of the lookup value within the specified range.

Which function returns a value from a specific row and column in a range?

A MATCH

B INDEX

C SUM

D VLOOKUP

INDEX returns the value at a given row and column in a range or array.

Why is INDEX-MATCH preferred over VLOOKUP in many cases?

A Because it only works with numbers

B Because it automatically sorts data

C Because it is faster to type

D Because it can look up values to the left and is more flexible

INDEX-MATCH can look left or right and is more flexible when columns change.

In the formula =INDEX(B2:B10, MATCH("Apple", A2:A10, 0)), what does the 0 mean?

A Exact match

B Ignore errors

C Approximate match

D Match first letter only

The 0 tells MATCH to find an exact match of "Apple".

If MATCH cannot find the lookup value, what happens?

A It returns the last value in the range

B It returns 0

C It returns #N/A error

D It returns the first value in the range

MATCH returns #N/A error if the lookup value is not found.

Explain how the INDEX-MATCH combination works to find a value in a table.

Think about how MATCH and INDEX work together step-by-step.

Describe a situation where using INDEX-MATCH is better than VLOOKUP.

Consider limitations of VLOOKUP and how INDEX-MATCH overcomes them.

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