Microsoft Excel HLOOKUP Formulas: A Guide with Examples

Microsoft Excel's HLOOKUP function is a powerful tool for searching and retrieving data from a horizontal table. HLOOKUP allows you to search for a specific value in the topmost row of a table and retrieve a corresponding value from a different row. In this guide, we will explore the syntax of the HLOOKUP function and provide examples to demonstrate its application.

HLOOKUP Syntax

The syntax for the HLOOKUP function is as follows:

    =HLOOKUP(lookup_value, table_range, row_index, [range_lookup])
  

• lookup_value: The value you want to search for.
• table_range: The range of cells that contains the lookup table. This range must include the row you want to search and the row from which you want to retrieve the value.
• row_index: The row number (relative to the table_range) from which you want to retrieve the value. The topmost row is row 1.
• range_lookup: An optional argument that specifies whether the lookup should be approximate or exact. Enter FALSE for an exact match or TRUE (or omit) for an approximate match.

HLOOKUP Examples

Let's look at a few examples to understand how the HLOOKUP function works:

• To retrieve the price of a product based on its ID from a table in rows 1 and 2, you can use the following formula:
  =HLOOKUP(B2, A1:E2, 2, FALSE)
  This formula searches for the value in cell B2 in row 1 of the range A1 to E2, and returns the corresponding value from row 2 (the second row in the range).
• If you have a table with employee IDs and their corresponding salaries, and you want to retrieve the salary of an employee based on their ID, you can use the following formula:
  =HLOOKUP(D2, A1:E3, 3, FALSE)
  This formula searches for the value in cell D2 in row 1 of the range A1 to E3, and returns the corresponding value from row 3.
• In case the values in the topmost row of the table are not sorted in ascending order, you can use the approximate match option by omitting the range_lookup argument or setting it to TRUE. For example:
  =HLOOKUP(F2, A1:E4, 4)
  This formula searches for the value in cell F2 in row 1 of the range A1 to E4, and returns the closest match from row 4.

Conclusion

The HLOOKUP function in Microsoft Excel is a valuable tool for searching and retrieving data from a horizontal table. By understanding its syntax and using appropriate examples, you can leverage the power of HLOOKUP to quickly and efficiently extract relevant information from your data. Experiment with different scenarios and datasets to master the application of HLOOKUP in your Excel workflows.