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.
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.
Here are seven practical examples of HLOOKUP functions that you can follow:
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.
Enter the formula and press Enter. It returns $2500, the sales figure for March.
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".
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.
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.
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.
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.
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.
