HLOOKUP in Excel

HLOOKUP on Excel (Table of Contents)

• HLOOKUP Excel Function
• Formula of HLOOKUP
• How to Use HLOOKUP?

Hlookup in Excel

HLOOKUP Function is used for horizontally searching the value in the table array and provides the value from the same column based on the given index_number. In HLOOKUP the “H” represents the “Horizontal” as it will search the value moving horizontally to the right in the table_array.

Basically, HLOOKUP Excel function retrieves the value horizontally in the table data as similar to the Vlookup function used for vertical lookup the value in table_array.

Formula of HLOOKUP in Excel

Explanation of HLOOKUP Function in Excel

There are four parameters are used in HLOOKUP function in excel 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 value reference and a string passed in the Hlookup formula.
• Table_array: table_array is the raw data where you want to search 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?

HLOOKUP in Excel function is very simple and easy to use. Let’s understands the working of HLOOKUP with examples.

Example of HLOOKUP in Excel #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 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 of HLOOKUP in Excel #2

Let’s pick another Hlookup example here to understand the exact match value and approximate match value in 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 about the HLOOKUP Function

• HLOOKUP function in excel is a case-insensitive lookup. It means if you write “tanuj” and “Tanuj” it will treat it same no effect on the output due to letter case.
• There is a limitation in HLOOKUP in excel 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 in excel

There are three kinds of error which can come in 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 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 in Excel 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

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

Recommended Articles

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

• VLOOKUP with SUM in Excel
• EXACT Formula
• LOOKUP Excel Function
• Address Function in Excel
• IPMT Excel
• Column Function
• MIRR Function