HLOOKUP function

The HLOOKUP function in Excel is used to search for a value in the top row of a table or range and return a value in the same column from a row you specify. The “H” in HLOOKUP stands for “Horizontal,” indicating that it works across rows rather than down columns like VLOOKUP.

Syntax:

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

• lookup_value: The value to search for in the first row of the table or range.
• table_array: The range of cells containing the data. The first row of this range is where HLOOKUP searches for the lookup_value.
• row_index_num: The row number in the table_array from which to return the value. For example, if you want the value from the second row, this argument should be 2.
• [range_lookup] (optional): A logical value (TRUE or FALSE) that specifies whether to find an exact match or an approximate match.
  • TRUE (default): Finds an approximate match. If an exact match is not found, the function returns the closest value that is less than lookup_value.
  • FALSE: Finds an exact match. If no exact match is found, the function returns #N/A.

Example 1: Basic HLOOKUP

Suppose you have the following table in the range A1:E2:

You want to find the age of “Steve” using HLOOKUP. You would use:

=HLOOKUP("Steve", A1:E2, 2, FALSE)

In this example:

• "Steve" is the lookup_value you want to search for in the first row.
• A1:E2 is the table_array where the data is located.
• 2 is the row_index_num, meaning you want the value from the second row (the “Age” row).
• FALSE indicates that you want an exact match.

This will return:

40

Example 2: Approximate Match (Using TRUE)

Suppose you have a table of grades based on scores:

You want to find the grade for a score of 65. You would use:

=HLOOKUP(65, A1:D2, 2, TRUE)

In this example:

• 65 is the lookup_value you want to search for.
• A1:D2 is the table_array.
• 2 is the row_index_num, meaning you want the grade (from the second row).
• TRUE indicates that you want an approximate match.

Since 65 is between 60 and 70, the function will return the grade for the closest lower score (which is 60), so it will return:

D

Example 3: Using HLOOKUP for Exact Match

Suppose you want to find a grade for a score of 85 in the previous table, but this score doesn’t exist in the table. You would use:

=HLOOKUP(85, A1:D2, 2, FALSE)

This will return #N/A because 85 is not found in the table, and you’re asking for an exact match (FALSE).

Example 4: Using HLOOKUP for Multiple Columns

You can use HLOOKUP across multiple columns of data to look up and retrieve values. For instance, with a larger dataset, you could search across a broader range and retrieve data from the desired row.

Benefits of HLOOKUP:

• Simple Horizontal Search: Perfect for situations where your data is organized horizontally and you need to retrieve a value from a specific row.
• Flexible: The range_lookup argument allows you to search for either exact or approximate matches.
• Useful for Reports: It’s great for pulling specific data out of horizontal reports, tables, or summaries.

Limitations:

• Must Search the Top Row: HLOOKUP can only search the topmost row of the table. If your data is organized differently, you may need to transpose it or use a different function like INDEX or MATCH.
• Fixed Row Index: If the layout of the table changes (e.g., rows are added or removed), the row_index_num might need to be manually adjusted.

Use Cases:

• Data Retrieval: Retrieve a value from a horizontal table based on a specified condition (like a name or ID number).
• Dynamic Reports: Use HLOOKUP to pull specific values from a report or dataset that is organized horizontally.
• Lookup in Grade Tables: Great for lookup tables that are organized horizontally, such as score-to-grade mappings.

In summary, the HLOOKUP function is a powerful tool for searching horizontally in a table or range, making it useful when your data is organized by rows rather than columns.