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.