HLOOKUP in Excel

Hlookup in excel is very similar to Vlookup in excel, Hlookup is a referencing worksheet function in excel which uses a reference and finds and matches the value from a row instead of a column, that is why the name was given to this formula as Hlookup which means horizontal lookup where we search for data horizontally in rows.

Formula

Explanation

There are four parameters are used in the HLOOKUP function in which three are compulsory and one is optional.

Compulsory Parameter:

Lookup_value: lookup_value represents the value which you want to search in table array, it can be a valuable reference and a string passed in the Hlookup formula.
Table_array: table_array is the raw data where you want to search for lookup value.
Row_index_num: Row_index_num is the number of rows in table array from which you need the required details for a particular lookup value.

Optional Parameter:

[Range_lookup]: [Range_looup] is an optional parameter where the value will be 0 or 1.

Here 0 stands for exact match value and 1 stands for approximate match value.

How to use HLOOKUP in excel? (with Examples)

This function is very simple and easy to use. Let’s understand the working of HLOOKUP with examples.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

You can download this HLOOKUP Excel Template here – HLOOKUP Excel Template

Example #1

Consider the below table as raw table and we need the specific data from the raw table.

Suppose you need a region-wise data of total sales, profit, and date like the show in the below table.

For Profit: =HLOOKUP($A10,$A$4:$E$7,2,0), output will be 575.

For Sales: =HLOOKUP($A10,$A$4:$E$7,3,0), output will be 120.

For Date: =HLOOKUP($A10,$A$4:$E$7,4,0), output will be 5/30/2018.

Example #2

Let’s pick another Hlookup example here to understand the exact match value and approximate match value in the HLOOKUP function in Excel.

Consider the below data as table_array or raw data.

And you need to find out the percentage of data 1^st Jan and 2^nd April.

For 1^st April: =HLOOKUP(A20,A15:E16,2,0) or =HLOOKUP(A20,A15:E16,2,False) here false for exact match value.

For 2^nd April: =HLOOKUP(A21,A15:E16,2,1) or =HLOOKUP(A20,A15:E16,2,True) here true is for approximate match value.

Things to Remember

HLOOKUP function in excel is a case-insensitive lookup. It means if you write “tanuj” and “Tanuj” it will treat it the same no effect on the output due to letter case.
There is a limitation in HLOOKUP that is the ‘Lookup_value’ should be the topmost row of the ‘table_array’. If you need to retrieve the data from something else row than other excel formula is used in place of HLOOKUP in excel.
HLOOKUP supports special characters such as “*” or “?” in the ‘lookup_value’ parameter only if ‘lookup_value’ is a text string.

Error in HLOOKUP Function

There are three kinds of error which can come in HLOOKUP function in excel due to wrong arguments.

#N/A error: HLOOKUP returns the #N/A error if ‘range_lookup’ is FALSE/0 sets to exact match and HLOOKUP function in excel is unable to find the ‘lookup_value’ in the given table_array.

#Value error: If the supplied ‘row_index_num’ < 1 and < the number of columns in table_array then HLOOKUP would return VALUE! error.

Ex.- =HLOOKUP(A20,A15:E16,0,0)

#REF! error:If the supplied ‘row_index_num’ < the number of columns in table_array then Hlookup in excel would return #REF! error.

Ex.- =HLOOKUP(A20,A15:E16,3,0)

The HLOOKUP function can only retrieve the first value matched value in the table_array that matches the lookup value.

If there are a few duplicate records in the table_array, then it will return first match value to avoid this situation you can remove the duplicates values from table_array then apply HLOOKUP in Excel.

If duplicate records should be kept in the dataset, create a PivotTable to group your data the way you want or you may use an array formula to extract all duplicate values in the lookup range

Hlookup in Excel