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
To see how the HLOOKUP function works, you can watch this short video tutorial. This video uses the sample workbook from this tutorial.
The HLOOKUP function can find an exact match in the lookup row, or the closest match. There are examples below that:
The HLOOKUP function has the following syntax:
The HLOOKUP function can be slow, especially when matching an exact text string match, in an unsorted table. For better results:
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:
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
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:
The formula in cell D5 is:
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.
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.
For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Last updated: October 4, 2016 7:34 PM