This guide covers everything you need to know about the Google Sheets HLOOKUP function, including its definition, syntax, use cases, and how to use it.
What is the HLOOKUP Function? How Does It Work?
The HLOOKUP function in Google Sheets is a powerful feature that allows you to perform a horizontal lookup. It searches across the first row of a specified range for a certain key and returns the value of a specified cell in the column where it found the key.
The main purpose of the HLOOKUP function is to streamline data retrieval in large datasets. This function essentially eliminates the need to manually search for specific values across rows, making it a tremendous time-saver. It’s particularly useful when working with large spreadsheets where the information you seek is arranged horizontally.
How does it work? Let’s break down the HLOOKUP process.
First, the function scans the first row within your defined range for the “search_key” value you’re looking for. This could be any data type, such as a number, text, or cell reference.
Next, once the function locates the search_key value in the first row, it moves downward vertically to a specific row (determined by the “index” value you specify) in the same column where it found the search_key. The function then returns the value in this cell.
The HLOOKUP function, by default, assumes that the first row of your range is sorted in ascending order. If this is not the case, you must set the optional [is_sorted] argument to FALSE.
It’s worth noting that the HLOOKUP function doesn’t support search keys based on regular expressions or wildcard patterns. For such cases, the QUERY function may be more appropriate. Furthermore, it’s recommended to use sorted ranges with is_sorted set to TRUE for optimal performance whenever possible.
HLOOKUP Syntax
The syntax and arguments for the function are as follows:
HLOOKUP(search_key, range, index, [is_sorted])
- search_key: This is the value you want the function to search for. It can be a numeric value, a string, or a cell reference. For instance, you could use a number like 42, a string like “Cats”, or a cell reference like I24.
- range: This is the range within which the function will search for the search_key. The function will look for the search_key in the first row of this range.
- index: This is the row number within the range from which the function will return a value. The first row in the range is considered row 1.
- is_sorted: This is an optional argument that defaults to TRUE if not specified. It indicates whether the first row of the range is sorted.
Here are some important usage notes related to the syntax and arguments of the HLOOKUP function:
- If you’re searching for a numeric or date value, ensure the first row of your range isn’t sorted by text values. For example, if you’re sorting numbers, they should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would if they were sorted as strings. Using an incorrect sort type may cause the function to return incorrect values.
- The HLOOKUP function does not support search keys based on regular expressions or wildcard patterns. If you need to use these types of search keys, you should use the QUERY function instead.
Examples of How to Use the HLOOKUP Function
Here are some practical examples of how the HLOOKUP function can be used in Google Sheets:
Example #1: Looking Up Prices
Let’s say you have a spreadsheet where the first row contains various product names and the rows underneath contain their corresponding prices. You want to find out the price of a specific product. The HLOOKUP function can be used to achieve this.
Assume that the product names are in cells A1 to E1 and their corresponding prices are in cells A2 to E2. If you want to find out the price of the product in cell B1, you can use the following formula:
=HLOOKUP(B1, A1:E2, 2, FALSE)
This formula will return the price of the product in cell B1.
Example #2: Employee Details Lookup
Let’s say you have a spreadsheet that contains employee details. The first row contains employee IDs and the rows underneath contain their corresponding names, departments, and salaries. You want to find out the department of a specific employee using his/her ID. The HLOOKUP function can be used for this.
Assume that the employee IDs are in cells A1 to E1, the names are in cells A2 to E2, the departments are in cells A3 to E3, and the salaries are in cells A4 to E4. If you want to find out the department of the employee with the ID in cell C1, you can use the following formula:
=HLOOKUP(C1, A1:E4, 3, FALSE)
This formula will return the department of the employee with the ID in cell C1.
Example #3: Student Grades Lookup
Suppose you have a spreadsheet that contains student IDs in the first row and their corresponding grades in the rows underneath. You want to find out the grade of a specific student using his/her ID. The HLOOKUP function can be used for this.
Assume that the student IDs are in cells A1 to E1, and their corresponding grades are in cells A2 to E2. If you want to find out the grade of the student with the ID in cell D1, you can use the following formula:
=HLOOKUP(D1, A1:E2, 2, FALSE)
This formula will return the grade of the student with the ID in cell D1.
HLOOKUP: Common Mistakes & Problems
When using the HLOOKUP function in Google Sheets, users often make a few common mistakes. Understanding these potential pitfalls can help you avoid them and use the function more effectively:
- Incorrect Reference Row: When defining the row_index in the HLOOKUP function, ensure it’s correctly set. If the row_index is set to 1, the function will return data from the first row of the range, not necessarily the row where the lookup value is found. The row_index should correspond to the number of rows down from the top of the range where the return value is located.
- Not Using Exact Match: When the range_lookup argument is set to TRUE or is omitted, Google Sheets assumes that the first row of the table is sorted in ascending order. If this is not the case, the HLOOKUP function may return incorrect results. To avoid this, if the first row is not sorted, set the range_lookup argument to FALSE to force an exact match.
- Lookup Value Does Not Exist: If the lookup value does not exist in the first row of the table array, the HLOOKUP function will return an #N/A error. Ensure the lookup value exists in the first row of your data range.
- Using Non-Numeric Values in Numeric Columns: If you’re searching in a numeric column, ensure your lookup value is also a number. If the lookup value is text and the column contains numbers, the HLOOKUP function cannot find a match and will return an #N/A error.
- Merged Cells: If you have merged cells in your data range, the HLOOKUP function may not work properly. It’s best to avoid using merged cells when planning to use HLOOKUP.
- Incorrect Range Specification: When specifying the range for HLOOKUP, ensure that it includes the row that contains the lookup value and the row that contains the return value. The function will not work correctly if these rows are not included in the specified range.
- Using Array Formulas: HLOOKUP can return unexpected results with array formulas. If you’re using array formulas, the INDEX and MATCH functions are a better fit for the task at hand.
Why Is HLOOKUP Not Working? Troubleshooting Common Errors
If you’re using the HLOOKUP function in Google Sheets and it’s not working as expected, there could be several reasons causing this. Understanding these common errors, their causes, and solutions can help you troubleshoot and fix them.
#VALUE! Error
Cause: The #VALUE! error in HLOOKUP typically occurs when the ‘search_key’ doesn’t match the data type in ‘range’. For instance, if you’re trying to look for a text string in a range of numbers, you’ll get this error.
Solution: Make sure the ‘search_key’ matches the data type in the ‘range’. If you’re looking for a number, ensure your ‘search_key’ is also a number. Similarly, your ‘search_key’ should be a text string if you’re looking for text.
#N/A Error
Cause: The #N/A error usually happens when the ‘search_key’ cannot be found within the ‘range’. This means the value you’re trying to find does not exist in the specified range.
Solution: Double-check the ‘search_key’ and the ‘range’ to ensure the value you’re looking for exists in the range. If the value does not exist, you may need to update your ‘search_key’ or ‘range’.
#REF! Error
Cause: A #REF! error is typically caused by a reference to a cell that is not valid in the HLOOKUP function. For instance, if you delete a row or column that is referenced in your HLOOKUP formula, you will see this error.
Solution: To fix a #REF! error, you need to adjust your HLOOKUP function to refer to valid cells. Check your formula to ensure that all referenced cells exist and have not been deleted.
#NUM! Error
Cause: The #NUM! error often appears when the ‘range’ parameter in the HLOOKUP function is set to FALSE, and the first row of the ‘range’ is not sorted in ascending order.
Solution: If the ‘range’ parameter is set to FALSE, ensure that the first row of your ‘range’ is sorted in ascending order. If it’s not possible to sort the data, change the ‘range’ parameter to TRUE.
Using HLOOKUP With Other Google Sheets Functions
Combining HLOOKUP with other Google Sheets functions can further enhance its capabilities, allowing you to perform more complex searches and data manipulation. Here are some examples of how you can use HLOOKUP with other functions:
With IFERROR
Usage: The IFERROR function returns an alternate value or performs a different action in case the primary function returns an error. When used with HLOOKUP, it can prevent your sheet from displaying error values when the HLOOKUP function doesn’t find the lookup value.
Example: Let’s say you have a dataset in range A1:F10, and you want to look up a value in the first row and return the corresponding value from the 5th row. Here’s how you can use IFERROR with HLOOKUP:
=IFERROR(HLOOKUP(“LookupValue”, A1:F10, 5, FALSE), “Not Found”)
This formula will return “Not Found” if the “LookupValue” is not found instead of displaying an error.
With ISNA
Usage: The ISNA function is used to check if a value is #N/A error, which commonly occurs when a lookup function like HLOOKUP can’t find the lookup value. By using ISNA with HLOOKUP, you can control what happens when the lookup value is not found in the range.
Example: Using the same dataset as above, here’s how you can use ISNA with HLOOKUP:
=IF(ISNA(HLOOKUP(“LookupValue”, A1:F10, 5, FALSE)), “Not Found”, HLOOKUP(“LookupValue”, A1:F10, 5, FALSE))
This formula will return “Not Found” if the “LookupValue” is not found in the range. Otherwise, it will return the corresponding value from the 5th row.
With INDEX and MATCH
Usage: While HLOOKUP is a powerful function on its own, its capabilities can be further enhanced when used with INDEX and MATCH functions. This combination can be used when you are not certain about the row index number from which the match should be returned.
Example: Let’s say you have a dataset in A1:F10 and want to look up a value in the first row and return the corresponding value from a row where another specific value is found. Here’s how you can do it:
=INDEX(A1:F10, MATCH(“SpecificValue”, A1:A10, 0), HLOOKUP(“LookupValue”, A1:F10, MATCH(“SpecificValue”, A1:A10, 0), FALSE))
This formula will look for the “LookupValue” in the first row and return the corresponding value from the row where “SpecificValue” is found.
For more details on the HLOOKUP function, check out the official documentation at the Google Docs Editors Help Center.
