0
0
Google Sheetsspreadsheet~8 mins

Approximate vs exact match in Google Sheets - Dashboard Approaches Compared

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

This dashboard helps you understand the difference between approximate and exact match lookups in Google Sheets. It answers: How do exact and approximate matches affect the results when searching for values?

Sample Data
Product IDProduct NamePrice
1001Apple1.20
1003Banana0.50
1005Carrot0.30
1007Date1.50
1010Eggplant2.00

Note: Product IDs are sorted ascending for approximate match.

Dashboard Components
  • KPI Card 1: Exact Match Lookup
    =VLOOKUP(1003, A2:C6, 2, FALSE)
    Result: Banana (finds exact Product ID 1003)
  • KPI Card 2: Approximate Match Lookup
    =VLOOKUP(1004, A2:C6, 2, TRUE)
    Result: Banana (finds closest Product ID less than or equal to 1004)
  • KPI Card 3: Exact Match Not Found
    =IFERROR(VLOOKUP(1004, A2:C6, 2, FALSE), "Not found")
    Result: Not found (no exact 1004)
  • Table: Lookup Inputs and Results
    Lookup IDExact Match ResultApproximate Match Result
    1003=VLOOKUP(1003, A2:C6, 2, FALSE)=VLOOKUP(1003, A2:C6, 2, TRUE)
    1004=IFERROR(VLOOKUP(1004, A2:C6, 2, FALSE), "Not found")=VLOOKUP(1004, A2:C6, 2, TRUE)
    1006=IFERROR(VLOOKUP(1006, A2:C6, 2, FALSE), "Not found")=VLOOKUP(1006, A2:C6, 2, TRUE)
Dashboard Layout
+----------------------+-------------------------+
| Exact Match Lookup   | Approximate Match Lookup |
| (KPI Card 1)        | (KPI Card 2)             |
+----------------------+-------------------------+
| Exact Match Not Found| Lookup Inputs & Results  |
| (KPI Card 3)        | (Table)                  |
+----------------------+-------------------------+
Interactivity

You can change the Lookup ID in the table to any number. The exact and approximate match formulas update automatically to show how results differ. This helps you see when exact matches fail and approximate matches find the closest lower value.

Self Check

Try changing the Lookup ID to 1008. Which components update and what results do they show?

  • Exact Match Lookup shows Not found because 1008 is not in the list.
  • Approximate Match Lookup shows Date because 1010 is greater than 1008, so it returns the closest lower ID 1007 (Date).
  • Lookup Inputs & Results table updates formulas and results accordingly.
Key Result
Shows how exact and approximate VLOOKUP matches return different results for product lookups.