How do you use the HLOOKUP function in Google Sheets, and what are some best use cases?
The HLOOKUP function searches for data in the first row of a range and returns a specific nth cell in the column found. HLOOKUP is an excellent function to use in data sets where the primary search needs to be performed on data contained in the first row.
The HLOOKUP function contains three parameters. The first search_key is the value to be found in the first row of the range (second parameter), with the index (third parameter) returning the nth cell from the first row. The final parameter is_sorted is not a required field, but if the range is not sorted, set this parameter to FALSE, and it will search for an exact match.
HLOOKUP Example
A common use case I have found with this function is in the financial reporting domain when you are looking at financial reports containing the totals of product or service sales over time.
The sheet would contain in the first row specific periods, whether the months of the year or years themselves. And then the first column contains a row each for the products or services provided by a company.
Each cell would then contain the number of sales for the product or service for the specific time period above.
Looking something like this:
| A | B | C | |
|---|---|---|---|
| 1 | Products | 2021 | 2022 |
| 2 | X | 20,000 | 25,000 |
| 3 | Y | 10,100 | 10,000 |
| 4 | Z | 30,000 | 50,000 |
Sales report data over time for products X, Y and Z
Should certain managers only want to look at data for their product or services, then the HLOOKUP function can further refine this by reporting their relevant items.
For example, using the data above if somebody just wanted the result of the sales of Product X in 2022 the HLOOKUP function could work as follows:
This would return the result of 25,000. The HLOOKUP looks across first for an exact match of the value 2022 in the first row of the data set and then upon finding this in cell C1 would then count down two rows: 2022 being 1, and 25,000 being 2.
Here’s how this looks if you insert the HLOOKUP function in A5:
| A | B | C | |
|---|---|---|---|
| 1 | Products | 2021 | 2022 |
| 2 | X | 20,000 | 25,000 |
| 3 | Y | 10,100 | 10,000 |
| 4 | Z | 30,000 | 50,000 |
| 5 | 25,000 =HLOOKUP(2022,1:4,2,FALSE) |
Sales report data over time for products X, Y and Z
Lookup functions like HLOOKUP and VLOOKUP are great when the nth cell is a known and fixed quantity in the range, but what if the rows change?
Is there a way to make the HLOOKUP function a little more dynamically?
Dynamic HLOOKUP Function
To make the HLOOKUP function a little more dynamic by what value it returns, you can use the MATCH function to find the right index to return.
The MATCH function in Google Sheets finds the position of a value (first parameter) in a range of cells (second parameter) and, depending upon whether the data is_sorted (third parameter) it returns the corresponding cell reference.
To enable the HLOOKUP to be a little more dynamic the index parameter can be changed to contain the MATCH function where the MATCH function’s responsibility will be to search for the required value in the first column.
This would look something like so:
This value returns the same result as demonstrated below:
| A | B | C | |
|---|---|---|---|
| 1 | Products | 2021 | 2022 |
| 2 | X | 20,000 | 25,000 |
| 3 | Y | 10,100 | 10,000 |
| 4 | Z | 30,000 | 50,000 |
| 5 | 25,000 =HLOOKUP(2022,1:4,MATCH("X",A1:A4,FALSE),FALSE) |
Sales report data over time for products X, Y and Z
If Product X was moved from cell A2 and swapped with cell A4 in our data range, the dynamic formula would fetch the correct value from column C which matches the year 2022 from the HLOOKUP function’s first parameter, as demonstrated below:
| A | B | C | |
|---|---|---|---|
| 1 | Products | 2021 | 2022 |
| 2 | Z | 20,000 | 25,000 |
| 3 | Y | 10,100 | 10,000 |
| 4 | X | 30,000 | 50,000 |
| 5 | 50,000 =HLOOKUP(2022,1:4,MATCH("X",A1:A4,FALSE),FALSE) |
Sales report data over time for products X, Y and Z
Summary
The HLOOKUP function is a great way to lookup and returns the intersection of data from specific rows and columns in Google Sheets. By using the MATCH function as the third parameter, you can make the HLOOKUP function more dynamic and adaptable should the data range change.
Click here to get a free copy of the HLOOKUP Function Google Sheet