The Excel HLOOKUP Function

Basic Description

The Excel Hlookup function 'looks up' a given value in the top row of a data array (or table), and returns the corresponding value from another row of the array.

The syntax of the function is:

HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Where the function arguments are as follows:

lookup_value-The value that you want to search for, in the first row of the supplied data array.
table_array-The data array or table, containing the data to be searched in the top row, and the return values in any other row.
row_index_num-The row number, within the supplied table_array, that you want the corresponding value to be returned from.
[range_lookup]-An optional logical argument, which can be set to TRUE or FALSE, meaning:
TRUE-

if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value.

Note: If [range_lookup] is set to TRUE, the top row of the table_array must be in ascending order).

FALSE-if the function cannot find an exact match to the supplied lookup_value, it should return an error.

Wildcards

In text-related Hlookups, when the [match_type] argument is set to 0, the lookup_value can contain the following wildcard characters:

?    -    matches any single character
*    -    matches any sequence of characters

Hlookup Function Examples

Hlookup Example 1 - Exact Match Lookup

Cells A2-F6 of the spreadsheet below, show the exam scores for 5 students in 4 different subjects. If you want to look up a specific score (e.g. Biology) for one of the students (e.g. Ed), this can be done using the Hlookup function, as shown in cell B10 of the spreadsheet.

 Formulas:

Example of use of the Excel Hlookup Function

 Results:

Excel Hlookup Function Result

In the above example, the Hlookup function searches through the top row of the table_array (the range A2-F2), to find a match for the lookup_value (the name "Ed"). When the the name "Ed" is found, the function returns the corresponding value from the 5th row of the table_array.

Excel Hlookup Example Explanation

This is illustrated in the above spreadsheet on the right. The function finds the name 'Ed' in the top row of the table_array and then returns the value from the 5th row of the table_array.

If we change the name in cell A10 of the spreadsheet from 'Ed' to 'Cara', the Hlookup functions would automatically recalculate the function to display the exam results for Cara.


Hlookup Example 2 - Closest Match Lookup

Cells A1-F3 of the spreadsheet below, show body types relating to body mass index (BMI), for the ranges 0 - 18.4, 18.5 - 24.9, 25.0 - 29.9 and over 30.

Cell C6 shows the user's current BMI, which is 23.5, and cell C7 shows the Hlookup function that is used to look up the body type that relates to this BMI.

Example of use of the Excel Hlookup Function

The Hlookup function in the above spreadsheet returns the result "Normal Weight", which is the correct body type for a BMI of 23.5.

Note that, in this example, the [range_lookup] argument is set to TRUE, to tell that function that, if it cannot find an exact match to the supplied lookup_value, it should use the closest match below this value. Therefore, for all BMIs up to and including 18.4 the function would return "Underweight", for all BMIs between 18.5 and 24.9, the function would return "Normal Weight", etc.


Further Hlookup Examples

For a practical example of the HLOOKUP function being used to create a variable drop-down list, see the Variable Drop-Down List page.

Also, there are further examples on the Microsoft Office website.


Excel Hlookup Function Errors

If you get an error from the Excel Hlookup function this is likely to be one of the following:

Common Errors
#N/A-Occurs if the Hlookup function fails to find a match to the supplied lookup_value
The cause of this will generally depend on the supplied [range_lookup]:
if [range_lookup] = TRUE
(or is omitted)
-the #N/A error is likely to be because the smallest value in the lookup row is greater than the supplied lookup_value.
if [range_lookup] = FALSE-

the #N/A error is likely to be because an exact match to the lookup_value is not found in the lookup row.

This error is described in more detail in the Common Hlookup Problem section below.
#REF!-Occurs if the supplied row_index_num argument is greater than the number of rows in the supplied table_array.
#VALUE!-

Occurs if either:

  • The supplied row_index_num argument is < 1 or is non-numeric
or
  • The supplied [range_lookup] argument is not recognised as TRUE or FALSE.


Also, the following problem is encountered by some users:

Common Hlookup Problem

Many users find that the Excel HLOOKUP function returns the #N/A error when performing an exact lookup (i.e. with [range_lookup] set to FALSE), even though they know that the lookup value is present in the table_array.

However, they cannot understand why the Hlookup function does not find the lookup_value in the table_array.

Step No. 1

Investigate this problem by checking for equality between the cells that you believe should match.

In Example 1 above, we expect the text "Ed" in cell A10 to be matched with the text "Ed" in cell E2 of the spreadsheet. Therefore, we need to test if Excel considers the contents of these two cells to be truly equal. We can do this by typing the following formula into any free Excel cell:

=A10=E2

This formula will evaluate to TRUE if Excel considers the contents of cells A10 and E2 to be truly equal. If the formula evaluates to FALSE, however, this tells you that the cause of your Hlookup error is that the contents of cells A10 and E2 are not truly equal.

Step No. 2

If Excel tells you that the cells that you expect to be matched are not truly equal, you need to find out why this is. The reason is likely to be one of the following:

Possible Reason No. 1

You may have unseen characters, such as spaces, at the start or end of either the value you are looking up, or in the cells of the table_array. These characters cause the lookup_value cell and the 'matching' cell in the table_array to have slightly different content.

Solution:

In this case, you need to click into each cell and remove any additional characters.

Possible Reason No. 2

The contents of the cells that are being compared may have different data types. For example, the cell containing the lookup_value may be stored as a number by Excel, whereas the values in the table_array may be stored as text (even though they may look like numbers).

Solution:

Force both sets of data to have the same type. For example, if you want both sets of values to be stored as text, convert both sets of data to text, using Excel's Text To Columns tool:

  1. Use the mouse to select the cells you want to convert to text (this must be done one column at a time);
  2. From the Data tab on the Excel ribbon, select the Text to Columns option;
  3. Make sure the Delimited option is selected and click next;
  4. Make sure all the delimiter options are unselected and then click next again;
  5. You should now be offered a selection of Column Data Formats. Select Text and click the Finish button.

The data in your selected cells should now be stored as text within Excel and so the Excel Hlookup function should be able to find the matching value.

Note that you could also have chosen to convert the contents of the cells to Excel's 'general' type, by simply selecting the column data format General in the Text To Columns tool.

Further details of this common problem are provided on the Failure to Match Values page.