Approximate vs exact match in Excel - Practice Questions

Challenge - 5 Problems

📊 Formula Result

intermediate

Exact match with VLOOKUP

You have a list of product codes in column A and their prices in column B. You want to find the price of product code "P102" exactly using VLOOKUP. Which formula will return the correct price?

Excel

A1:B5
P101 10
P102 15
P103 20
P104 25
P105 30

A =VLOOKUP("P102", A1:B5, 3, FALSE)

B =VLOOKUP("P102", A1:B5, 1, FALSE)

C =VLOOKUP("P102", A1:B5, 2, FALSE)

D =VLOOKUP("P102", A1:B5, 2, TRUE)

💡 Hint

Use FALSE for exact match in VLOOKUP.

VLOOKUP with FALSE as the last argument looks for an exact match. Using TRUE or omitting it causes approximate match, which may return wrong results if data is not sorted.

📊 Formula Result

intermediate

Approximate match with VLOOKUP

You have a table of scores in column A and grades in column B sorted ascending by scores. You want to find the grade for a score of 78 using approximate match. Which formula returns the correct grade?

Excel

A1:B5
50 F
65 D
75 C
85 B
95 A

A =VLOOKUP(78, A1:B5, 2, TRUE)

B =VLOOKUP(78, A1:B5, 2, FALSE)

C =VLOOKUP(78, A1:B5, 1, TRUE)

D =VLOOKUP(78, A1:B5, 3, TRUE)

💡 Hint

Approximate match requires sorted data and TRUE as last argument.

With approximate match (TRUE), VLOOKUP finds the largest value less than or equal to the lookup value. Here, 75 is the largest score ≤ 78, so grade C is returned.

❓ Function Choice

advanced

Choosing between MATCH exact and approximate

You want to find the position of the value 40 in a sorted list in column A. Which MATCH formula will return the position of the closest value less than or equal to 40 if 40 is not found?

A =MATCH(40, A1:A10, 1)

B =MATCH(40, A1:A10, 0)

C =MATCH(40, A1:A10, -1)

D =MATCH(40, A1:A10)

💡 Hint

Use 1 for approximate match with ascending sorted data.

MATCH with 1 finds the largest value less than or equal to the lookup value in ascending sorted data. 0 requires exact match, -1 is for descending sorted data.

🎯 Scenario

advanced

Error caused by approximate match on unsorted data

You use this formula to find a price for product code "P103" in an unsorted list:
=VLOOKUP("P103", A1:B5, 2, TRUE)
What is the likely result?

A #REF! error due to wrong column index

B #N/A error because exact match not found

C Correct price because VLOOKUP always finds exact match first

D Incorrect price or wrong result because data is not sorted for approximate match

💡 Hint

Approximate match requires sorted data.

Using approximate match (TRUE) on unsorted data can return wrong or unexpected results because VLOOKUP assumes data is sorted ascending.

❓ data_analysis

expert

Comparing outputs of exact vs approximate match

Given the sorted table:
A1:B6
10 Low
20 Medium
30 High
40 Very High
50 Extreme

What is the output of these formulas?
1) =VLOOKUP(35, A1:B6, 2, TRUE)
2) =VLOOKUP(35, A1:B6, 2, FALSE)

A 1) Very High<br>2) Medium

B 1) High<br>2) #N/A error

C 1) Medium<br>2) #N/A error

D 1) #N/A error<br>2) High

💡 Hint

Approximate match returns closest lower value; exact match fails if no exact value.

For approximate match, 35 is between 30 and 40, so it returns the label for 30 which is High. For exact match, 35 is not found, so it returns #N/A.