0
0
Excelspreadsheet~8 mins

Approximate vs exact match in Excel - Dashboard Approaches Compared

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - Approximate vs exact match
Goal

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

Sample Data
ProductPrice
Apple1.00
Banana0.50
Cherry2.00
Date3.00
Elderberry5.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.