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. In short, for horizontal lookup.
The alphabet H in HLOOKUP means horizontal, and V in VLOOKUP is vertical. In the below 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.
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.
[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 non-exact match use TRUE (Default).
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 it, I would love to hear from you.