HLOOKUP function

The HLOOKUP function in Excel is used to search for a value in the header row of a table and return a corresponding value from the row below. It’s like looking up a word in a dictionary and finding its definition.

Syntax

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

Arguments

Here are the arguments you can use with the HLOOKUP function:

lookup_value	The value you want to find in the header row of the table.
table_array	The range that contains the data you want to search in, including the header row.
row_index_num	The row number from which you want to return the data (counting from the top row of the table as 1).
[range_lookup]	(Optional) A logical value that indicates whether you want an exact or approximate match. If TRUE or omitted, it looks for an approximate match; if FALSE, it looks for an exact match.

How to Use

Let’s understand how to use the HLOOKUP function with some examples:

Example 1:

Suppose you have a table of student grades, and you want to find the grade for a student named “Alice.” You can use the HLOOKUP function like this:

This formula searches for “Alice” in the header row (A1 to E1), and when it finds her, it returns her grade from row 2 (A2 to E2).

Example 2:

If you want to find an approximate match, say you want to find the grade of a student whose name is alphabetically between “Alex” and “Bob,” you can use a range_lookup of TRUE or omit it, like this:

The function will find the closest match and return the corresponding grade.

Example 3:

If the header row is not sorted in ascending order, you can still use HLOOKUP to find a value. It will return the first match it finds in the header row.

Even if the names are not in order, it will find “Alice” in the header row and return her grade.

Example 4:

Remember that HLOOKUP searches only in the header row and returns a value from the specified row. If it doesn’t find the value, it returns an error (#N/A). So, make sure your lookup value is present in the header row.

If “David” is not in the header row, this formula will result in an error.

Additional Information

It’s essential to understand the use of HLOOKUP when dealing with data organized in tables with headers, as it helps you quickly find and retrieve specific information based on a lookup value.