Approximate vs exact match in Excel - Dashboard Approaches Compared

Dashboard Mode - Approximate vs exact match

Goal

Find prices for products using exact and approximate matches to compare results.

Sample Data

Product	Price
Apple	1.00
Banana	0.50
Cherry	2.00
Date	3.00
Elderberry	5.00

Lookup Product
Banana
Blueberry
Cherry
Apricot

Dashboard Components

• KPI Card: Exact Match Price
  =VLOOKUP(B2, A2:B6, 2, FALSE)
  Finds the exact price for the product in B2. Returns #N/A if not found.
• KPI Card: Approximate Match Price
  =VLOOKUP(B2, A2:B6, 2, TRUE)
  Finds the closest price less than or equal to the product in B2. Data must be sorted ascending by product.
• Table: Lookup Results for all products
  =VLOOKUP(B2, A2:B6, 2, FALSE) and =VLOOKUP(B2, A2:B6, 2, TRUE) copied down for each lookup product.

Dashboard Layout

+----------------------+------------------------+
|  Lookup Product List  |  Exact Match Price KPI  |
|  (Table of products)  |                        |
+----------------------+------------------------+
| Approximate Match Price KPI                    |
+-----------------------------------------------+

Interactivity

Select a product from the lookup list. The exact and approximate match prices update automatically to show the price found by each method.

Self Check

If you add a filter to show only products starting with 'B', which lookup products remain? How do the exact and approximate match prices change for 'Blueberry'?

Key Result

Compare exact and approximate match prices for lookup products using VLOOKUP in Excel.

Practice

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