Users who often work with large spreadsheets can find themselves needing to locate a value that fulfills a specific category, and doing this manually can be incredibly time-consuming. This is where the HLOOKUP function comes in.
You may be aware of the more commonly known VLOOKUP function. Think of HLOOKUP as a transposed version of the VLOOKUP version. It searches horizontally instead of vertically.
This article discusses what HLOOKUP is, the syntax for the formula, and how to do an HLOOKUP Google Sheets search. Read on to master this function.
How Does HLOOKUP Work?
The HLOOKUP function in Sheets searches for a specific value in the first row of the table. It returns a value in the column according to the index position given by the formula parameter.
The lookup parameters have to be in the table’s first row. The HLOOKUP function allows for both approximate and accurate matching.
You can use the HLOOKUP function in a horizontal table where the comparison data is in the top row of the dataset, and you wish to look down a specific number of rows.
This function aims to look up a value in a dataset, and the returning value is found in the provided table.
The HLOOKUP function is a Google Sheets built-in function classified as a LOOKUP function and is very similar to the VLOOKUP function. Many users already know how VLOOKUP works as it is the more commonly used function. However, you can use HLOOKUP can when VLOOKUP doesn’t work.
What are the Differences Between the HLOOKUP Google Sheets Function and VLOOKUP?
VLOOKUP is used to find data from a vertical spreadsheet, while the HLOOKUP function is used to find data from a horizontal one. VLOOKUP is more commonly used than HLOOKUP because spreadsheets generally exist in a vertical format rather than a horizontal one. The formulas used for both functions are essentially the same, the only change being that one searches rows while the other searches columns.
The Syntax for HLOOKUP in Google Sheets
The HLOOKUP Sheets function searches across the first row of a range for a search key and then returns a value of the specified cell in the column. The formula uses four arguments.
Here is the syntax for the Google Sheets HLOOKUP function:
=HLOOKUP(search-key, range, index, sorted)
Here are the arguments for the horizontal lookup function:
- search-key: this is the value that you wish to search for. This can be a numerical value or a string.
- range: this argument defines the range in which the search-key will be looked up. The first row is searched for the key.
- index: this argument defines the row index for the value to be returned. The first row in the range is numbered as 1.
- sorted: This is an optional argument and is used to specify whether or not the row is to be sorted is sorted or not. It is TRUE by default if you don’t provide input.
How to Use HLOOKUP Function in Google Sheets
To use the HLOOKUP function in Google Sheets, first, we need to have a set of data. In this case, we are using a data set with students’ marks on a test.
To demonstrate the function, we will write a few questions inside the spreadsheet and try to answer them using the HLOOKUP function.
Here are the steps you need to follow to use the HLOOKUP function in Google Sheets:
- Click on the cell where you wish to input the formula. In this case, the cell address is D5.
- Type in the starting part of the HLOOKUP formula. Remember to start the formula with an Equal (=) sign.
- The first argument is the value we wish to look for in the data. In this case, the number is 86.
- Now enter the cell range where the value will be searched for. In this case, the cell range is B2:I3. Add a comma after inputting the cell address.
- Now, enter the third argument used to define which rows to show the corresponding value. We use 2 as the names are in the second row, relative to the formula.
- Enter the fourth and final argument as FALSE as the values arent in ascending order.
- Click on Enter to execute the formula.
Now, let’s find the student who got the highest score. To do this, we use the HLOOKUP function in tandem with the MAX function. The cell address range for the marks is defined in the MAX formula. Other than that, the formula has the same arguments as the previous steps. Here is the formula used in the example:
Here is another example where we want to find a specific student’s name. To do this, we enter the student’s roll number in the first argument, the cell address in the second, and the row index in the third one. Here is the formula used for this example:
Problems With the HLOOKUP Function
Although HLOOKUP is a beneficial function, there are a few problems with it. These include:
- When looking up the search-key, the formula always uses the top row in the range argument. You can’t look up a value below the lookup row which can be frustrating for many users.
- The formula isn’t dynamic which means that inserting another row between the range won’t automatically update the index, meaning that you manually have to change it in the formula.
Although these problems can be extremely frustrating, not all hope is lost. You can use a combination of the REGEXMATCH, INDEX, and MATCH functions to fix many issues when using the HLOOKUP function.
Frequently Asked Questions
How Do I Use HLOOKUP in Google Sheets?
To use the HLOOKUP formula in Google Sheets, ensure that the row you wish to look through is at the top of the formula. Then utilize the formula =HLOOKUP(search-key, range, index, sorted) to look for a specific value in your spreadsheet.
Does Google Sheets Have HLOOKUP?
Google Sheets has the HLOOKUP formula, and it works similarly to the one in Microsoft Excel. The formula allows you to get a value from a table that has been organized into rows.
What is the Difference Between HLOOKUP and VLOOKUP?
VLOOKUP stands for Vertical LOOKUP, while HLOOKUP stands for Horizontal LOOKUP. You use VLOOKUP for vertically set up tables and HLOOKUP for horizontally set up ones. Other than that, the functionality of both functions is essentially the same.
Mastering the HLOOKUP Function in Google Sheets
The horizontal lookup Google Sheets function is a great way to find specific values in your spreadsheets, especially if the sheet is organized horizontally.
We hope this article helps you better understand the differences between VLOOKUP and HLOOKUP Google Sheets functions and how to do an HLOOKUP search.
Let us know in the comments if you have any questions.