For day 10 in the 30XL30D challenge, we'll examine the HLOOKUP function. Not too surprisingly, this function is very similar to VLOOKUP, and works with items that are in a Horizontal list.
Poor HLOOKUP isn't as popular as its sibling though, because most tables are set up with the lookup values listed vertically. When was the last time that you wanted to search for a value across a row, and then return a value from that column, in a specific row below?
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).
Anyway, let's give HLOOKUP its moment in the spotlight, and take a look at its information and examples. Remember, if you have other tips or examples, please share them in the comments.
Function 10: HLOOKUP
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.
How Could You Use HLOOKUP?
The HLOOKUP function can find exact matches in the lookup row, or the closest match, so it can:
- Find the sales total in a selected region
- Find rate in effect on selected date
The HLOOKUP function has the following syntax:
- lookup_value: the value that you want to look for -- it can be a value, or a cell reference.
- table_array: the lookup table -- this can be a range reference or a range name, with 2 or more columns.
- row_index_num: the row that has the value you want returned, based on the row number within the table.
- [range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value row sorted in ascending order.
Like VLOOKUP, the HLOOKUP function can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested. Wherever possible, use a table that is sorted by the first row, in ascending order, and use an approximate match. You can 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. We'll look at those functions later in the challenge, and see how flexible and powerful they are.
Example 1: Find the Sales for a Selected 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. It's important to get the correct amount, so the following settings are used:
- a region name is entered in cell B7
- the 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:
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 Selected Date
Usually, an exact match is required when using HLOOKUP, but sometimes an approximate match works better. For example, if rates change at the start of each quarter, only those dates are entered as column headings. Then, with HLOOKUP and an approximate match, you can find the rate that was in effect for that 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:
If the date is not found in the first row of the lookup table, the HLOOKUP formula result is the next largest value that is less than lookup_value. The lookup value in this example is March 15th. That value is not in the date row, so the value for January 1st (0.25) is returned.
Download the HLOOKUP Function 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.
Watch the HLOOKUP Video
To see a demonstration of the examples in the HLOOKUP function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Find Table Data with HLOOKUP Function