The Excel HLOOKUP function searches a value in the top row of a table or array of values, and then returns a corresponding value in the same column from a specified row. The “H” in HLOOKUP stands for horizontal, because, unlike the VLOOKUP function, HLOOKUP moves horizontally to search for the lookup value.
HLOOKUP( lookup_value, table_array, row_index, [range_lookup], … )
lookup_value – The value to be found in the first row of the table
table_array – The table array in which the data will be looked up
row_index – The row number in the table array, from which the matching value will be returned
range_lookup – [Optional] A logical value specifying the search mode (exact or approximate match)
The matched value from the table array
How to use the HLOOKUP function in Excel
Use the Excel HLOOKUP function to search a value in the first row of a table and return a value from a specified row at the match column.
In the above example, a table of tax rates for multiple countries is provided as a lookup table. Since the lookup values (countries) are located horizontally, we can use the HLOOKUP function to automatically look up tax rates (in row 2) for a set of projects with the following formula:
If you do not specify the optional range_lookup argument, HLOOKUP will allow approximate match, but it will use an exact match if one is found.
Relevance of HLOOKUP in latest Excel versions
In Office 365 (now Microsoft 365), Microsoft introduced a revolutionary new function called XLOOKUP. This function covers the functionality of HLOOKUP and many other functions and technically makes HLOOKUP obsolete.
Nonetheless, keep in mind that not every one around you has the latest Excel version, so HLOOKUP might still be the better choice in case you share your worksheets with others.
The Excel VLOOKUP function searches for a value by matching on the first table column and returns the corresponding value from a column in the same row.
The Excel XLOOKUP function searches an array, and returns an item corresponding to the first match it finds. It replaces many older lookup functions.
The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.