Approximate vs exact match in Google Sheets - Practice Questions

Challenge - 5 Problems

📊 Formula Result

intermediate

Exact match with VLOOKUP

Given the table below in cells A1:B4:

A1: Product
A2: Apple
A3: Banana
A4: Cherry

B1: Price
B2: 1.2
B3: 0.5
B4: 2.0

What is the result of the formula =VLOOKUP("Banana", A2:B4, 2, FALSE)?

A 2.0

B 0.5

C 1.2

D #N/A

💡 Hint

Remember, FALSE means exact match in VLOOKUP.

The formula looks for "Banana" exactly in the first column and returns the price 0.5.

📊 Formula Result

intermediate

Approximate match with VLOOKUP

Using the same table as before, what is the result of the formula =VLOOKUP("Blueberry", A2:B4, 2, TRUE)?

Note: The list in column A is sorted alphabetically.

A 0.5

B 2.0

C 1.2

D #N/A

💡 Hint

TRUE means approximate match; it finds the closest value less than or equal to the lookup value.

Since "Blueberry" is not found, VLOOKUP with TRUE returns the price for the closest smaller value, which is "Banana" at 0.5.

❓ Function Choice

advanced

Choosing the right match type

You want to find the price of a product in a list where the product names are not sorted. Which formula will correctly find the price of "Cherry"?

A =VLOOKUP("Cherry", A2:B4, 2, FALSE)

B =VLOOKUP("Cherry", A2:B4, 2, TRUE)

C =VLOOKUP("Cherry", A2:B4, 2)

D =VLOOKUP("Cherry", A2:B4, 3, FALSE)

💡 Hint

When data is unsorted, use exact match.

FALSE forces exact match, which works regardless of sorting. TRUE requires sorted data and may return wrong results.

🎯 Scenario

advanced

Approximate match pitfalls

You have a grade table:

A1: Score
A2: 0
A3: 60
A4: 70
A5: 80
A6: 90

B1: Grade
B2: F
B3: D
B4: C
B5: B
B6: A

You want to assign a grade for a score of 75 using =VLOOKUP(75, A2:B6, 2, TRUE). What grade will you get?

A F

B B

C D

D C

💡 Hint

TRUE finds the largest value less than or equal to the lookup value.

75 is between 70 and 80, so it matches 70 which corresponds to grade C.

❓ data_analysis

expert

Detecting errors with approximate match

You have a price list sorted by product code in column A and prices in column B. You use =VLOOKUP(105, A2:B10, 2, TRUE) to find the price for product code 105. The product codes are: 100, 102, 104, 106, 108.

What price will the formula return and why might this cause a problem?

A Price for 106; approximate match rounds up to next code

B #N/A error; 105 is not in the list

C Price for 104; because 105 is not found, it returns the closest smaller code's price

D Price for 100; approximate match always returns first value

💡 Hint

Approximate match returns the largest value less than or equal to the lookup value.

105 is not found, so VLOOKUP returns price for 104. This can cause errors if you expect exact matches.

Practice

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

easy

A. It sorts the data before searching.

B. It tells VLOOKUP to find an approximate match.

C. It tells VLOOKUP to find an exact match.

D. It ignores errors in the lookup.

Solution

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

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

2. Step 2: Interpret FALSE in range_lookup

   FALSE means VLOOKUP looks for an exact match only, no close values.

3. Final Answer:

   It tells VLOOKUP to find an exact match. -> Option C

4. Quick Check:

   FALSE = exact match [OK]

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

Common Mistakes:

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

2. Which of these is the correct syntax for an exact match VLOOKUP in Google Sheets?

easy

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

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

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

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

Solution

1. Step 1: Recall the exact match parameter for VLOOKUP

   Exact match requires the fourth argument to be FALSE.

2. Step 2: Check each option

   =VLOOKUP(A2, B2:D10, 2) omits the fourth argument (defaults to TRUE, approximate). =VLOOKUP(A2, B2:D10, 2, TRUE) uses TRUE (approximate). =VLOOKUP(A2, B2:D10, 2, FALSE) uses FALSE (correct exact match). =VLOOKUP(A2, B2:D10, 2, 1) uses 1 which is approximate match.

3. Final Answer:

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

4. Quick Check:

   FALSE means exact match [OK]

Hint: Use FALSE as 4th argument for exact match VLOOKUP [OK]

Common Mistakes:

• Omitting the 4th argument for exact match
• Using TRUE instead of FALSE for exact match
• Using 1 instead of FALSE in Google Sheets

3. Given this sorted data in column A: 10, 20, 30, 40, 50, what will =VLOOKUP(25, A1:A5, 1, TRUE) return?

medium

A. Error

B. 25

C. 30

D. 20

Solution

1. Step 1: Understand approximate match behavior

   With TRUE (approximate), VLOOKUP finds the largest value less than or equal to the lookup value.

2. Step 2: Find largest value ≤ 25 in the list

   Values are 10, 20, 30, 40, 50. The largest value ≤ 25 is 20.

3. Final Answer:

   20 -> Option D

4. Quick Check:

   Approximate match returns closest lower value [OK]

Hint: Approximate match returns closest smaller or equal value [OK]

Common Mistakes:

• Expecting exact 25 to be found
• Choosing next higher value 30
• Thinking TRUE means exact match

4. You used =VLOOKUP(15, A1:B5, 2, TRUE) but got wrong results. The data in column A is unsorted. What is the fix?

medium

A. Sort column A ascending or use FALSE for exact match.

B. Change TRUE to 1 in the formula.

C. Remove the 4th argument entirely.

D. Use HLOOKUP instead.

Solution

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

   Approximate match (TRUE) requires sorted data; unsorted data causes wrong results.

2. Step 2: Fix by sorting data or using exact match

   Either sort column A ascending or use FALSE for exact match to avoid errors.

3. Final Answer:

   Sort column A ascending or use FALSE for exact match. -> Option A

4. Quick Check:

   Approximate match needs sorted data [OK]

Hint: Approximate match needs sorted data or use FALSE for exact [OK]

Common Mistakes:

• Using 1 instead of FALSE (still approximate)
• Removing 4th argument causes approximate match on unsorted data
• Switching to HLOOKUP without reason

5. You have a price table sorted by quantity: 1= $10, 5= $8, 10= $6. Which formula finds the correct price for quantity in A2 using approximate match?

hard

A. =VLOOKUP(A2, A1:B3, 2, TRUE)

B. =VLOOKUP(A2, {1,10,5,8,10,6}, 2, TRUE)

C. =VLOOKUP(A2, A1:B3, 2, FALSE)

D. =VLOOKUP(A2, A1:B3, 2)

Solution

1. Step 1: Recognize approximate match use case

   Price depends on quantity ranges, so approximate match (TRUE) finds closest lower quantity price.

2. Step 2: Check data sorting and formula correctness

   Data in A1:B3 is sorted by quantity ascending. Using TRUE for approximate match is correct. =VLOOKUP(A2, A1:B3, 2, TRUE) matches this.

3. Final Answer:

   =VLOOKUP(A2, A1:B3, 2, TRUE) -> Option A

4. Quick Check:

   Approximate match with sorted data finds correct price [OK]

Hint: Use TRUE with sorted data for range pricing lookups [OK]

Common Mistakes:

• Using FALSE causes no approximate match
• Incorrect array syntax in option A
• Omitting 4th argument defaults to TRUE but less clear