HLOOKUP

• How to use HLOOKUP formula?
• Examples of using HLOOKUP formula
• HLOOKUP formula not working?
• Similar formulas to HLOOKUP

Usage

Use the HLOOKUP formula with the syntax shown below, it has 3 required parameters and 1 optional parameter:

=HLOOKUP(search_key, range, index, [is_sorted])

1. search_key (required):
   The value to search for in the top row of the table.
2. range (required):
   The table range to search in. The first row of the range is used as the lookup row.
3. index (required):
   The relative row number within the range to return a value from.
4. 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.

Examples

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.

Common Mistakes

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.

Related Formulas

The following functions are similar to HLOOKUP or are often used with it in a formula:

• VLOOKUP

  The VLOOKUP function 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.

• INDEX

  The INDEX formula 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.

• MATCH

  The MATCH formula 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 INDEX formula to retrieve a value from a specific cell in a range. The search_type parameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.

Learn More

You can learn more about the HLOOKUP Google Sheets function on Google Support.