Hello, in this article we will learn how to use the HLOOKUP Function in Google Sheets.
What is HLOOKUP? Many of you have already heard of and/or used the related term VLOOKUP in Google Sheets. Is it comparable to VLOOKUP? Yes, you may consider it a sibling of the VLOOKUP, HLOOKUP, and VLOOKUP since they are all members of the LOOKUP family and are designed to help you find a certain island’s coordinates. HLOOKUP stands for horizontal lookup, which means that it searches for values in a horizontal order, whereas VLOOKUP stands for vertical lookup, which searches for values in a vertical order.
Now, VLOOKUP is without a doubt the most popular and widely used function in Google Sheets. The cause is Google Sheets’ default format. We hardly ever see data in horizontal order in Google Sheets, but it does exist, so we need to be aware of it in order to work on it. Google Sheets data is by default set to be vertical. We utilize HLOOKUP rather than VLOOKUP to carry out the identical search operation on data that has been written horizontally.
How to Use HLOOKUP Function in Google Sheets
We frequently need to search the numbers in the given range or section in our Google Sheets file because we frequently employ enormous data sets. Since the data is now written in row-wise order instead of the usual column-wise format, how will we search for the key? Without a doubt, HLOOKUP will not operate on it, thus we must understand how to utilize it to find the key in a dataset that has been written horizontally. In the absence of VLOOKUP, we must learn how to use HLOOKUP in Google Sheets to search the key value efficiently. HLOOKUP enables us to do a search on horizontal data and obtain the appropriate value.
- To carry out a vertically aligned dataset’s key-value search.
- Managing row-wise data.
- To leverage the transposed VLOOKUP features.
HLOOKUP Function in Google Sheets
We require several examples to put into practice and master the technique before we can use the HLOOKUP function in Google Sheets.
I will apply the HLOOKUP formula to a sample of vertically written data to observe how it performs.
Following that, we’ll look at several HLOOKUP use cases along with some other straightforward operations.
To help you understand why we need HLOOKUP and why VLOOKUP failed for data that was horizontally aligned, we will also attempt to use a VLOOKUP on the same data.
How to Use the HLOOKUP Function?
Formula (Syntax) =HLOOKUP(search-key, range, index, sorted)
Here,
- Search key: Is the value being looked up?
- Range: is the area in which the search key will be looked for.
- Index: The row’s index number is what we use to compare the value we wish to obtain from that row to the matching search-key value.
- sorted: is used to indicate whether or not the data range is sorted.
I have a dataset with the top automobiles, their top speeds, and their prices written horizontally. We’ll try retrieving the relevant car name using HLOOKUP on the data range by using the top speed or price as the search key. To comprehend every element, follow the detailed steps below.
Step 1>
To continue the example using comparable sample data.
Step 2>
In the empty cell below, start typing the HLOOKUP function by writing “=”.
Step 3>
Pass the first parameter-search_key, which identifies the data range we’re looking for.
Step 4>
Pass the second option (data_range, the area in which the search key should be searched) along.
Step 5>
In VLOOKUP, which works with column-wise data, we pass the row index (the row number from which we wish to obtain the associated value of the search key).
Step 6>
If your range is sorted, then you should pass true for is_sorted; otherwise, you should pass false.
Step 7>
Close the final bracket, then press Enter to finish the formula.
Step 8>
If you want to drag or copy the formula to additional columns, use the $ Notation.
Here’s how to utilize the HLOOKUP function in Google Sheets; further examples of what else may be done with HLOOKUP and other functions are shown below.
How to Use HLOOKUP Function in Google Sheets – HLOOKUP with MIN Function
Formula (Syntax) =HLOOKUP(MIN(B2:F2),B2:F3,1,False)
Here,
- Min: is used to determine the minimum value.
- B2:F2: The range in which we want to obtain the minimum value is B2:F2.
- B2:F3: The range in which we wish to look for the value is B2:F3.
- 1: How closely related to the formula is the row number?
- False: Is-sorted is false. false if the range is not sorted
The same data set is available, but this time we need to determine the top speed of the car with the lowest price. This is how this formula can be applied.
Step 1>
Find the car’s top speed for the lowest cost.
Step 2>
Add Min function in HLOOKUP.
Step 3>
HLOOKUP Function arguments should be passed.
Step 4>
Final Formaula (Syntax) is
=HLOOKUP(MIN(B2:F2),B2:F3,1,False)
Step 5>
After pressing enter you can see below image we have obtained the fastest-speed car at the minimum price.
How to Use HLOOKUP Function in Google Sheets – with MAX Function
Formula (Syntax) =HLOOKUP(MAX(B2:F2),B2:F3,2,False)
Here,
- Max: The function to find the maximum value is called Max.
- B2:F2: The range in which we want to find the maximum value is B2:F2.
- B2:F3: The range in which we wish to look for the value is B2:F3.
- 2: s the row number related to the formula.
- False: Is-sorted is false. false if the range is unsorted.
Step 1>
Find the price of the car with the maximum speed.
Step 2>
Start HLOOKUP syntax and Add the MAX function in HLOOKUP syntax.
Step 3>
Pass the arguments to the HLOOKUP function, and the result is returned.
Step 4>
Now the final formula (syntax) is.
=HLOOKUP(MAX(B2:F2),B2:F3,2,False)
Step 5>
After pressing enter you can see below image we have obtained the price of the maximum-speed car.
How to Use HLOOKUP Function in Google Sheets – Difference between VLOOKUP and HLOOKUP Functions
The LOOKUP family includes both VLOOKUP and HLOOKUP; VLOOKUP is used for vertical data lookups while HLOOKUP is used for horizontal data lookups. For data that is aligned columnarily, we use VLOOKUP, and for data that is oriented horizontally, we use HLOOKUP. The only change is in the data orientation; there is no difference in the way things work in terms of syntax.
Remember
- Make sure that the row holding the lookup values and the row from which you wish to get data are in the same column in your data. The first row of the data structure is where HLOOKUP looks for the lookup value.
- Specify the lookup value with care. It must exactly match the value you’re looking for. Partial matches may prevent HLOOKUP from operating as planned.
- Verify again that both the row containing the lookup values and the row from which you wish to extract data are included in the table range. To prevent unexpected changes while duplicating the formula, this range should be an absolute reference (e.g., $A$1:$D$2).
- In the event that the lookup value is not present in the first row, be ready to handle #N/A errors that might happen. If you want to offer a more user-friendly response when problems happen, think about using the IFERROR function.
Frequently Asked Questions
What happens if my lookup value is not in the table range’s first row?
HLOOKUP returns the #N/A error when the lookup value cannot be located in the first row. In order to deal with this, you can utilize the IFERROR function to give a clear warning or do particular actions when errors happen.
Can I simultaneously extract data from several rows using HLOOKUP?
HLOOKUP is primarily made for retrieving data from single rows. Use the INDEX and MATCH functions together to extract data from several rows.
Is it possible to combine HLOOKUP with other Google Sheets functions?
Yes, different functions can be layered inside of HLOOKUP to accommodate more complex situations. For more sophisticated data retrieval and error management, you can combine it with operations like IF, INDEX, or MATCH.
How can I dynamically adapt my HLOOKUP formula to changing data sets?
Using named ranges or the OFFSET function, you can give your HLOOKUP formula some dynamic functionality. This enables your formula to evolve or grow automatically as your data does.
Are there any other horizontal data retrieval methods in Google Sheets other than HLOOKUP?
Yes, the INDEX and TRANSPOSE functions are options. While TRANSPOSE can change the orientation of your data, INDEX is flexible and can be used to get both horizontal and vertical data.
Conclusion
We’ve covered all the essentials of this useful tool, explored how it differs from VLOOKUP, and delved into the importance of learning HLOOKUP in this thorough introduction to using the HLOOKUP function in Google Sheets. The HLOOKUP function on horizontally structured data has been implemented step-by-step, and we’ve even delved into more complex pairings, such as combining HLOOKUP with the MIN and MAX functions.
In the coming future, keep an eye out for additional instructive lessons from us. Take care until then and don’t forget to subscribe, like, and share our blog. We remain inspired and motivated thanks to your support. We are grateful that you chose OfficeChaser as your learning space. Maintain your research, learning, and exploration!