XLOOKUP is a powerful formula, which allows you to look up and retrieve values from a table based on one or more search criteria. This formula is a more advanced version of the traditional VLOOKUP and HLOOKUP formulas, and offers greater flexibility and functionality.
Here’s a step-by-step guide on how to use XLOOKUP in Excel, along with some actual table examples:
Step 1: Identify the lookup and return arrays
Before you start using the XLOOKUP formula, you need to identify the lookup and return arrays. The lookup array is the range of cells that contains the values you want to search for, while the return array is the range of cells that contains the values you want to retrieve.
For example, let’s say you have a table that contains the names of employees in column A, and their corresponding salaries in column B. In this case, column A would be your lookup array, while column B would be your return array.
Step 2: Determine the search criteria
Next, you need to determine the search criteria that you want to use to find the value you’re looking for. This can be a single value, or a combination of values.
For instance, let’s say you want to find the salary of a specific employee named “John”. In this case, “John” would be your search criteria.
Step 3: Enter the XLOOKUP formula
Once you’ve identified the lookup and return arrays, and determined the search criteria, you can enter the XLOOKUP formula into the cell where you want the retrieved value to appear.
The basic syntax of the XLOOKUP formula is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
Here’s what each argument means:
- lookup_value: This is the value or cell reference that contains the search criteria.
- lookup_array: This is the range of cells that contains the values you want to search for.
- return_array: This is the range of cells that contains the values you want to retrieve.
- match_mode: This is an optional argument that specifies how the search should be conducted. The available options are 0 (exact match), -1 (exact match or next smaller value), and 1 (exact match or next larger value).
- search_mode: This is an optional argument that specifies whether the search should be conducted from the beginning of the lookup_array or from the end of the lookup_array. The available options are 1 (search from beginning) and -1 (search from end).
For example, to find the salary of “John” in the table mentioned earlier, you would enter the following XLOOKUP formula:
=XLOOKUP("John", A2:A10, B2:B10)
This formula tells Excel to search for the value “John” in the range A2:A10 (which contains the names of employees), and return the corresponding value from the range B2:B10 (which contains the salaries).
Step 4: Adjust the XLOOKUP formula as needed
Depending on your specific needs, you may need to adjust the XLOOKUP formula to include additional search criteria, or to change the match_mode or search_mode arguments.
For instance, let’s say you have a table that contains the names of employees in column A, their corresponding job titles in column B, and their salaries in column C. In this case, you could use the following XLOOKUP formula to find the salary of an employee based on both their name and job title:
=XLOOKUP("John", A2:A10&B2:B10, C2:C10)
This formula concatenates the employee name and job title together (using the ampersand symbol) as the search criteria, and searches for that combined value in the range A2:A10&B2:B10. It then returns the corresponding value from the range C2:C10.
Step 5: Handle errors
Finally, it’s important to handle any errors that may occur when using the XLOOKUP formula. For example, if the lookup value is not found in the lookup array, the formula will return a “#N/A” error.
To handle this error, you can use the IFERROR function to return a custom message instead. For instance, you could modify the previous formula as follows:
=IFERROR(XLOOKUP("John", A2:A10&B2:B10, C2:C10), "Employee not found")
This formula tells Excel to first execute the XLOOKUP formula to find the salary of “John” based on his name and job title. If the formula returns a “#N/A” error (indicating that the employee was not found), the IFERROR function returns the custom message “Employee not found” instead.
And that’s it! With these steps, you should now be able to use the XLOOKUP formula in Excel to retrieve values from a table based on one or more search criteria.