Excel - Microsoft Excel Hlookup 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

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 (eg. Biology) for one of the students (eg. Ed), this can be done using the Hlookup function, as shown in cell B10 of the spreadsheet. 

In the example below, the formula is shown in cell B10 of the spreadsheet on the left, and the result is shown in the spreadsheet on the right. 

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

This is illustrated 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 the individual spreadsheet from 'Ed' to 'Cara', the Hlookup functions would automatically recalculate the functions to display the exam results for Cara. 

Hlookup Example 2

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. 

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. 

Hlookup Function Common Errors

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

Common Errors 

Also, the following problem is encountered by some users: 

Common Hlookup Problem 

You can see the value that you want to look up in your table_array, but your Excel HLOOKUP is returning the #N/A error. Why can't it 'see' the lookup_value in the table_array? 

Possible Reason No. 1 

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

Solution No. 1 

Check the contents of the cells that you believe should match. If there are unseen additional spaces in one or both of the lists, remove these using the TRIM function: 

Possible Reason No. 2 

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

Solution No. 2 

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: 

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