Approximate vs exact match in Google Sheets - Business Scenario Comparison

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to find product prices based on product codes. Some product codes may not match exactly, so you need to use both exact and approximate match methods to find prices.

📊 Data: You have a list of product codes and their prices. You also have a list of sales transactions with product codes that may not exactly match the product list.

🎯 Deliverable: Create a sheet that shows the price for each sales transaction using exact match first, and if no exact match is found, use approximate match to find the closest product price.

Sample Data

Product Code	Price
1001	25
1003	30
1005	45
1010	50
1020	60

Transaction ID	Product Code
T001	1003
T002	1004
T003	1015
T004	1020
T005	1000

1

Step 1: Sort the product list by Product Code in ascending order to prepare for approximate match.

Select the product list range and sort by 'Product Code' ascending.

Expected Result

Product codes sorted: 1001, 1003, 1005, 1010, 1020.

2

Step 2: In the sales transactions sheet, use VLOOKUP with exact match to find the price for each product code.

=VLOOKUP(B2, Products!$A$2:$B$6, 2, FALSE)

Expected Result

For T001 (1003), price is 30; for T004 (1020), price is 60; others show #N/A.

3

Step 3: Use IFERROR to handle missing exact matches and apply VLOOKUP with approximate match for those cases.

=IFERROR(VLOOKUP(B2, Products!$A$2:$B$6, 2, FALSE), VLOOKUP(B2, Products!$A$2:$B$6, 2, TRUE))

Expected Result

For T002 (1004), approximate price is 30 (from 1003); for T003 (1015), approximate price is 50 (from 1010); for T005 (1000), approximate price is #N/A because 1000 is less than smallest product code.

4

Step 4: Improve the formula to show 'No price found' instead of #N/A when approximate match fails.

=IFERROR(VLOOKUP(B2, Products!$A$2:$B$6, 2, FALSE), IFERROR(VLOOKUP(B2, Products!$A$2:$B$6, 2, TRUE), "No price found"))

Expected Result

For T005 (1000), the result is 'No price found'.

Final Result

Transaction ID | Product Code | Price
-------------------------------------
T001           | 1003         | 30
T002           | 1004         | 30
T003           | 1015         | 50
T004           | 1020         | 60
T005           | 1000         | No price found

✓Exact match finds prices only for product codes that exist exactly in the product list.

✓Approximate match finds the closest lower product code price when exact match is missing.

✓Approximate match fails if the lookup value is smaller than the smallest product code.

✓Combining exact and approximate match with IFERROR gives a complete price lookup solution.

Bonus Challenge

Create a formula that finds the closest product price either lower or higher than the product code when exact match is missing.

Show Hint

Use FILTER and MIN/ABS functions to find the closest product code above or below the lookup value.

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