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.

Practice

1. What does the FALSE argument in the VLOOKUP function specify?

easy

A. It sorts the data automatically before searching.

B. It tells Excel to find the closest smaller value.

C. It ignores errors in the lookup value.

D. It tells Excel to find an exact match only.

Solution

1. Step 1: Understand the role of the last argument in VLOOKUP

   The last argument in VLOOKUP is the range_lookup, which controls match type.

2. Step 2: Identify what FALSE means in range_lookup

   When set to FALSE, VLOOKUP looks for an exact match only, returning an error if not found.

3. Final Answer:

   It tells Excel to find an exact match only. -> Option D

4. Quick Check:

   FALSE = exact match [OK]

Hint: FALSE means exact match; TRUE means approximate match [OK]

Common Mistakes:

• Confusing FALSE with approximate match
• Thinking FALSE sorts data automatically
• Assuming FALSE ignores errors

2. Which of the following is the correct syntax for an exact match VLOOKUP formula searching for value in A2 in range B2:D10 and returning the 3rd column?

easy

A. =VLOOKUP(A2, B2:D10, 3, TRUE)

B. =VLOOKUP(A2, B2:D10, 3, FALSE)

C. =VLOOKUP(A2, B2:D10, 3)

D. =VLOOKUP(A2, B2:D10, 3, 1)

Solution

1. Step 1: Recall exact match syntax for VLOOKUP

   Exact match requires the last argument to be FALSE.

2. Step 2: Check each option

   =VLOOKUP(A2, B2:D10, 3, FALSE) uses FALSE explicitly, which is the standard exact match syntax.

3. Final Answer:

   =VLOOKUP(A2, B2:D10, 3, FALSE) -> Option B

4. Quick Check:

   Exact match uses FALSE in VLOOKUP [OK]

Hint: Use FALSE for exact match in VLOOKUP formulas [OK]

Common Mistakes:

• Omitting the last argument defaults to approximate match
• Using TRUE when exact match is needed
• Confusing 0 and FALSE syntax

3. Given this sorted table in cells A1:B5:

A1: Score
A2: 50
A3: 70
A4: 90
A5: 100

What will =VLOOKUP(85, A2:B5, 1, TRUE) return?

medium

A. 70

B. 85

C. 90

D. #N/A

Solution

1. Step 1: Understand approximate match behavior

   With TRUE, VLOOKUP finds the largest value less than or equal to lookup value (85) in sorted data.

2. Step 2: Find closest smaller or equal value to 85

   Values are 50, 70, 90, 100. The largest value ≤ 85 is 70.

3. Final Answer:

   70 -> Option A

4. Quick Check:

   Approximate match returns closest smaller value [OK]

Hint: TRUE finds closest smaller value in sorted data [OK]

Common Mistakes:

• Expecting exact match with TRUE
• Choosing 90 because it's closest larger value
• Getting #N/A error for approximate match

4. You used =VLOOKUP(75, A2:B5, 2, TRUE) but got wrong results. The data in A2:A5 is not sorted. What is the best fix?

medium

A. Change the column index to 1.

B. Change TRUE to FALSE to force exact match.

C. Sort the data in ascending order before using TRUE.

D. Use INDEX instead of VLOOKUP.

Solution

1. Step 1: Identify problem with unsorted data and approximate match

   Approximate match (TRUE) requires data sorted ascending to work correctly.

2. Step 2: Fix by sorting data ascending

   Sorting the lookup column ascending ensures approximate match finds correct closest smaller value.

3. Final Answer:

   Sort the data in ascending order before using TRUE. -> Option C

4. Quick Check:

   Approximate match needs sorted data [OK]

Hint: Sort data ascending before using TRUE in VLOOKUP [OK]

Common Mistakes:

• Switching to FALSE without needing exact match
• Changing column index without reason
• Replacing VLOOKUP with INDEX unnecessarily

5. You have a price list sorted by quantity:

Qty: 1, 10, 20, 50
Price: 100, 90, 80, 70

Which formula correctly finds the price for an order of 15 units using approximate match?

hard

A. =VLOOKUP(15, A2:B5, 2, TRUE)

B. =VLOOKUP(15, A2:B5, 2, FALSE)

C. =VLOOKUP(15, A2:B5, 1)

D. =VLOOKUP(15, A2:B5, 2, 0)

Solution

1. Step 1: Understand the need for approximate match in price tiers

   For quantity 15, price depends on closest smaller quantity tier (10), so approximate match is needed.

2. Step 2: Choose formula with TRUE for approximate match

   =VLOOKUP(15, A2:B5, 2, TRUE) uses TRUE, enabling approximate match to find price for 10 units tier.

3. Final Answer:

   =VLOOKUP(15, A2:B5, 2, TRUE) -> Option A

4. Quick Check:

   Approximate match finds correct tier price [OK]

Hint: Use TRUE for tiered pricing with sorted quantity [OK]

Common Mistakes:

• Using FALSE causes #N/A if exact quantity missing
• Omitting last argument defaults to TRUE but unclear
• Using 0 instead of TRUE for approximate match