In this article:

HLOOKUP in Google Sheets: How to Use it in 2024

hlookup google sheets
Source:zebrabi.com

What is HLOOKUP in Google Sheets?

HLOOKUP is a function in Google Sheets used for horizontal lookup. It searches for a specified value in the top row of a table or range and returns a value in the same column from a row you specify.

Example: If you have a table where each column represents a different month and the top row contains the names of the months, you can use HLOOKUP to find specific data for a given month.

HLOOKUP SYNTAX

The basic syntax of HLOOKUP is as follows:

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

search_key: The value to search for. For instance, this could be a specific date, a name, or some other identifier.

range: The range to consider for the search. The first row in the range is searched for the key.

index: The row index of the value to be returned, where the first row in range is 1.

is_sorted: An optional argument which indicates whether the range is sorted. If TRUE, HLOOKUP will return the closest match for search_key if it does not find an exact match. If FALSE, HLOOKUP will only find an exact match.

7 HLOOKUP Function Examples

Here are seven practical examples of HLOOKUP functions that you can follow:

1. Finding Sales Data for a Specific Month

Scenario: You have a sales data table where each column represents a different month, and you want to find the sales figure for a specific month.

Data:

A1:G1: Jan, Feb, Mar, Apr, May, Jun

A2:G2: $2000, $3000, $2500, $3200, $2800, $2700

Formula: =HLOOKUP("Mar", A1:G2, 2, FALSE)

To find the sales figure for March, place the cursor where you want the result.

google sheets hlookup

Enter the formula and press Enter. It returns $2500, the sales figure for March.

2. Matching Employee IDs with Names

Scenario: You have a list of employee IDs in the first row and corresponding names in the second row. You need to find the name associated with a specific ID.

Data:

A1:E1: 101, 102, 103, 104, 105

A2:E2: John, Emma, Mike, Sarah, Dave

Formula: =HLOOKUP(104, A1:E2, 2, FALSE)

To find the name of the employee with ID 104, select the cell for the result. Enter the formula and press Enter.

The formula returns "Sarah".

3. Extracting Product Prices

Scenario: Your spreadsheet contains product names in the top row and their prices in the second row. You want to find the price of a specific product.

Data:

A1:D1: Laptop, Camera, Smartphone, Headphones

A2:D2: $800, $500, $300, $150

Formula: =HLOOKUP("Camera", A1:D2, 2, FALSE)

To find the price of a "Camera", select the desired output cell.

Enter the formula and press Enter. It shows $500, the price of the Camera.

4. Using HLOOKUP with Approximate Match

Scenario: You have a grading scale where each column represents the lower bound of a grade range, and you need to find the grade corresponding to a specific score.

Data:

A1:E1: 0, 60, 70, 80, 90

A2:E2: F, D, C, B, A

Formula: =HLOOKUP(85, A1:E2, 2, TRUE)

To find the grade for a score of 85, select where you want the result. Enter the formula and press Enter.

The formula correctly returns "B" for a score of 85.

5. Combining HLOOKUP with Other Functions

Scenario: You want to use HLOOKUP to find a value and then perform a calculation with that value.

Data:

A1:D1: Apples, Oranges, Bananas, Grapes

A2:D2: 3, 2, 1.5, 4

Formula: =HLOOKUP("Bananas", A1:D2, 2, FALSE) * 10

To find the price of "Bananas" and calculate the cost for 10 bananas, go to the desired cell. Enter the formula and press Enter.

The formula calculates the total cost for 10 bananas.

6. HLOOKUP with Dynamic References

Scenario: You have a dataset where you want to look up values dynamically using cell references.

Data:

A1:D1: Red, Blue, Green, Yellow

A2:D2: 10, 20, 30, 40

Formula: =HLOOKUP(F1, A1:D2, 2, FALSE)

Put the color you want to search for in cell F1 (e.g., "Green"). In another cell, enter the formula and press Enter.

It returns 30 if "Green" is in F1.

7. Error Handling in HLOOKUP

Scenario: You want to use HLOOKUP but also handle cases where the lookup value is not found.

Data: Same as in previous examples.

Formula: =IFERROR(HLOOKUP("Purple", A1:D2, 2, FALSE), "Not Found")

Assuming you're looking for a value that might not exist (e.g., "Purple"), select the output cell. Enter the formula and press Enter.

It returns "Not Found" if the color is not in the dataset.

We hope that you now have a better understanding of what HLOOKUP in Google Sheets is and how to use Google Sheets HLOOKUP.

You might also like our articles on how to use VLOOKUP in Google Sheets and all about the Google Sheets Not Equal symbol.

To optimize your workflow, we recommend reading our guide on how to use data validation for email addresses in Google Sheets and trying our software for contract reminders.

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!