Google Sheets LOOKUP Function: Syntax, Examples & Tips

search_key:
The value that you want to search for within the specified search_range or search_result_array.

search_range|search_result_array:
The range of cells that you want to search through for the search_key. This can also be an array of values from which to retrieve the result.

result_range:
(Optional) The range of cells from which to return the corresponding value if the search_key is found in the search_range. If omitted, the function returns the value from the search_range or search_result_array.

What is a LOOKUP?

The LOOKUP function in Google Sheets is a fundamental tool used to search for a value in a one-dimensional range or array and return a corresponding value from another one-dimensional range or array. It is a simple yet effective way to retrieve data based on a specific criterion. While it may not be as versatile as newer functions like XLOOKUP, it remains a valuable function for straightforward lookup tasks.

In simpler terms, LOOKUP allows you to find specific data in a row or column and retrieve related information from that row or column.

A Practical Example

Imagine you are managing a list of students and their corresponding grades stored in a Google Sheets document:

Students Table:

You want to find the grade of each student based on their StudentID.

LOOKUP Formula

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

=LOOKUP(B2, Students!A:A, Students!C:C)

In this formula:

• B2 is the cell containing the StudentID you want to look up.
• Students!A:A is the range where the function will look for the StudentID in the Students Table.
• Students!C:C is the range from which the function will return the corresponding Grade.

Result of the Formula

When you apply the LOOKUP formula to the Students Table, the output would look like this:

If you were to add a row for a student that hasn't been included, such as StudentID 4, the result would still show the corresponding grade if it exists:

In this case, if you input StudentID 4 in the lookup, the LOOKUP would return #N/A since there is no corresponding grade for David.

Why Use LOOKUP?

LOOKUP is beneficial because it provides a straightforward way to retrieve data without the complexity of nested functions. It is particularly useful for simple datasets where you need to find a value based on a single criterion. However, it is important to note that LOOKUP assumes the data is sorted in ascending order, which may limit its use in some scenarios.

Key Takeaways:

• LOOKUP: A basic function that searches for a value and returns a corresponding value from another range.
• Simplicity: Ideal for straightforward lookup tasks with simple datasets.
• Limitations: Assumes sorted data and may not handle errors as gracefully as newer functions.
• Common Use Cases: Suitable for small datasets, quick lookups, and scenarios where data is organized in a single dimension.

LOOKUP is a fundamental function for anyone working with data in Google Sheets, providing a simple way to enhance data retrieval tasks.

Happy spreadsheeting!