The HLOOKUP function allows you to retrieve data from a horizontal table in Excel. It searches for a value across the top row of a table and returns information from a row below it. In this lesson, you’ll see how HLOOKUP can be used to pull labor rates and dispatch fees by month from a pricing table, helping HVAC companies automatically bring operational data into their service call analysis.
Download the Excel file used in this tutorial:
Create a reference table where the lookup values are arranged across the first row.
In the example from the tutorial, the table includes:
This horizontal structure is what allows the HLOOKUP function to retrieve values correctly.
In the working sheet, identify the value that will drive the lookup. In the example, the month from the service call record is the lookup value.
This month will be used to search across the first row of the pricing table.
Insert the HLOOKUP function and reference:
Because the labor rate is located in the second row of the table, the function returns the labor rate associated with that month.
When copying the formula down the column, the table reference must remain fixed.
To prevent Excel from shifting the table range as the formula is filled down:
This step ensures the lookup continues using the correct month row.
After locking the table reference:
The same lookup table can be used to return additional pricing fields.
For example, to retrieve the dispatch fee:
Instead of manually typing absolute references, you can:
This makes the formula easier to copy across multiple rows.
Once the lookup table is locked and the row index is set correctly:
You now have a working lookup that retrieves pricing values from a horizontal reference table, allowing monthly labor rates and dispatch fees to be pulled into operational data automatically.
Q1. What does the HLOOKUP function do in Excel?
The HLOOKUP function searches for a value in the first row of a table and returns the corresponding value from a row below it. It’s designed for situations where your lookup data is organized horizontally across columns.
Q2. When should I use HLOOKUP instead of VLOOKUP?
Use HLOOKUP when your lookup values are stored across the top row of a table and the results you want are located in rows below. If your lookup values are organized vertically in the first column, then VLOOKUP is the better option.
Q3. What types of business data work well with HLOOKUP?
HLOOKUP works well for retrieving values such as monthly pricing, labor rates, dispatch fees, commission tables, or seasonal pricing schedules where categories are arranged across columns.
Q4. Why is exact match important in lookup formulas?
Using an exact match ensures that Excel returns the correct value for the lookup item. This is especially important when working with months, product codes, or pricing tables where an incorrect match could return the wrong value.
Q5. Why do formulas sometimes break when I copy them down a column?
This usually happens because the table reference shifts when the formula is copied. Using absolute referencing locks the lookup table in place so Excel always searches the correct range.
Q6. Is HLOOKUP still used today if Excel has newer functions?
Yes. While newer functions like XLOOKUP provide more flexibility, HLOOKUP is still widely used and remains important for understanding how traditional Excel lookup formulas work.