HLOOKUP function in Excel - Dashboard Guide

Dashboard Mode - HLOOKUP function

Dashboard Goal

Find product prices and stock levels quickly by looking up product names in the header row.

Sample Data

	Product A	Product B	Product C	Product D
Price	10	15	20	25
Stock	100	150	200	250

Dashboard Components

• KPI Card: Price of Selected Product
  Formula: =HLOOKUP(B2, B4:E6, 2, FALSE)
  Explanation: Looks for the product name typed in cell B2 in the header row (B4:E4) and returns the price from the second row.
• KPI Card: Stock of Selected Product
  Formula: =HLOOKUP(B2, B4:E6, 3, FALSE)
  Explanation: Looks for the product name typed in cell B2 and returns the stock from the third row.
• Input Cell for Product Name
  Cell B2 where user types product name (e.g., "Product B")
• Product Table
  Range B4:E6 with product names in first row, prices in second, stock in third.

Dashboard Layout

+-----------------------------+
| Product Lookup Dashboard     |
+-----------------------------+
| Enter Product Name: [ B2 ]  |
+-----------------------------+
| Price:       [Price KPI]    |
+-----------------------------+
| Stock:       [Stock KPI]    |
+-----------------------------+
| Product Table (B4:E6)       |
| +------------+------------+ |
| | Product A  | Product B  | |
| | Price=10   | Price=15   | |
| | Stock=100  | Stock=150  | |
| +------------+------------+ |
+-----------------------------+

Interactivity

User types a product name in cell B2. The two KPI cards update automatically to show the price and stock for that product using the HLOOKUP formulas.

If the product name is not found exactly, the formulas return an error, prompting the user to check spelling.

Self Check

Try typing "Product C" in cell B2. What values appear in the Price and Stock KPI cards?

Answer: Price shows 20 and Stock shows 200.

Key Result

Dashboard to lookup product price and stock using HLOOKUP based on product name input.