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.