Excel HLOOKUP function

How to use the Excel HLOOKUP function

Description

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. 

Syntax

HLOOKUP( lookup_value, table_array, row_index, [range_lookup], … )

Arguments

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)

Return value

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:

= HLOOKUP(“US”, “$C$5:$G$6”, 2, FALSE)

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.

Formula examples

Related Tutorials

Related Functions

How to use the Excel VLOOKUP function

Excel VLOOKUP function

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.

How to use the Excel XLOOKUP function

Excel XLOOKUP function

The Excel XLOOKUP function searches an array, and returns an item corresponding to the first match it finds. It replaces many older lookup functions.

How to use the Excel FILTER function

Excel FILTER function

The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email