Have you ever wondered how Google Sheets can help you search for a specific value in a 2D array and return a corresponding value from a specified row? Look no further! The HLOOKUP function is here to save the day.
Imagine you have a spreadsheet with month names at the top and corresponding sales volumes below. What if you need to find a specific salesperson’s volume for a particular month? That’s where the HLOOKUP function comes in handy! By using the HLOOKUP function and specifying the key, you can easily retrieve the desired value.
But wait, the HLOOKUP function is not alone in the realm of lookup operations. There are other functions like XLOOKUP, LOOKUP, and even a combination of the INDEX and MATCH or XMATCH functions. Let’s focus on the HLOOKUP function for now.
HLOOKUP Function: Syntax and Arguments
The HLOOKUP function in Google Sheets follows a simple syntax: HLOOKUP(search_key, range, index, [is_sorted]). Here’s what each argument means:
- Search_key: The value you want to find in the first row of the data set. You can enter it directly or use a cell reference.
- Range: The range of cells to be considered for the search. It can be a reference to a range or a named range.
- Index: The row number in the range from which the matching value will be returned.
- Is_sorted: A logical value that determines whether the search is for an exact match or the nearest match of the search_key in the first row. It is an optional argument. If omitted, the formula will use TRUE for approximate match and FALSE for exact match.
When the is_sorted parameter is set to TRUE, the HLOOKUP function will return the nearest value if an exact match is not found in the first row. On the other hand, setting it to FALSE ensures an exact match.
Search_key Examples in HLOOKUP Function in Google Sheets
Let’s explore some examples of search keys to understand the versatility of the HLOOKUP function:
- Text: You can search for a specific text value in the first row of the data set.
- Date: Similarly, you can search for a particular date.
- Time: The HLOOKUP function can even handle time values.
- Number: And of course, you can search for a specific number.
You can also use cell references as search keys. Simply enter the key in a cell and refer to that cell in the HLOOKUP function.
Horizontal Lookup in an Unsorted Range
Searching for a key in an unsorted range? No problem! Let’s consider the following example:
- Search_key: “Yumbilla Falls”
- Range: A1:J4 (unsorted)
- Index: 4
- Is_sorted: FALSE
Using the formula =HLOOKUP("Yumbilla Falls",A1:J4,4,false), we can retrieve the country name corresponding to the waterfall “Yumbilla Falls” from the fourth row.
How can an HLOOKUP function in Google Sheets return multiple values?
Want to return multiple values using the HLOOKUP function? We’ve got you covered! By using one or more row indexes in the third parameter of the HLOOKUP function, you can enclose the desired row indexes in curly brackets. However, since the HLOOKUP function cannot return multiple values on its own, you’ll need to use the ARRAYFORMULA function to support it.
For example, let’s say we want to retrieve the locality and country for the waterfall named “Yumbilla Falls”. The formula =ARRAYFORMULA(HLOOKUP("Yumbilla Falls",A1:J4,{3,4},false)) will do the trick!
Horizontal Lookup in a Sorted Range
What if your data is sorted? Fear not! With the HLOOKUP function, you can still find the value you’re looking for. Consider the following scenario:
- Range B1:F1: Interview appointment times
- Range B2:F2: Candidate names
Let’s say you want to search for a specific time to find the assigned candidate’s name. For example, if the search key is 13:00, you can use the formula =HLOOKUP(D6,B1:F2,2,TRUE). If an exact match is not available, the formula will match the next smallest value that is less than or equal to the search key.
Wildcards in HLOOKUP Function in Google Sheets
Want to match patterns in your search key? The HLOOKUP function supports the use of wildcards! You can use an asterisk (*) to match any number of characters or a question mark (?) to match a single character. Don’t forget to set the is_sorted parameter to FALSE when using wildcards in the HLOOKUP function.
For instance, if you’re unsure whether your first row contains day abbreviations or full names, you can use the asterisk wildcard. The formula =HLOOKUP("Wed*",A2:G3,2,FALSE) will match any cell in the first row that starts with the letters “Wed”.
Similarly, you can use the question mark wildcard to match any cell that starts with a specific letter, followed by any single character, and then a specific number. For example, the formula =HLOOKUP("B?126",A7:C8,2,FALSE) will return the value 400.
How to Clean Up Error Values in HLOOKUP Function in Google Sheets
Encountering errors while using the HLOOKUP function? Here are a few common ones and how to address them:
- REF!: This error can occur if you specify an index number greater than the number of rows in your range or if you accidentally delete the range used in the HLOOKUP function.
- VALUE!: This error might occur when you use multiple search keys without using the ARRAYFORMULA function or when you specify 0 as the index.
- N/A: This error signifies that the search key is not found in the HLOOKUP evaluation.
To clean up error values, you can wrap the HLOOKUP function in the IFNA function. This function removes only #N/A errors, allowing you to troubleshoot other errors effectively.
Conclusion
Congratulations! You’ve mastered the power of the HLOOKUP function in Google Sheets. This step-by-step guide has equipped you with the knowledge to effortlessly search for values and retrieve corresponding data from a specified row.
If you have any questions or comments, feel free to reach out. And for further development in the HLOOKUP function, check out the resources below:
- Vlookup and Hlookup Combination in Google Sheets.
- Hlookup to Search Entire Table and Find the Header in Google Sheets.
- How to Do a Reverse Hlookup in Google Sheets.
- How to Use Multiple Conditions in Hlookup in Google Sheets.
- Move Single Column to Multiple Columns Using Hlookup in Google Sheets.
Don’t forget to visit Crawlan.com for more exciting articles and expert advice on Google Sheets and other topics. Let’s embrace the power of Google Sheets together!
