HLOOKUP in Excel

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 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 the 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.

Example #1

Consider the below table as a raw table, and we need 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 with no effect on the output due to letter case.
• There is a limitation in HLOOKUP is that the ‘Lookup_value’ should be the topmost row of the ‘table_array.’ If you need to retrieve the data from something else row, then 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

Three kinds of error can come in the HLOOKUP function in excel due to wrong arguments. 

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

2. #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)

3. #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)

4. 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 the 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 Function Video

Recommended Articles

This has been a guide to HLOOKUP in Excel. Here we discuss its Formula and how to use the HLOOKUP function along with practical examples and downloadable excel templates. You may also look at these useful functions in excel

• VLOOKUP with Two Criteria
• HLOOKUP Examples in Excel
• VLOOKUP Errors in Excel
• Power BI Vlookup
• Price Function in Excel