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.