HLOOKUP function is a lookup formula that searches for a key in the top row of a table and returns the value in the same column for a specified row. This function is commonly used to extract data from a table based on a specific criteria.
- How to use
- Examples of using
HLOOKUPformula not working?
- Similar formulas to
HLOOKUP formula with the syntax shown below, it has 3 required parameters and 1 optional parameter:
- search_key (required):
The value to search for in the top row of the table.
- range (required):
The table range to search in. The first row of the range is used as the lookup row.
- index (required):
The relative row number within the range to return a value from.
- is_sorted (optional):
An optional parameter that specifies whether the search range is sorted in ascending order. If omitted or set to TRUE, the search range is assumed to be sorted.
ExamplesHere are a few example use cases that explain how to use the
HLOOKUP formula in Google Sheets.
Extracting data based on a specific criteria
HLOOKUP can be used to extract data from a table based on a specific criteria. For example, if you have a table of sales data and you want to extract the total sales for a specific month, you can use HLOOKUP to search for the month in the top row of the table and return the corresponding value in the row for total sales.
Populating a cell based on a dropdown selection
HLOOKUP can be used in conjunction with a dropdown list to populate a cell with a value based on the selected option. For example, if you have a dropdown list of products and you want to populate a cell with the corresponding price, you can use HLOOKUP to search for the selected product in the top row of a table and return the corresponding value in the row for prices.
HLOOKUP not working? Here are some common mistakes people make when using the
HLOOKUP Google Sheets Formula:
Missing or incorrect search key
One of the most common mistakes when using HLOOKUP is forgetting to input the search key or inputting the wrong search key. Make sure the search key matches the data you are trying to look up.
Incorrect range input
Another common mistake is inputting the wrong range in the formula. The range should include both the lookup value and the result you want to return. Double check that the range is correct.
Incorrect index number
If you input the wrong index number, the formula will return the wrong value. Make sure the index number matches the position of the result you want to return, starting from the first row of the range.
Not sorting the range
If the range is not sorted, the formula may not return the correct result. If the range is not sorted, make sure to set the is_sorted parameter to FALSE.
Using HLOOKUP instead of VLOOKUP
HLOOKUP is used to look up values in a row, while VLOOKUP is used to look up values in a column. Make sure you are using the correct formula for the orientation of your data.
The following functions are similar to
HLOOKUP or are often used with it in a formula:
VLOOKUPfunction is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.
INDEXformula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.
MATCHformula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with the
INDEXformula to retrieve a value from a specific cell in a range. The
search_typeparameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.
You can learn more about the
HLOOKUP Google Sheets function on Google Support.