Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
HLOOKUP on Excel (Table of Contents)
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.
- 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.
- [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 1st Jan and 2nd April.
For 1st April: =HLOOKUP(A20,A15:E16,2,0) or =HLOOKUP(A20,A15:E16,2,False) here false for exact match value.
For 2nd 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.
- #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.
- #REF! error:If the supplied ‘row_index_num’ < the number of columns in table_array then Hlookup in excel would return #REF! error.
- 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
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