XLOOKUP function with 5 Examples
NEW & IMPROVED XLOOKUP (with 5 Examples)
Are you tired of using the old VLOOKUP function in Excel? Well, you’re in luck the new and improved XLOOKUP function is here, and it’s a game-changer. In this article, we’ll dive into the world of XLOOKUP and explore its features, benefits, and examples to get you started.
Let’s also check that XLOOKUP is better than VLOOKUP or both have same functionality.
What is XLOOKUP?
XLOOKUP is a powerful lookup function in Excel that allows you to search for a value in a table and return a corresponding value from another column. It’s similar to VLOOKUP, but with more flexibility and ease of use.
Why Use XLOOKUP?
So, why should you use XLOOKUP instead of VLOOKUP? Here are a few reasons:
- Flexibility: It allows you to search for a value in any column, not just the first column like VLOOKUP.
- Ease of use: It has a more intuitive syntax than VLOOKUP, making it easier to use and understand.
- Error handling: It provides better error handling than VLOOKUP, making it less prone to errors.
The XLOOKUP Syntax
The XLOOKUP syntax is as follows:
XLOOKUP(lookup_value, table_array, col_index, [if_not_found], [match_mode], [search_mode])- lookup_value: The value you want to search for.
- table_array: The range of cells that contains the data you want to search.
- col_index: The column number that contains the value you want to return.
- [if_not_found]: The value to return if the lookup value is not found.
- [match_mode]: The match mode to use (exact, approximate, or wildcard).
- [search_mode]: The search mode to use (vertical or horizontal).
Example 1: Basic XLOOKUP
Suppose we have a table with employee names and their corresponding salaries. We want to use XLOOKUP to find the salary of an employee.
| Employee | Salary |
|---|---|
| John | 50000 |
| Jane | 60000 |
| Bob | 70000 |
We can use the following formula:
=XLOOKUP(A2, A:B, 2)Where A2 is the cell containing the employee name, and A:B is the range of cells containing the employee names and salaries.
Example 2: XLOOKUP with Multiple Criteria
Suppose we have a table with employee names, departments, and salaries. We want to use it to find the salary of an employee in a specific department.
| Employee | Department | Salary |
|---|---|---|
| John | Sales | 50000 |
| Jane | Marketing | 60000 |
| Bob | IT | 70000 |
| Alice | Sales | 55000 |
We can use the following formula:
=XLOOKUP(A2, A:C, 3, "", 2, 1)Where A2 is the cell containing the employee name, A:C is the range of cells containing the employee names, departments, and salaries, and 3 is the column number containing the salaries.
Example 3: XLOOKUP with Wildcard
Suppose we have a table with product names and prices. We want to use it to find the price of a product that contains a specific word.
| Product | Price |
|---|---|
| Apple iPhone | 1000 |
| Samsung TV | 2000 |
| Apple Watch | 500 |
| Samsung Phone | 1500 |
We can use the following formula:
=XLOOKUP("*Apple*", A:B, 2)Where A:B is the range of cells containing the product names and prices, and “Apple” is the wildcard search term.
Example 4: XLOOKUP with Error Handling
Suppose we have a table with employee names and salaries, but some employees are missing salaries. We want to use XLOOKUP to find the salary of an employee, and return a custom error message if the salary is missing.
| Employee | Salary |
|---|---|
| John | 50000 |
| Jane | 60000 |
| Bob | |
| Alice | 55000 |
We can use the following formula:
=XLOOKUP(A2, A:B, 2, "Not found")Where A2 is the cell containing the employee name, A:B is the range of cells containing the employee names and salaries,
Example 5: XLOOKUP with Dynamic Range
Suppose we have a table with employee names and salaries, and we want to use XLOOKUP to find the salary of an employee, but the range of cells containing the data is dynamic.
| Employee | Salary |
|---|---|
| John | 50000 |
| Jane | 60000 |
| Bob | 70000 |
| Alice | 55000 |
We can use the following formula:
=XLOOKUP(A2, OFFSET(A1, 0, 0, COUNTA(A:A), 2), 2)Where A2 is the cell containing the employee name, A1 is the top-left cell of the range, COUNTA(A:A) returns the number of rows in the range, and 2 is the column number containing the salaries.
Common XLOOKUP Errors
Like any other Excel function, XLOOKUP can throw errors if not used correctly. Here are some common XLOOKUP errors and how to fix them:
- #N/A: This error occurs when the lookup value is not found in the table. To fix this, make sure the lookup value exists in the table, or use the
[if_not_found]argument to return a custom error message. - #VALUE: This error occurs when the syntax is incorrect. To fix this, check the syntax and make sure it’s correct.
- #REF: This error occurs when the range is invalid. To fix this, check the range and make sure it’s valid.
XLOOKUP vs VLOOKUP
So, how does XLOOKUP compare to VLOOKUP? Here are some key differences:
- Flexibility: XLOOKUP is more flexible than VLOOKUP, allowing you to search for a value in any column, not just the first column.
- Ease of use: XLOOKUP has a more intuitive syntax than VLOOKUP, making it easier to use and understand.
- Error handling: XLOOKUP provides better error handling than VLOOKUP, making it less prone to errors.
Conclusion
In this article, we’ve explored the new and improved XLOOKUP function in Excel. We’ve seen how to use XLOOKUP to search for a value in a table and return a corresponding value from another column.
We’ve also seen some examples of using XLOOKUP with multiple criteria, wildcard, error handling, and dynamic range. Finally, we’ve compared XLOOKUP to VLOOKUP and seen how XLOOKUP is more flexible, easier to use, and provides better error handling.
