HLOOKUP function in Google Sheets - Dashboard Guide

Dashboard Mode - HLOOKUP function

Dashboard Goal

Find sales data for specific products by looking up product names in the top row and returning sales values below.

Sample Data

	Product A	Product B	Product C	Product D
Q1 Sales	150	200	250	300
Q2 Sales	180	220	270	320
Q3 Sales	210	240	290	350
Q4 Sales	230	260	310	370

Dashboard Components

• KPI Card: Q2 Sales for Product C
  Formula: =HLOOKUP("Product C", B1:E5, 3, FALSE)
  Result: 270
• KPI Card: Q4 Sales for Product A
  Formula: =HLOOKUP("Product A", B1:E5, 5, FALSE)
  Result: 230
• Summary Table: All Q3 Sales
  Formula for Q3 Sales row: =HLOOKUP(B1:E1, B1:E5, 4, FALSE) (applied per product)
  Values: Product A=210, Product B=240, Product C=290, Product D=350

Dashboard Layout

+----------------------+----------------------+
| Q2 Sales Product C:   | Q4 Sales Product A:   |
|        270           |        230           |
+----------------------+----------------------+
|                  Summary Table                 |
|  Product A | Product B | Product C | Product D  |
|     210    |    240    |    290    |    350    |
+------------------------------------------------+

Interactivity

User can change the product name in a cell (e.g., cell G1) to update the KPI cards dynamically using formulas like =HLOOKUP(G1, B1:E5, 3, FALSE). This lets the dashboard show sales for any product selected.

Self Check

If you change the product name in the lookup cell to "Product B", what will be the Q2 Sales value shown in the KPI card?

Answer: 220

Key Result

Dashboard shows sales for selected products using HLOOKUP to find quarterly sales data.