How to hlookup in excel

Overview of HLOOKUP in Excel

The HLOOKUP function in Microsoft Excel is a powerful tool for searching for information horizontally within a dataset. Unlike its more commonly known counterpart, VLOOKUP, which searches vertically, HLOOKUP is specifically designed to find a value in the first row of a table or array and then return a value from a different row in the same column. This makes it incredibly useful when your data is organized with headers or comparison points running across the top rather than down the side.

Imagine you have a table where different months are listed in the first row, and subsequent rows contain sales figures, expenses, or other metrics for each month. If you want to quickly find the sales figure for, say, 'March', you would use HLOOKUP to scan the first row for 'March' and then pull the corresponding sales figure from the correct row below it.

Understanding the HLOOKUP Syntax

The basic syntax for the HLOOKUP function is as follows:

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

• lookup_value: This is the value you want to search for in the first row of your table. It can be a number, text, or a logical value.
• table_array: This is the range of cells that contains the data you want to search within. The first row of this range is where HLOOKUP will look for your lookup_value.
• row_index_num: This is a number that indicates which row in the table_array contains the value you want to return. The first row of the table_array is row 1, the second row is row 2, and so on.
• [range_lookup]: This is an optional argument that specifies whether you want an exact match or an approximate match.
• TRUE (or omitted): Finds an approximate match. If an exact match is not found, it returns the next largest value that is less than or equal to the lookup_value. For this to work correctly, the first row of your table_array must be sorted in ascending order.
• FALSE: Finds an exact match. If an exact match is not found, it returns the #N/A error.

How to Use HLOOKUP: Step-by-Step Examples

Example 1: Finding Exact Sales Data

Let's say you have the following data in cells A1:D3:

If you want to find the 'Sales' figure for 'Mar', you would enter the following formula in any empty cell:

=HLOOKUP("Mar", A1:D3, 2, FALSE)

Here:

• "Mar" is the lookup_value.
• A1:D3 is the table_array.
• 2 is the row_index_num because 'Sales' is in the second row of the table.
• FALSE ensures we are looking for an exact match for 'Mar'.

This formula would return 2200.

Example 2: Using a Cell Reference for Lookup Value

Instead of typing 'Mar' directly, you can reference a cell. Let's say cell F1 contains the text 'Mar'. The formula becomes:

=HLOOKUP(F1, A1:D3, 2, FALSE)

This will also return 2200.

Example 3: Approximate Match for Ranges

Consider data where the first row represents score ranges and the second row represents the corresponding grade:

If you want to find the grade for a score of 75, you might think HLOOKUP can do this. However, HLOOKUP works best when the lookup_value is in the lookup_value column/row. For this scenario, VLOOKUP or XLOOKUP are typically better suited. But if we adapt the data to make HLOOKUP work:

To find the grade for a score of 75, using the second table format:

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

Here:

• 75 is the lookup_value.
• A1:D2 is the table_array.
• 2 is the row_index_num.
• TRUE indicates an approximate match. Excel looks for the largest value in the first row that is less than or equal to 75. In this case, it finds 60.

This formula would return C.

When to Use HLOOKUP vs. VLOOKUP

The choice between HLOOKUP and VLOOKUP depends entirely on the orientation of your data:

• Use HLOOKUP when the values you are searching for (your lookup values) are located in the top row of your data range, and you want to retrieve data from a row below it.
• Use VLOOKUP when the values you are searching for are located in the first column of your data range, and you want to retrieve data from a column to its right.

Limitations and Alternatives

While HLOOKUP is useful, it has limitations:

• It can only search in the top row.
• It cannot easily look up values to the left of the lookup column (which is inherent to its horizontal nature).
• Inserting or deleting columns within the table_array can break the formula if the row_index_num is not adjusted correctly.

For these reasons, Microsoft has introduced the XLOOKUP function in newer versions of Excel (Microsoft 365, Excel 2021). XLOOKUP is more versatile, capable of looking up in any direction (left, right, up, or down), and offers more robust error handling and features. It is generally recommended to use XLOOKUP if your version of Excel supports it.

Conclusion

HLOOKUP remains a valuable function for performing horizontal lookups in Excel, especially when dealing with older versions or specific data structures. By understanding its syntax and how to apply it with both exact and approximate matches, you can efficiently retrieve the information you need from your spreadsheets.