This guide covers everything you need to know about the Google Sheets LOOKUP function, including its definition, syntax, use cases, and how to use it.
What is the LOOKUP Function? How Does It Work?
The LOOKUP function in Google Sheets allows you to search for specific values in a sorted row or column and returns the corresponding value from a result range. This result range is located in the same position as the search row or column, enabling you to link and extract related data across different columns or rows.
The function works by scanning through the specified range in a row or column for a particular key value that you provide. Once it locates this key, it subsequently returns the value of the cell in the result range that occupies the same relative position.
One crucial aspect to note about the LOOKUP function is that it requires the data in the search range to be sorted. If the data is not sorted, the function might not work as expected, and using other functions such as VLOOKUP or HLOOKUP might be more suitable.
If the search key is not found within the range, the LOOKUP function will revert to the closest smaller value. For instance, if you’re searching for the number 2 in a dataset containing the numbers 1, 3, and 5, the function will return the value related to the number 1.
When using the search_result_array method, the function returns a value from the last row or column in the provided range. The direction of the search in this method will depend on whether the range contains more columns than rows, more rows than columns, or an equal number of rows and columns.
To illustrate, consider an example where you want to find the price of a part with a specific part number. By using the LOOKUP function and providing the part number as the search key, the range of part numbers as the search range, and the range of prices as the result range, the function would return the price corresponding to the provided part number.
The LOOKUP function is a convenient way to locate and return related data from different columns or rows, provided the data is sorted and the key exists within the range. However, other functions like VLOOKUP, HLOOKUP, or MATCH might be more appropriate if these conditions are not met.
LOOKUP Syntax
The syntax and arguments for the function are as follows:
LOOKUP(search_key, search_range|search_result_array, [result_range])
- search_key – This is the value you’re searching for in the row or column. It can be a number, text, or a cell reference, such as 42, “Cats”, or I24.
- search_range|search_result_array – There are two ways to use the LOOKUP function. One way is to provide a single row or column (search_range) to look through for the search_key, with a second argument (result_range) from which to return a result. The other way is to combine these two arguments into one (search_result_array), where the first row or column is searched and a value is returned from the last row or column in the array.
- result_range – This argument is optional. It’s the range from which to return a result. The value returned corresponds to the location where the search_key is found in the search_range. This range must be a single row or column and should not be used if you’re using the search_result_array method.
There are some important usage notes related to the syntax and arguments:
- The LOOKUP function only works properly if the data in the search_range or search_result_array is sorted. If your data isn’t sorted, you should use VLOOKUP, HLOOKUP, or another related function.
- If the search_key isn’t found, the LOOKUP function will use the value immediately smaller in the range provided. For example, if your data set contains the numbers 1, 3, and 5 and your search_key is 2, then 1 will be used for the lookup.
- When using the search_result_array method, the value returned will be from the last row or column in the provided range. If you want a value from a different row or column, use VLOOKUP or HLOOKUP instead.
- If you’re using the search_result_array method and the range provided contains more columns than rows, the search will be from left to right over the first row in the range. If the range contains an equal number of rows and columns or more rows than columns, the search will be from top to bottom over the first column in the range.
Examples of How to Use the LOOKUP Function
Here are some practical examples that will show you how to use the LOOKUP function in Google Sheets:
Example #1: Basic LOOKUP Function
Suppose you have a list of employees with their respective sales for the month. You want to find out how much sales John made. Here’s how you can use the LOOKUP function:
- In A1:A5, input the names of the employees.
- In B1:B5, input the corresponding sales.
- In cell C1, type the following formula: =LOOKUP(“John”, A1:A5, B1:B5)
The formula will return the sales of John. Here, “John” is the search_key, A1:A5 is the lookup range, and B1:B5 is the result range.
Example #2: LOOKUP Function with Sorted Data
The LOOKUP function assumes that the data is sorted. If it’s not, the results might be incorrect. Let’s use the same data as in Example #1 but sort it in ascending order.
- Sort the data in A1:A5 in ascending order.
- Use the same formula as in Example #1: =LOOKUP(“John”, A1:A5, B1:B5)
The formula will return the correct sales of John, even if the data is sorted.
Example #3: LOOKUP Function with Numeric Data
The LOOKUP function can also be used with numeric data. Suppose you have a list of product codes and their respective prices. You want to find out the price of the product with code 1002.
- In A1:A5, input the product codes.
- In B1:B5, input the corresponding prices.
- In cell C1, type the following formula: =LOOKUP(1002, A1:A5, B1:B5)
The formula will return the price of the product with code 1002.
LOOKUP: Common Mistakes & Problems
When using the LOOKUP function in Google Sheets, it’s easy to encounter a few common mistakes and problems. Here are some of the most frequently encountered issues:
- Unsorted Data: The LOOKUP function in Google Sheets assumes that the data in the range is sorted in ascending order. If the data is not sorted, the function may not work as expected.
- Mismatched Data Types: The LOOKUP function may return an error if the data types in the search_key and the range are not the same. For example, if you are searching for a text string in a range of numbers, the function will not work.
- Non-Unique Values: If the range contains non-unique values, the LOOKUP function may return unexpected results. This is because the function will always return the first match it finds.
- Incorrect Search Key: If the search_key is not in the range, the LOOKUP function will return an error. Make sure the value you are searching for is actually in the specified range.
- Using the Wrong Function: The LOOKUP function is not always the best choice for every situation. If you need to search for a value in a two-dimensional range, you might want to use the VLOOKUP or HLOOKUP function instead.
- No Match Found: If LOOKUP can’t find the search_key, it will return the closest (but smaller) value. If all values in the vector are larger than the search_key, LOOKUP will return an error.
- Array Formulas: If using LOOKUP within an array formula, you need to ensure the dimensions of your arrays match, or an error will occur
Why Is LOOKUP Not Working? Troubleshooting Common Errors
If you experience difficulties while using the LOOKUP function in Google Sheets, it’s likely due to one of several common errors. Understanding these errors, their causes, and potential solutions can help you troubleshoot and resolve the issue.
#N/A Error
Cause: This error typically occurs when the LOOKUP function can’t find the lookup value within the specified range.
Solution: Double-check your lookup value and range to ensure they match. If your lookup value doesn’t exist within the range, you’ll need to add it or use a different value.
#REF! Error
Cause: The #REF! Error is caused when the formula references a cell that doesn’t exist, such as when a row or column has been deleted.
Solution: To fix this, you should adjust your formula to reference only existing cells.
#VALUE! Error
Cause: The #VALUE! error appears when the wrong data type is used in the formula. For instance, if you try to perform a mathematical operation on a text value, the formula can’t process it and will return this error.
Solution: Ensure that your formula is referencing the correct data types. If you’re performing a mathematical operation, for instance, all referenced cells should contain numbers.
#NUM! Error
Cause: This error occurs when the LOOKUP function is used with non-numerical data or when a numerical result cannot be calculated from the provided data.
Solution: Verify that your lookup value and array are numerical if you’re using a numerical lookup. If the error persists, consider using alternative functions like VLOOKUP or HLOOKUP which are better equipped to handle non-numerical data.
#ERROR! Error
Cause: This typically occurs when Google Sheets encounters an unknown error that it can’t classify under the previous error types.
Solution: Try simplifying your formula or breaking it down into smaller parts to identify the problem area. If the error persists, you may need to consult with Google’s help center or forums for more specific assistance.
Circular Dependency Detected
Cause: This error arises when a formula refers back to its own cell either directly or indirectly, creating a loop that Google Sheets can’t resolve.
Solution: To resolve this, you need to adjust your formulas so they don’t refer back to the cell in which they’re located.
Using LOOKUP With Other Google Sheets Functions
Combining the LOOKUP function with other Google Sheets functions can allow you to perform more complex data analysis and manipulation. Here’s how you can use LOOKUP in conjunction with other functions:
With SUM Function:
Usage: You can use the LOOKUP function with the SUM function to find a value in a row or column and sum it with other values.
Example: Let’s say you have a list of products and their respective sales in different months, and you want to find the total sales of a specific product. You can use LOOKUP to find the product and SUM to add up its sales. Here’s how you can do it:
=SUM(LOOKUP(“Product A”, A2:A10, B2:D10))
In this example, “Product A” is the value we’re looking for in the range A2:A10, and B2:D10 is the range of cells containing the sales data. The LOOKUP function finds the row for “Product A”, and the SUM function adds up the sales.
With IF Function:
Usage: You can use the LOOKUP function with the IF function to return a value based on a condition.
Example: Let’s say you have a list of employees and their respective salaries, and you want to find out if an employee’s salary is above the average. You can use LOOKUP to find the employee and IF to check the condition. Here’s how you can do it:
=IF(LOOKUP(“Employee A”, A2:A10, B2:B10)>AVERAGE(B2:B10), “Above Average”, “Below Average”)
In this example, “Employee A” is the value we’re looking for in the range A2:A10, and B2:B10 is the range of cells containing the salary data. The LOOKUP function finds the salary for “Employee A”, and the IF function checks if it’s above the average salary. If it is, it returns “Above Average”; if not, it returns “Below Average”.
With VLOOKUP Function:
Usage: You can use the LOOKUP function with the VLOOKUP function to return a value from a specific column based on a lookup value.
Example: Let’s say you have a table of customers and their orders, and you want to find out what a specific customer ordered. You can use LOOKUP to find the customer and VLOOKUP to return the order. Here’s how you can do it:
=VLOOKUP(LOOKUP(“Customer A”, A2:A10), A2:C10, 3, FALSE)
In this example, “Customer A” is the value we’re looking for in the range A2:A10, and A2:C10 is the range of cells containing the customer and order data. The LOOKUP function finds the row for “Customer A”, and the VLOOKUP function returns the order from the third column of the table.
For more details on the LOOKUP function, check out the official documentation at the Google Docs Editors Help Center.
