Approximate vs exact match in Excel - Business Scenario Comparison

Scenario Mode

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

📋 Request: Your manager wants you to create a lookup table to find product prices based on product codes. Some product codes may not match exactly, so you need to use approximate match for price ranges and exact match for specific product codes.

📊 Data: You have a list of product codes and their prices. You also have a sales list with product codes that need to be matched to prices.

🎯 Deliverable: Create formulas that use exact match to find prices for specific product codes and approximate match to find prices for product codes that fall within price ranges.

Sample Data

Product Code	Price
1001	10
1005	15
1010	20
1015	25
1020	30

Sales Product Code	Price (Exact Match)	Price (Approximate Match)
1005
1007
1015
1022
999

1

Step 1: Use VLOOKUP with exact match to find the price for each sales product code.

=VLOOKUP(A2, $A$7:$B$11, 2, FALSE)

Expected Result

For product code 1005, price is 15; for 1007, #N/A; for 1015, 25; for 1022, #N/A; for 999, #N/A

2

Step 2: Use VLOOKUP with approximate match to find the price for each sales product code. Make sure the product codes in the lookup table are sorted ascending.

=VLOOKUP(A2, $A$7:$B$11, 2, TRUE)

Expected Result

For product code 1005, price is 15; for 1007, price is 15; for 1015, price is 25; for 1022, price is 30; for 999, #N/A

3

Step 3: Explain that exact match returns #N/A if no exact product code is found, while approximate match returns the closest lower product code price.

No formula needed

Expected Result

Learner understands difference between exact and approximate match in VLOOKUP.

Final Result

Sales Product Code | Price (Exact Match) | Price (Approximate Match)
-------------------|---------------------|-------------------------
1005               | 15                  | 15                      
1007               | #N/A                | 15                      
1015               | 25                  | 25                      
1022               | #N/A                | 30                      
999                | #N/A                | #N/A                    

✓Exact match returns price only if product code matches exactly.

✓Approximate match returns price for closest lower product code if exact code is not found.

✓Product codes in lookup table must be sorted ascending for approximate match to work correctly.

Bonus Challenge

Create a formula that returns 'Not Found' instead of #N/A for exact match lookups.

Show Hint

Use IFERROR function to catch errors and display custom text.