MATCH function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find the position of a value in a list

Given the list {10, 20, 30, 40, 50} in cells A1:A5, what is the result of =MATCH(30, A1:A5, 0)?

A 3

B 2

C 4

D 0

💡 Hint

MATCH with 0 finds the exact position of the value in the range.

The value 30 is the third item in the list A1:A5, so MATCH returns 3.

📊 Formula Result

intermediate

Using MATCH with approximate match

With the sorted list {5, 10, 15, 20, 25} in cells B1:B5, what does =MATCH(18, B1:B5, 1) return?

A 5

B 4

C 2

D 3

💡 Hint

MATCH with 1 finds the largest value less than or equal to the lookup value.

18 is not in the list, but 15 is the largest value less than 18 at position 3.

❓ Function Choice

advanced

Choose the correct MATCH formula for last exact occurrence

You have the list {2, 4, 2, 6, 2} in cells C1:C5. Which formula returns the position of the last exact match of 2?

A =MATCH(2, C1:C5, 0)

B =MAX(IF(C1:C5=2, ROW(C1:C5)-ROW(C1)+1))

C =MATCH(2, C5:C1, 0)

D =MATCH(2, C1:C5, 1)

💡 Hint

MATCH finds the first match; to find the last, use an array formula with MAX and IF.

Option B uses an array formula to find all positions of 2 and returns the maximum position, which is the last occurrence.

🎯 Scenario

advanced

MATCH function with unsorted data and approximate match

Given the unsorted list {30, 10, 20, 50, 40} in cells D1:D5, what will =MATCH(25, D1:D5, 1) return?

A #N/A error

B 1

C 3

D 2

💡 Hint

Approximate match requires the list to be sorted ascending.

The list is not sorted ascending, so MATCH with 1 returns an unreliable result. In this case, it returns 3 (position of 20).

❓ data_analysis

expert

Count how many values are less than a threshold using MATCH

You have a sorted list {3, 7, 8, 12, 15, 20} in cells E1:E6. Which formula correctly counts how many values are less than 13?

A =MATCH(13, E1:E6, 1)

B =MATCH(13, E1:E6, 0)

C =MATCH(13, E1:E6, -1)

D =MATCH(12, E1:E6, 1)

💡 Hint

MATCH with 1 returns the position of the largest value less than or equal to the lookup value.

MATCH(13, E1:E6, 1) returns 4 because 12 is the largest value less than 13 at position 4, so 4 values are less than 13.

Practice

1. What does the MATCH function do in Excel?

easy

A. Adds numbers in a range

B. Sorts data alphabetically

C. Finds the position of a value in a list or range

D. Deletes duplicate values

Solution

1. Step 1: Understand the purpose of MATCH

   The MATCH function searches for a value in a range and returns its position number.

2. Step 2: Compare with other options

   Options A, C, and D describe different functions (SUM, SORT, Remove Duplicates), not MATCH.

3. Final Answer:

   Finds the position of a value in a list or range -> Option C

4. Quick Check:

   MATCH locates position [OK]

Hint: MATCH returns position, not the value itself [OK]

Common Mistakes:

• Confusing MATCH with SUM or LOOKUP
• Thinking MATCH returns the value found
• Assuming MATCH sorts data

2. Which of these is the correct syntax for the MATCH function to find an exact match?

easy

A. MATCH(range, value, 1)

B. MATCH(value, range, 0)

C. MATCH(value, range)

D. MATCH(range, value, -1)

Solution

1. Step 1: Recall MATCH syntax

   The correct syntax is MATCH(lookup_value, lookup_array, match_type). For exact match, match_type is 0.

2. Step 2: Check options

   MATCH(value, range, 0) uses correct order and match_type 0. Others have wrong order or missing match_type.

3. Final Answer:

   MATCH(value, range, 0) -> Option B

4. Quick Check:

   Exact match uses 0 as third argument [OK]

Hint: Exact match always uses 0 as third argument [OK]

Common Mistakes:

• Swapping value and range arguments
• Omitting the match_type argument
• Using 1 or -1 for exact match

3. Given the list {10, 20, 30, 40, 50} in cells A1:A5, what is the result of =MATCH(30, A1:A5, 0)?

medium

A. 3

B. 2

C. 30

D. Error

Solution

1. Step 1: Identify the position of 30 in the list

   The list is 10 (pos 1), 20 (pos 2), 30 (pos 3), 40 (pos 4), 50 (pos 5). 30 is at position 3.

2. Step 2: Understand MATCH with exact match

   MATCH returns the position number, so it returns 3.

3. Final Answer:

   3 -> Option A

4. Quick Check:

   Position of 30 in list = 3 [OK]

Hint: MATCH returns position, not the value itself [OK]

Common Mistakes:

• Returning the value 30 instead of position
• Confusing position with index starting at 0
• Getting error due to wrong match_type

4. What is wrong with this formula: =MATCH(25, A1:A5, 0) if A1:A5 contains {10, 20, 30, 40, 50}?

medium

A. No error, returns 3

B. Syntax error due to missing parentheses

C. Returns 2 because 20 is closest

D. Returns #N/A because 25 is not found

Solution

1. Step 1: Check if 25 exists in the list

   The list has 10, 20, 30, 40, 50. 25 is not present.

2. Step 2: Understand MATCH with exact match

   With match_type 0, if value not found, MATCH returns #N/A error.

3. Final Answer:

   Returns #N/A because 25 is not found -> Option D

4. Quick Check:

   Exact match missing value returns #N/A [OK]

Hint: Exact match returns #N/A if value missing [OK]

Common Mistakes:

• Assuming MATCH returns closest value position
• Thinking MATCH returns syntax error
• Ignoring #N/A error handling

5. You have a sorted list of prices in A1:A10. Which MATCH formula finds the position of the largest price less than or equal to 75?

hard

A. MATCH(75, A1:A10, 1)

B. MATCH(75, A1:A10, 0)

C. MATCH(75, A1:A10, -1)

D. MATCH(A1:A10, 75, 1)

Solution

1. Step 1: Understand match_type for approximate match

   match_type 1 finds largest value less than or equal to lookup_value in ascending sorted list.

2. Step 2: Check options for correct match_type

   MATCH(75, A1:A10, 1) uses 1, correct for this case. MATCH(75, A1:A10, -1) is for descending order, MATCH(75, A1:A10, 0) is exact match, MATCH(A1:A10, 75, 1) has wrong argument order.

3. Final Answer:

   MATCH(75, A1:A10, 1) -> Option A

4. Quick Check:

   Approximate match ascending uses 1 [OK]

Hint: Use 1 for largest value ≤ lookup in ascending list [OK]

Common Mistakes:

• Using 0 for approximate match
• Using -1 for ascending sorted list
• Omitting match_type argument