Google Sheets HLOOKUP Function: Syntax, Examples & Tips

search_key:
The value that you want to search for in the first row of the specified range.

range:
The range of cells that contains the data you want to search through. The first row of this range is where the search_key will be looked up.

index:
The row number in the range from which to return the value. The first row is 1, the second row is 2, and so on.

is_sorted:
An optional parameter that indicates whether the first row of the range is sorted. If TRUE or omitted, an approximate match is returned; if FALSE, an exact match is required.

What is an HLOOKUP?

The HLOOKUP function in Google Sheets is a useful tool that allows you to search for a value in the first row of a range or array and return a corresponding value from a specified row within that range. It is particularly helpful when dealing with horizontal data layouts, making it easier to retrieve information from tables organized in rows.

In simpler terms, HLOOKUP enables you to find specific data in a horizontal table and retrieve related information from that table efficiently.

A Practical Example

Imagine you are managing a student database with their scores stored in a Google Sheets spreadsheet:

Students Table:

You want to find the score of each student in a specific subject.

HLOOKUP Formula

To achieve this, you would use the HLOOKUP function as follows:

=HLOOKUP("Math", Students!A1:E4, 3, FALSE)

In this formula:

• "Math" is the value you are searching for in the first row of the Students Table.
• Students!A1:E4 is the range where the function will look for the subject name and return the corresponding scores.
• 3 indicates that you want to return the value from the third row of the specified range.
• FALSE specifies that you want an exact match for the search.

Result of the Formula

When you apply the HLOOKUP formula, the output would show the Math scores as follows:

If you were to change the subject in the HLOOKUP formula to "Science", the result would update accordingly:

=HLOOKUP("Science", Students!A1:E4, 3, FALSE)

This would return:

Why Use HLOOKUP?

HLOOKUP is beneficial because it simplifies the process of looking up data in horizontal tables. It allows for quick retrieval of information without the need for complex formulas. However, it is important to note that HLOOKUP can only search for values in the first row of the specified range, which may limit its versatility compared to other lookup functions.

Key Takeaways:

• HLOOKUP: A Google Sheets function that searches for a value in the first row and returns a corresponding value from a specified row.
• Horizontal Data: Ideal for tables organized in rows, making it suitable for specific data layouts.
• Exact Match: Offers the ability to specify whether you want an exact match or an approximate match.
• Common Use Cases: Useful for retrieving scores, product information, or any scenario where data is organized horizontally.

HLOOKUP is a valuable function for anyone working with horizontal data in Google Sheets, providing a straightforward way to enhance data analysis and reporting capabilities.

Happy spreadsheeting!