How to use the HLOOKUP formula with an example

Excel’s HLOOKUP function requires you to look up a value in the highest row of the specified range (table array) and return the corresponding value from the specified row within that range. So let us have a look at the syntax and some examples including some additional test data.

The Syntax of the HLOOKUP Function is as follows:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: It is the value that you are looking for (numeric, text, or cell reference).
• table_array: This is the area that has tables that have more than one line where required values are sought (range, table, or named range).
• row_index_num: The matched row needs to be retrieved from.
• [range_lookup]: An optional argument (True or False). True for approximate match and False for exact match. If omitted True.

Example 1 – Retrieve Data for the Northern Region:

Consider a dataset with sales revenue, profits, and dates for different regions. We want to retrieve profit, sales revenue, and data for the Northern region.

Data:

Formulas

=HLOOKUP(“North”, $A$4:$E$7, 2, 0)

=HLOOKUP(“North”, $A$4:$E$7, 3, 0)

=HLOOKUP(“North”, $A$4:$E$7, 4, 0)

These formulas return the profit, sales revenue, and date for the Northern region.

Retrieve Percentages with Exact and Approximate Match:

Assume a table with dates and corresponding percentages. We want to retrieve the percentage for 1-Jan with an exact match and for 2-Apr with an approximate match.

Formulas

=HLOOKUP(“1-Jan”, A15:E16, 2, 0)

=HLOOKUP(“2-Apr”, A15:E16, 2, 1)

These formulas return the percentages for 1-Jan and 2-Apr respectively.

Errors and Key Points in HLOOKUP:

Errors Returned:

#N/A Error: It happens when an exact match is not found with range_lookup set to FALSE.

#VALUE! Error: Occurs if the row_index_num is less than 1.

#REF! Error: Comes because of giving row_index_num that is more than the total rows in the table array.

Key Points:

Case-insensitive: HLOOKUP treats uppercase and lowercase values the same.

Topmost Row: The lookup values for HLOOKUP should be in the topmost row of the table array.

Wildcard Characters: Support wildcards such as (*) and (?).

First Match: It returns the first match if multiple occurrences of lookup value are present.

Wildcard Characters with HLOOKUP:

The HLOOKUP function supports wildcard characters like asterisk (*) and question mark (?). This allows you to perform partial matches or pattern-based lookups. For example:

=HLOOKUP(“Nor*”, $A$4:$E$7, 2, FALSE)

This formula will match any text that starts with “Nor” in the topmost row.

Dealing with Duplicates:

When there are duplicate values in the top row of the table array, the HLOOKUP Function looks for the first match that occurs. For unintended results to never happen it is necessary to ensure your lookup values are unique. The MATCH function can be used more effectively together with HLOOKUP to handle duplicates.

Index-Match as another Option:

Although useful, HLOOKUP Excel can also be substituted with a combination of INDEX and MATCH functions which offer flexibility and control. This method enables you to do vertical and horizontal lookups at the same time.

As Example:

=INDEX($B$4:$E$7, MATCH(“North”, $A$4:$A$7, 0), MATCH(“Profit”, $B$3:$E$3, 0))

Sorting Data:

The top row of the table array which has data must be sorted in ascending order during approximate match (range_lookup = TRUE). Approximately matching HLOOKUP Excel depends on sorted data.

Dynamic Range Names:

Use named ranges for your table arrays. This will help you make your formulas more readable and manageable. To create a named range, select the range, right-click on it, and then click “Define Name.”

Error Handling:

Furthermore, the IFERROR function can be used to handle errors more gracefully than the aforementioned errors. For example;

=IFERROR(HLOOKUP(“2-Jan”, A15:E16, 2, FALSE), “Not Found”)

This formula will display “Not Found” if an error occurs.

Use Table References:

If your data set is established in an Excel Table, you can employ table references to refer to columns, e.g., TableName[ColumnName]. These references are auto-expanding with your data as it grows offering a dynamic solution.