This guide covers everything you need to know about the Google Sheets XLOOKUP function, including its definition, syntax, use cases, and how to use it.
What is the XLOOKUP Function? How Does It Work?
The XLOOKUP function returns specific data from a set within a Google Sheets document by searching for a value within a range and delivering the corresponding value from a different range. It functions similarly to the VLOOKUP and HLOOKUP functions but brings added flexibility and functionality to the table.
Unlike VLOOKUP, which only retrieves data vertically (column-based search), and HLOOKUP, which only searches horizontally (row-based search), XLOOKUP can perform both tasks, making data retrieval more versatile.
XLOOKUP provides the ability to perform an approximate match, which can be extremely beneficial when the exact data is unavailable. This makes XLOOKUP more robust in handling different types of data search requirements.
For instance, if you have a list of products with corresponding prices and want to find out the price of a specific product, you can use XLOOKUP to search for the product name in the list (the lookup value) and return the corresponding price (the return value).
XLOOKUP’s capacity to handle multiple criteria significantly enhances its search capability. This is useful when your data analysis tasks involve complex data sets with multiple variables or parameters.
XLOOKUP Syntax
The syntax and arguments for the function are as follows:
- XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Let’s cover the arguments in greater detail:
- lookup_value: This is the value you want to look for in the lookup_array. This argument is required.
- lookup_array: This is the array or range where the lookup_value is located. This argument is required.
- return_array: This is the array or range that contains the data you want to return. This argument is required.
- if_not_found: This is an optional argument. It allows you to define what the function should return if the lookup_value is not found in the lookup_array. If you don’t specify this argument, the function will return an #N/A error if it can’t find the lookup_value.
- match_mode: This is an optional argument that lets you define how the function should match the lookup_value with the values in the lookup_array. It can be set to exact match (0), exact match or next smaller item (-1), exact match or next larger item (1), or a wildcard match (2). If you don’t specify this argument, the function will default to exact match (0).
- search_mode: This is an optional argument that defines how the function should search the lookup_array. It can be set to search first to last (1), search last to first (-1), binary search sorted in ascending order (2), or binary search sorted in descending order (-3). If you don’t specify this argument, the function will default to search first to last (1).
Usage notes related to syntax and arguments:
- The lookup_value, lookup_array, and return_array arguments can be cell references, arrays, or range names.
- The if_not_found, match_mode, and search_mode arguments can be numbers or cell references that contain numbers.
- The lookup_array and return_array should have the same dimensions. If they don’t, the function may return unexpected results.
- If the match_mode argument is set to a wildcard match (2), the lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters, while a question mark matches any single character.
- If the search_mode argument is set to a binary search, the lookup_array must be sorted. If it’s not, the function may return incorrect results.
- If the XLOOKUP function can’t find an exact match and the match_mode argument is set to exact match or next smaller item (-1) or exact match or next larger item (1), the function will return the closest match.
Examples of How to Use the XLOOKUP Function
Here are some practical examples of how to use the XLOOKUP function in Google Sheets:
Example #1: Looking for a Specific Value
Imagine you have a list of employees with their corresponding ID numbers. You want to find the name of the employee with the ID ‘EMP002’. You can use the XLOOKUP function to do this.
Assume the employee IDs are in column A (from A2 to A10) and the employee names are in column B (from B2 to B10). The formula would be:
=XLOOKUP(“EMP002”, A2:A10, B2:B10)
This formula will return the name of the employee with the ID ‘EMP002’.
Example #2: Finding the Price of a Product
Suppose you have a product list with their corresponding prices. You want to find the price of the product ‘Apple’. You can use the XLOOKUP function for this.
Assume the product names are in column A (from A2 to A20) and the product prices are in column B (from B2 to B20). The formula would be:
=XLOOKUP(“Apple”, A2:A20, B2:B20)
This formula will return the price of the product ‘Apple’.
Example #3: Showing a Custom Message when No Match is Found
In the previous examples, if the lookup value is not found, the XLOOKUP function will return an error. However, you can specify a custom message to be shown when no match is found.
Using the same employee list from Example #1, if you want to look for an ID ‘EMP050’ which is not in the list, you can use the following formula:
=XLOOKUP(“EMP050”, A2:A10, B2:B10, “No Match Found”)
This formula will return ‘No Match Found’ because ‘EMP050’ is not in the list.
Example #4: Looking in Reverse Order
By default, the XLOOKUP function searches from top to bottom. However, you can make it search from bottom to top.
Using the same product list from Example #2, if you want to find the last occurrence of the product ‘Apple’, you can use the following formula:
=XLOOKUP(“Apple”, A2:A20, B2:B20, , -1)
This formula will return the price of the last occurrence of the product ‘Apple’.
XLOOKUP: Common Mistakes & Problems
When using the XLOOKUP function in Google Sheets, users often encounter several common mistakes and problems. Understanding these can help you avoid them and use the XLOOKUP function more effectively:
- Mismatched array sizes: XLOOKUP requires the lookup_array and return_array to be the same size. If they are not, you will receive an error. Ensure the two arrays you’re working with have the same number of rows or columns.
- Using a non-unique lookup value: If the lookup value is not unique within the lookup array, XLOOKUP will return the first match it finds. This can lead to incorrect results if you intend to match a different instance of the value.
- Not handling errors: If XLOOKUP doesn’t find a match for the lookup_value, it will return an error. You should plan for this possibility in your formulas, for example, by using the IFERROR function to provide a default value in case of an error.
- Relying on default behavior: By default, XLOOKUP searches from the first item to the last. It also does an exact match and assumes the data is unsorted. If your data or needs don’t fit these assumptions, be sure to use the optional arguments to adjust the function’s behavior.
- Overlooking data types: XLOOKUP can’t match different data types. If your lookup value is a number, but the values in your lookup array are stored as text (or vice versa), XLOOKUP will not be able to find a match.
- Neglecting to use absolute references: If you’re copying your XLOOKUP formula to other cells, make sure to use absolute references for the lookup and return arrays. If you don’t, the arrays might shift as you copy the formula.
Why Is XLOOKUP Not Working? Troubleshooting Common Errors
If you’re experiencing some issues while using the XLOOKUP function in Google Sheets, it’s possible that you’re encountering one of the common errors. In this section, we’ll discuss these errors, their causes, and how to resolve them.
#VALUE! Error
Cause: This error usually occurs when the search_key argument is not found in the search_range.
Solution: Ensure that the search_key you’re using exists within the search_range. Check for spelling mistakes, extra spaces, or incorrect capitalization that could prevent the function from locating the search_key.
#N/A Error
Cause: This error typically appears when the function cannot find a match for the specified search_key. This could be due to an incorrect search_mode or if the search_range is not sorted correctly for the specified search_mode.
Solution: Verify the search_mode and ensure your search_range is sorted accordingly. The search_range doesn’t need to be sorted for exact matches, but for approximate matches, it should be sorted in ascending order.
#REF! Error
Cause: This error might occur if the return_range argument doesn’t match the size of search_range or if the function references a cell range outside the boundaries of the spreadsheet.
Solution: Make sure that the size of your return_range matches the size of your search_range. Also, verify the cell range referenced by your function to ensure it’s within the boundaries of your spreadsheet.
#NUM! Error
Cause: This error is displayed when the function receives a non-numeric value for an argument that requires a numeric value. This can occur when the search_mode or match_mode is set to a non-numeric value.
Solution: Check the search_mode and match_mode arguments and ensure they are set to valid numeric values. The search_mode should be -1, 0, or 1, and the match_mode should be 0 or 1.
#DIV/0! Error
Cause: This error generally appears when the function tries to divide by zero. This can occur if the search_range is empty or the match_mode is set to 1 (exact match) and the search_key is not found.
Solution: Make sure your search_range is not empty, and if you’re using an exact match, verify that your search_key exists in the search_range.
#ERROR! Error
Cause: This is a general error that can occur if the function is not formatted properly.
Solution: Check your function and ensure it’s formatted correctly. All arguments should be in the correct order and separated by commas. Make sure you have an opening and closing parenthesis, and there are no missing or extra commas.
Using XLOOKUP With Other Google Sheets Functions
Combining the XLOOKUP function with other Google Sheets functions can enhance its utility and allow you to perform more complex data manipulation tasks. Here are some examples of how to use XLOOKUP with other functions:
With IFERROR
Usage: An IFERROR function can be combined with XLOOKUP to return a custom message if the XLOOKUP function does not find a match.
Example: Suppose you have a list of students in column A and their scores in column B. You want to look up a student’s score, but if the student’s name is not on the list, you want to return a message “Student not found”. Here’s how you can do this:
=IFERROR(XLOOKUP(E1, A2:B10, 2, FALSE), “Student not found”)
In this example, E1 contains the student’s name you’re looking for. If the name is not found in the range A2:B10, the formula returns “Student not found”.
With ARRAYFORMULA
Usage: ARRAYFORMULA can be used with XLOOKUP to return an array of results based on multiple lookup values.
Example: Suppose you have a list of product codes in column A and their prices in column B. You have another list of product codes in column D, and you want to find their corresponding prices. Here’s how you can do this:
=ARRAYFORMULA(XLOOKUP(D2:D5, A2:B10, 2, FALSE))
In this example, D2:D5 contains the product codes you’re looking for. The formula returns an array of prices for these product codes.
For more details on the XLOOKUP function, check out the official documentation at the Google Docs Editors Help Center.
