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.