HLOOKUP function in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a quick way to find monthly sales figures for different products using a horizontal lookup.

📊 Data: You have a table with product names in the first row and monthly sales figures below each product.

🎯 Deliverable: Create a formula using HLOOKUP to find the sales for a specific product in a given month.

Sample Data

	Product A	Product B	Product C
Jan	120	150	100
Feb	130	160	110
Mar	140	170	120

1

Step 1: Identify the product name you want to find sales for. For example, 'Product B'.

Expected Result

2

Step 2: Identify the month you want to find sales for. For example, 'Feb'.

Expected Result

3

Step 3: Use the HLOOKUP function to find the sales for 'Product B' in 'Feb'. The formula looks up 'Product B' in the first row and returns the value from the row corresponding to 'Feb'.

=HLOOKUP("Product B", A1:D4, 3, FALSE)

Expected Result

160

4

Step 4: Explain the formula: "Product B" is the value to find in the first row; A1:D4 is the table range including headers; 3 means the third row (Feb) in the table; FALSE means exact match.

Expected Result

5

Step 5: Change the row index number to 4 to find sales for 'Product B' in 'Mar'.

=HLOOKUP("Product B", A1:D4, 4, FALSE)

Expected Result

170

Final Result

Product Sales Lookup

+-----------+-----------+-----------+-----------+
|           | Product A | Product B | Product C |
+-----------+-----------+-----------+-----------+
| Jan       | 120       | 150       | 100       |
| Feb       | 130       | 160       | 110       |
| Mar       | 140       | 170       | 120       |
+-----------+-----------+-----------+-----------+

Example: Sales for Product B in Feb = 160

✓HLOOKUP can find values horizontally by looking up a value in the first row.

✓The row index number tells Excel which row's value to return.

✓Using FALSE ensures an exact match for the product name.

Bonus Challenge

Create a dynamic HLOOKUP formula that uses cell references for the product name and month instead of hardcoded values.

Show Hint

Use MATCH function to find the row number for the month dynamically and use cell references for the product name.