How to Use the HLOOKUP Function in Excel
Today, let’s delve into the functionality of a potent tool available in both Microsoft Excel and Google Sheets: the HLOOKUP function. HLOOKUP stands for “Horizontal Lookup” and is specifically crafted to search for a value in the first row of a table or an array, and then return a matching value from the same column out of a row you specify. This function proves invaluable when you need to rapidly retrieve data from horizontal rows or across multiple rows in a table.
Basic Syntax and Arguments
The syntax for the HLOOKUP function is:
=HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])- lookup_value: The value you’re searching for in the first row of the table.
- table_array: The range of cells containing the data under consideration. It’s important that the first row of this range includes the values to be compared with lookup_value.
- row_index_number: Indicates the row number from which the value should be returned. The numbering starts at 1 for the first row, then 2 for the second, and so on.
- range_lookup: [Optional] A boolean that decides whether to find an exact match (FALSE) or an approximate match (if TRUE, or omitted).
Examples and Use Cases
Example 1: Basic HLOOKUP
Consider the following table in Excel:
| Apples | Oranges | Bananas |
|---|---|---|
| 5 | 8 | 3 |
To determine the number of oranges, utilize the HLOOKUP function as shown:
=HLOOKUP("Oranges", A1:C2, 2, FALSE)This formula will output the value 8, indicating there are 8 oranges.
Example 2: Using HLOOKUP with Dynamic Data
Imagine a table that lists sales data over different months:
| Jan | Feb | Mar | |
|---|---|---|---|
| Product A | 100 | 150 | 200 |
| Product B | 75 | 100 | 125 |
To retrieve the February sales figure for Product B, apply the HLOOKUP function with cell references like so:
=HLOOKUP("Feb", B1:D3, MATCH("Product B", A1:A3, 0), FALSE)This formula will return the figure 100, representing February sales for Product B.
These scenarios exemplify a fraction of the potential applications of the HLOOKUP function to effectively access data from tables in Excel and Google Sheets. With a grasp of the basic syntax and some practice with diverse use cases, you can fully harness the capabilities of this flexible function to enhance your spreadsheet operations.
More information: https://support.microsoft.com/en-us/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f