How do you use the
HLOOKUP function in Google Sheets, and what are some best use cases?
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.
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.
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:
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
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
Lookup functions like
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.
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:
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:
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.