The Excel HLOOKUP Function (2024)

Home »Excel-Built-In-Functions »Excel-Lookup-And-Reference-Functions »Excel-Hlookup-Function

Function 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:

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 canbe done using the Hlookup function, as shown in cell B10 of the spreadsheet.

Formulas:

Results:

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

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

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

Note that although the top row of the table_arrayis in ascending order in this example, this is not essential, because the[range_lookup] argument is set to FALSE (requiringan exact match).

Hlookup Example 2 - Closest Match Lookup

Cells A1-F3 of the following spreadsheet show body types relating to body mass index (BMI), for theranges 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 functionthat is used to look up the body type that relates to this BMI.

The Hlookup function in the above spreadsheet returns the result "Normal Weight", which is the correctbody 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 suppliedlookup_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.

Note also that, in this case, as the [range_lookup] argument is setto TRUE, the top row of the table_array (i.e. cells A2-E2) mustbe in ascending order.

Further Hlookup Examples

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

Also, for further information on the Hlookup function, see theMicrosoft 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: