How to Use Google Sheet: HLOOKUP, How It Works.

5 min readMar 11, 2023

Google Sheets is a powerful tool that is used by many people for various purposes, ranging from budgeting to data analysis.

One of the key features of Google Sheets is its ability to search for specific values in a range of cells and return corresponding values from the same row or column. This is done using functions like HLOOKUP, which is particularly useful for searching for values in the first row of a table.

In this article, we will explore how HLOOKUP works in Google Sheets, and provide some examples of how it can be used in practice.

What is HLOOKUP?

HLOOKUP also known as Horizontal LOOKUP is a function in Google Sheets that allows you to search for a specific value in the first row of a table, and return a corresponding value from the same column. The function takes four arguments: the search key, the range to search within, the index of the column to return a value from, and a boolean value indicating whether the range is sorted.

=HLOOKUP(search_key, range, index, [is_sorted])

The search key is the value that you want to search for in the first row of the range. The range is the range of cells that you want to search within, including the first row that contains the search key. The index is the number of the column within the range that you want to return a value from. Finally, the is_sorted argument is optional, and is used to indicate whether the range is sorted in ascending order.

How does HLOOKUP work?

When you use the HLOOKUP function in Google Sheets, it searches for the search key in the first row of the range that you specify. If it finds a match, it returns the corresponding value from the same column as the search key, using the index that you provide.

For example, let’s say that you have a table with product names in the first row and their prices in the second row, like this:

If you wanted to use HLOOKUP to find the third product, after the product Apple, we’ll use the formula below:

=LOOKUP("Apple", A2:B10, 3, FALSE)

This formula searches for the value “Apple” in the first row of the range A2:B10, and returns the corresponding value from the index (3).

Note that the is_sorted argument is set to FALSE, indicating that the range is not sorted. This is important, because HLOOKUP will only work correctly if the range is sorted in ascending order.

Examples of using HLOOKUP in practice

HLOOKUP can be used in a variety of contexts in Google Sheets. Here are a few examples:

Sales data analysis

Suppose that you have a table of sales data for different products, with the product names in the first row and the sales figures for each month in subsequent rows.

You could use HLOOKUP to find the sales figure for a specific product in a given month with the fomula below.

=HLOOKUP(D1, $B$1:$F$2, 2, FALSE)

Budgeting

If you have a budget spreadsheet with categories of expenses in the first row and the amounts spent each month in subsequent rows, you could use HLOOKUP to find the amount spent on a specific category in a given month.

=HLOOKUP("Food", $A$5:$E$6, 2, FALSE)

Another use is time tracking. If you have a time tracking spreadsheet with dates in the first row and hours worked for different projects in subsequent rows, you could use HLOOKUP to find the hours worked on a specific project on a given date.

Conclusion

In conclusion, HLOOKUP is a powerful function in Google Sheets that allows you to search for specific values in the first row of a table, and return corresponding values from the same column.

With its flexibility and ease of use, HLOOKUP is a must-have function for anyone working with large datasets in Google Sheets. By mastering this function, users can streamline their workflows, save time, and make more informed decisions based on accurate and relevant data.

If you’d like to streamline your accounting and invoicing activities, tryout PayTrack. We have built an addon for freelancers and small businesses. Also, we have released an middleware to integrate your checkout flow to PayTrack. This way, you don’t need to write extra codes as your receipts and invoices are generated from the checkout point, tracked until they’re paid and recorded automatically in our bookkeeping tool.

Please, send a message to peppubooks@gmail.com if you have any questions or need help using PayTrack.

We’re starting a discord community for users who love to perform bookkeeping in a spreadsheet, you can join.

Interested in more about GoogleSheet? Check our previous articles:

Would you like to follow our series on creating a database with Rust and GoogleSheets, checkout our first post here.