Bird
Raised Fist0
Excelspreadsheet~15 mins

Approximate vs exact match in Excel - Business Scenario Comparison

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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.
Progress0 / 3 steps
Sample Data
Product CodePrice
100110
100515
101020
101525
102030

Sales Product CodePrice (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.

Practice

(1/5)
1. What does the FALSE argument in the VLOOKUP function specify?
easy
A. It sorts the data automatically before searching.
B. It tells Excel to find the closest smaller value.
C. It ignores errors in the lookup value.
D. It tells Excel to find an exact match only.

Solution

  1. Step 1: Understand the role of the last argument in VLOOKUP

    The last argument in VLOOKUP is the range_lookup, which controls match type.

  2. Step 2: Identify what FALSE means in range_lookup

    When set to FALSE, VLOOKUP looks for an exact match only, returning an error if not found.

  3. Final Answer:

    It tells Excel to find an exact match only. -> Option D

  4. Quick Check:

    FALSE = exact match [OK]
Hint: FALSE means exact match; TRUE means approximate match [OK]
Common Mistakes:
  • Confusing FALSE with approximate match
  • Thinking FALSE sorts data automatically
  • Assuming FALSE ignores errors
2. Which of the following is the correct syntax for an exact match VLOOKUP formula searching for value in A2 in range B2:D10 and returning the 3rd column?
easy
A. =VLOOKUP(A2, B2:D10, 3, TRUE)
B. =VLOOKUP(A2, B2:D10, 3, FALSE)
C. =VLOOKUP(A2, B2:D10, 3)
D. =VLOOKUP(A2, B2:D10, 3, 1)

Solution

  1. Step 1: Recall exact match syntax for VLOOKUP

    Exact match requires the last argument to be FALSE.

  2. Step 2: Check each option

    =VLOOKUP(A2, B2:D10, 3, FALSE) uses FALSE explicitly, which is the standard exact match syntax.

  3. Final Answer:

    =VLOOKUP(A2, B2:D10, 3, FALSE) -> Option B

  4. Quick Check:

    Exact match uses FALSE in VLOOKUP [OK]
Hint: Use FALSE for exact match in VLOOKUP formulas [OK]
Common Mistakes:
  • Omitting the last argument defaults to approximate match
  • Using TRUE when exact match is needed
  • Confusing 0 and FALSE syntax
3. Given this sorted table in cells A1:B5:
A1: Score
A2: 50
A3: 70
A4: 90
A5: 100

What will =VLOOKUP(85, A2:B5, 1, TRUE) return?
medium
A. 70
B. 85
C. 90
D. #N/A

Solution

  1. Step 1: Understand approximate match behavior

    With TRUE, VLOOKUP finds the largest value less than or equal to lookup value (85) in sorted data.

  2. Step 2: Find closest smaller or equal value to 85

    Values are 50, 70, 90, 100. The largest value ≤ 85 is 70.

  3. Final Answer:

    70 -> Option A

  4. Quick Check:

    Approximate match returns closest smaller value [OK]
Hint: TRUE finds closest smaller value in sorted data [OK]
Common Mistakes:
  • Expecting exact match with TRUE
  • Choosing 90 because it's closest larger value
  • Getting #N/A error for approximate match
4. You used =VLOOKUP(75, A2:B5, 2, TRUE) but got wrong results. The data in A2:A5 is not sorted. What is the best fix?
medium
A. Change the column index to 1.
B. Change TRUE to FALSE to force exact match.
C. Sort the data in ascending order before using TRUE.
D. Use INDEX instead of VLOOKUP.

Solution

  1. Step 1: Identify problem with unsorted data and approximate match

    Approximate match (TRUE) requires data sorted ascending to work correctly.

  2. Step 2: Fix by sorting data ascending

    Sorting the lookup column ascending ensures approximate match finds correct closest smaller value.

  3. Final Answer:

    Sort the data in ascending order before using TRUE. -> Option C

  4. Quick Check:

    Approximate match needs sorted data [OK]
Hint: Sort data ascending before using TRUE in VLOOKUP [OK]
Common Mistakes:
  • Switching to FALSE without needing exact match
  • Changing column index without reason
  • Replacing VLOOKUP with INDEX unnecessarily
5. You have a price list sorted by quantity:
Qty: 1, 10, 20, 50
Price: 100, 90, 80, 70

Which formula correctly finds the price for an order of 15 units using approximate match?
hard
A. =VLOOKUP(15, A2:B5, 2, TRUE)
B. =VLOOKUP(15, A2:B5, 2, FALSE)
C. =VLOOKUP(15, A2:B5, 1)
D. =VLOOKUP(15, A2:B5, 2, 0)

Solution

  1. Step 1: Understand the need for approximate match in price tiers

    For quantity 15, price depends on closest smaller quantity tier (10), so approximate match is needed.

  2. Step 2: Choose formula with TRUE for approximate match

    =VLOOKUP(15, A2:B5, 2, TRUE) uses TRUE, enabling approximate match to find price for 10 units tier.

  3. Final Answer:

    =VLOOKUP(15, A2:B5, 2, TRUE) -> Option A

  4. Quick Check:

    Approximate match finds correct tier price [OK]
Hint: Use TRUE for tiered pricing with sorted quantity [OK]
Common Mistakes:
  • Using FALSE causes #N/A if exact quantity missing
  • Omitting last argument defaults to TRUE but unclear
  • Using 0 instead of TRUE for approximate match
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard