How To Use HLOOKUP In Microsoft Excel?
Hope you enjoyed my previous article on 5 Craziest Microsoft Excel VLOOKUP Tricks You Must Know and found it useful. In this article, I will try to explain the HLOOKUP function in Microsoft Excel with a simple example.
“HLOOKUP (Horizontally lookup) function of Microsoft excel looks up a given value in the top row of a table and returns the corresponding value from another row. ”
The Parameters used in the HLOOKUP function are:
- lookup_value – The value which needs to be searched in the provided excel table (it should be from the first row of the supplied table or data array)
- table_array – The data array or table, containing the data to be searched in the top row, and the return values in any other row.
- row_index_num – The row number within the selected table range or table array from which the corresponding value will be returned as HLOOKUP output.
- [range_lookup] – An optional argument in the form of TRUE/FALSE
- Select TRUE in range_lookup parameter if you want search approximate value or closest match is HLOOKUP couldn’t find the exact match. Remember, if you are selecting [range_lookup] as true then the value in the first/top row of the table_array must be sorted in ascending order.
- Select FALSE if you are passing the value which has the exact match in the table_array and if the function cannot find an exact match to the supplied lookup_value, it will return an error.
“If [range_lookup] is TRUE or omitted, an approximate match is returned.”
Combining all together, we get complete HLOOKUP formula as below;
HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )
Let’s see a simple example explaining the HLOOKUP
Below table shows the salary structure of employees in horizontal format.
Now we want to know the basic salary of employees like below;
Below formula will help us in retrieving the desired output:
This formula will search the value present in cell “A11” in the table range A1:E5 and give the output from the second row if exact match found in the very first row of the table (in this case first column).
This is one way of using HLOOKUP, however, one can explore and use it as per individual requirements.
Common Errors in HLOOKUP :
- #N/A – It occurs if the Hlookup function fails to find a match to the supplied lookup_value in selected table_array
Possible reasons could be:
- You have selected [range_lookup] = TRUE or [range_lookup] = nothing and the smallest value in the lookup row is greater than the supplied lookup_value.
- You have selected [range_lookup] = FALSE but HLOOKUP didn’t find any exact match for the supplied lookup value within provided table_array.
- #REF! – Occurs if the supplied row_index_num argument is greater than the number of rows in the supplied table_array.
- #VALUE! -This error occurs if:
- The supplied row_index_num argument is < 1 or is non-numeric
- The supplied [range_lookup] argument is not recognized as TRUE or FALSE.
Passing wildcard characters in HLOOKUP as lookup_value:
If you have selected [range_lookup] as FALSE and your [lookup_value] is text, you can use the below wildcard characters.
- ? – Question mark– If you want to search a single character
- * – Asterisk mark – If you want to search a sequence of characters.
Note: If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Below screen demonstrates the uses of wildcard characters as lookup_value in HLOOKUP formula
You may also like to read my previous article – 7 Useful Microsoft Excel Tricks You Must Know
It will return the very first instance of the search result.
Hope you found this article useful, do share your excel tricks in the comment section of this post to help other readers.