The HLOOKUP function looks for a value in the first row of a table, and returns another value from the same column in that table. It is very similar to VLOOKUP, which is used for vertical lists
Video: HLOOKUP Function
To see how the HLOOKUP function works, you can watch this short video tutorial. This video uses the sample workbook from this tutorial.
Uses for HLOOKUP
The HLOOKUP function can find an exact match in the lookup row, or the closest match. There are examples below that:
- Find the sales total in a specific region
- Find the interest rate in effect on a specific date
HLOOKUP Syntax
The HLOOKUP function has the following syntax:
- HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
- lookup_value: value to look for -- can be a value, or a cell reference.
- table_array: lookup table -- range reference or a range name, with 2 or more columns.
- row_index_num: row in the lookup table, with value to be returned
- [range_lookup]: for exact match, use FALSE or 0; for approximate match, use TRUE or 1, with lookup value row sorted in ascending order.
HLOOKUP Traps
The HLOOKUP function can be slow, especially when matching an exact text string match, in an unsorted table. For better results:
- If possible, use a table that is sorted by the first row, in ascending order, and use an approximate match.
- Use MATCH or COUNTIF to check for the value first, to make sure it is in the table's first row.
- Other functions, such as INDEX and MATCH, can be used to return values from a table, and are more efficient.
Example 1: Find Sales for a Specific Region
The HLOOKUP function looks for a value in the top row of the lookup table. In this example, we'll find the sales total for a selected region. We want an exact match for the Region name, so the following settings are used:
- region name is entered in cell B7
- region lookup table has two rows, and is in range C2:F3
- sales total is in row 2 of the table.
- FALSE is used in the last argument, to find an exact match for the lookup value.
The formula in cell C7 is:
=HLOOKUP(B7,C2:F3,2,FALSE)
If the region name is not found in the first row of the lookup table, the HLOOKUP formula result is #N/A
Example 2: Find Rate for Specific Date
Usually, an exact match is required when using HLOOKUP, but sometimes an approximate match works better. In this example, rates change at the start of each quarter, and those dates are entered as column headings.
With HLOOKUP set for an approximate match, you can find the rate that was in effect for any date. In this example:
- a date is entered in cell C5
- the rate lookup table has two rows, and is in range C2:F3
- the lookup table is sorted by the Date row, in ascending order
- rate is in row 2 of the table.
- TRUE is used in the last argument, to find an approximate match for the lookup value.
The formula in cell D5 is:
=HLOOKUP(C5,C2:F3,2,TRUE)
If the exact date is not found in the first row of the lookup table, the HLOOKUP formula returns the rate for the next largest date that is less than lookup_value.
The lookup value in this example is March 15th. That date is not in the date row, so the value for January 1st (0.25) is returned.
Download the HLOOKUP Sample File
To see the formulas used in today's examples, you can download the HLOOKUP function sample workbook. The file is zipped, and is in Excel 2007 file format.