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 |
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:
=HLOOKUP(2022, Data!1:4, 2, FALSE)
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)
|
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:
=HLOOKUP(2022, Data!1:4, MATCH("X",Data!A:A,FALSE),FALSE)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)
|
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)
|
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.
