What is EXCEL HLOOKUP FUNCTION
The Excel HLOOKUP Function is listed under Microsoft Excel's Lookup Functions category. It lookups for a value in the top row of a table and returns the value from the same column of the matched value using the index number. In simple words, it performs a horizontal lookup.
How to use it
To learn how to use the HLOOKUP function in Excel, you need to understand its syntax and arguments:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: The value you want to lookup.
- table_array: The data table or an array from which you want to the lookup value.
- row_index_num: A numeric value representing a number of rows below from the top row from which you want the value. For example, if you specify 2 and your lookup value is in A10 in the data table, it will return value from cell B10.
- [range_lookup]: A logical value to specify the type of lookup. If you want to perform an exact match search use FALSE and if you want to perform a non-exact match use TRUE (Default).
- You can use wildcard characters.
- You can perform an exact match and an approximate match.
- While performing an approximate match make sure to sort data in ascending order from left to right, and if data is not in ascending order then it would return an inaccurate result.
- If range_lookup is true or omitted, it will perform a non-exact match but return an exact match if the lookup value exists in the lookup range.
- If range_lookup is true or omitted, and lookup value is not in the lookup range, it will return the nearest value which is less than the lookup value.
- If range_lookup is false, then there is no need to sort data range.
To master HLOOKUP function we need to try it out in an example and below is one which you can try out:
In the below example, we have used HLOOKUP function with MATCH to create a dynamic formula and then we have used a drop-down list to change the lookup value from the cell.
The zone name from cell C7 is used as a lookup value.
Range B1: F5 as table array and for row_index_num we have used match function to get the row number.
Whenever you change the value in cell C9, it will return the row number from the table array.
You don’t have to change your formula again and again. Just change values with the drop-down list and you will get value for that.
This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:
- EXCEL ADDRESS Function
- EXCEL AREAS Function
- EXCEL CHOOSE Function
- EXCEL COLUMN Function
- EXCEL COLUMNS Function
- EXCEL FORMULATEXT Function
- EXCEL HYPERLINK Function
- EXCEL INDEX Function
- EXCEL INDIRECT Function
- EXCEL LOOKUP Function
- EXCEL MATCH Function
- EXCEL OFFSET Function
- EXCEL ROW Function
- EXCEL ROWS Function
- EXCEL TRANSPOSE Function
- EXCEL VLOOKUP Function
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.