Approximate vs exact match in Google Sheets - Quick Revision & Key Differences

Recall & Review

beginner

What does an exact match mean in a lookup formula like VLOOKUP?

An exact match means the formula looks for a value that exactly matches the lookup value. If it doesn't find the exact value, it returns an error or a specific result indicating no match.

beginner

What is an approximate match in lookup formulas?

An approximate match means the formula finds the closest value that is less than or equal to the lookup value. It is used when data is sorted and an exact match might not exist.

beginner

How do you specify an exact match in VLOOKUP?

You set the fourth argument, called is_sorted, to FALSE or 0. For example: =VLOOKUP(A2, B2:D10, 2, FALSE).

beginner

How do you specify an approximate match in VLOOKUP?

You set the fourth argument, is_sorted, to TRUE or omit it. For example: =VLOOKUP(A2, B2:D10, 2, TRUE) or =VLOOKUP(A2, B2:D10, 2).

intermediate

Why must data be sorted when using approximate match in VLOOKUP?

Data must be sorted in ascending order because VLOOKUP stops searching once it finds a value greater than the lookup value. Sorting ensures it finds the closest smaller or equal value correctly.

What happens if you use VLOOKUP with FALSE for the last argument and the exact value is not found?

A It returns the closest smaller value

B It returns an error like #N/A

C It returns the closest larger value

D It returns the first value in the range

Using FALSE means exact match is required. If no exact match is found, VLOOKUP returns #N/A error.

If you want to find the closest match less than or equal to your lookup value, which VLOOKUP setting should you use?

A Use a different formula like SUM

B Set last argument to FALSE

C Set last argument to TRUE or omit it

D Sort data in descending order

Setting the last argument to TRUE or omitting it enables approximate match, which finds the closest smaller or equal value.

What is the default behavior of VLOOKUP if the last argument is omitted?

A Approximate match (TRUE)

B Exact match (FALSE)

C Returns an error

D Returns the first value

If omitted, VLOOKUP assumes TRUE for approximate match.

Why is sorting data important when using approximate match in VLOOKUP?

A Because VLOOKUP stops searching after passing the lookup value

B Because VLOOKUP only works on sorted data

C Because sorting changes the formula syntax

D Sorting is not important

VLOOKUP stops searching once it finds a value greater than the lookup value, so sorting ensures it finds the correct closest smaller value.

Which of these is true about exact and approximate matches?

A Both require FALSE

B Exact match requires TRUE, approximate match requires FALSE

C Both require TRUE

D Exact match requires FALSE, approximate match requires TRUE or omitted

Exact match uses FALSE, approximate match uses TRUE or omitted for the last argument in VLOOKUP.

Explain the difference between approximate and exact match in lookup formulas.

Think about how VLOOKUP behaves with TRUE vs FALSE in the last argument.

Describe why sorting data is necessary when using approximate match in VLOOKUP.

Consider how VLOOKUP searches through the data.

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