So we’ve covered how to execute a VLOOKUP function, but what about a HLOOKUP? HLOOKUP is the same function as VLOOKUP, only horizontal. Note, the letter in front of “LOOKUP” indicates whether the search will be a vertical or horizontal search.
With HLOOKUP’s we are working with rows instead of columns. So when would you use this function over a VLOOKUP? Follow along to find out.
The syntax of the function is: HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )
lookup_value - The value that you want to search for, in the first row of the supplied 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 supplied table_array, that you want the corresponding value to be returned from.
[range_lookup] - An optional logical argument, which can be set to TRUE or FALSE, meaning:
TRUE - if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value. Note: If [range_lookup] is set to TRUE, the top row of the table_array must be in ascending order).
FALSE - if the function cannot find an exact match to the supplied lookup_value, it should return an error.
Now, lets put this into practice.
I have a spreadsheet that shows what each person spent on 3 specific categories. I want to pull the Entertainment category for each person and list those out with my HLOOKUP function:
And with the HLOOKUP formulas revealed (=HLOOKUP(A10,A2:E5,4,FALSE):
In the above example, the HLOOKUP function searches through the top row of the table_array (the range A2-E5), to find a match for the lookup_value (the name in A10-A13). When the the name is found, the function returns the corresponding value from the 4th row of the table_array. We added FALSE so the name matching has to be exact.
As always, feel free to reach out with any questions!
Comments will be approved before showing up.