Use HLOOKUP for horizontal lookup.
HLOOKUP is VLOOKUP‘s sibling.
You can use HLOOKUP to lookup a value from a top row of the data table, and if that value found, you can retrieve a value which is a number of cells below in the same column.
The alphabet H in HLOOKUP means horizontal, and V in VLOOKUP is vertical.
In above table, we have months name in the first row and product name in the first column.
Let’s say if you want to lookup for “Jun” month from the first row and want quantity value of “Product-5” then you can use HLOOKUP for that.
And, another condition is, If you want to lookup for “Product 4” and want quantity value of “Apr” month then you can use VLOOKUP for that.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value A value you want to lookup.
table_array A data table or an array from which you want to lookup value.
row_index_num A numeric value represent 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.
More Information on Excel HLOOKUP Function
- You can use wildcard characters with HLOOKUP.
- You can perform an exact match or 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 lookup value exists in 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 lookup value.
- If range_lookup is false, then there is no need to sort data range.
I have used below formula to perform a dynamic HLOOKUP formula.
How does this work?
I have used zone name from cell C7 as a lookup value. Range B1: F5 as table array. And, for row_index_num I have used match function to get the row number.
Whenever I change the value in cell C9, it will return the row number from 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.
To learn more about Excel HLOOKUP Function you can check Microsoft’s Help Section. And, if you have a unique idea to use the vlookup function, I would love to hear from you.