The LOOKUP function in Google Sheets is a powerful tool used to search through a single row or column for a specified key. Once the key is found, it retrieves the corresponding value from a designated result range positioned similarly to the original search. This function is particularly useful for data analysis and retrieval tasks where efficient data organization is paramount.

Syntax

LOOKUP(search_key, search_range, [result_range])

• search_key: The value you are searching for in the search range.
• search_range: The range where the function will look for the specified key.
• result_range: (Optional) The range from which the value will be returned if the search_key is found. If not provided, it defaults to the search_range.

Example #1

LOOKUP("Banana", A1:A5, B1:B5)

This function searches for “Banana” in the range A1:A5 and returns the corresponding value from B1:B5. If A3 contains “Banana” and B3 contains “0.50”, the result would be 0.50.

Example #2

LOOKUP(5, D1:D5, E1:E5)

In this case, the function looks for the number 5 within D1:D5 and, if found, retrieves the corresponding value from E1:E5. If D4 holds the value 5 and E4 contains “Found!”, the output will be “Found!”.

Example #3

LOOKUP("Apple", F1:F5)

Here, the function searches for “Apple” in the range F1:F5 and returns the value from the same cell within the range since no result_range is specified. So if “Apple” is located in F2, the outcome will be whatever value is in that cell.

Error handling

• N/A: This error appears if the search_key is not found in the search_range.
• VALUE!: This error occurs when the data types of the search_key and search_range do not match, leading to invalid comparisons.
• REF!: This indicates that a reference is invalid, typically when the specified ranges are outside the permissible limits of the sheet.

Conclusion

The LOOKUP function in Google Sheets serves as an efficient means for data retrieval based on a specified search key. Its versatility allows users to easily extract relevant information from organized datasets, making it a valuable asset for both casual users and seasoned data analysts. With an understanding of its syntax and potential error messages, users can quickly harness its capabilities to improve their spreadsheet functionality.