Here’s a step-by-step tutorial to help you understand how to use XLOOKUP in Excel.
Let’s say you have a table with sales data for different products and regions, like this:
| Product | Region | Sales |
|---|---|---|
| A | East | $500 |
| B | North | $700 |
| C | South | $400 |
| D | West | $600 |
| E | North | $800 |
| F | East | $900 |
You want to find the sales amount for a specific product and region. Here’s how you can use XLOOKUP to do that:
- Start by selecting the cell where you want the result to appear.
- In this example, let’s say you want to find the sales amount for Product B in the North region, and you want the result to appear in cell D2.
- Type the XLOOKUP function in the formula bar. The syntax for XLOOKUP is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value you want to look up. In this example, it’s “B,North”.
- lookup_array: The range of cells you want to search in. In this example, it’s the first two columns of the table (“Product” and “Region”), which you can select by clicking and dragging from A2:B7.
- return_array: The range of cells you want to retrieve data from. In this example, it’s the “Sales” column of the table, which you can select by clicking and dragging from C2:C7.
- if_not_found (optional): The value to return if the lookup_value is not found. In this example, let’s leave it blank.
- match_mode (optional): Specifies whether to do an exact match or a closest match. In this example, let’s use an exact match by setting it to 0.
- search_mode (optional): Specifies whether to search from the beginning or end of the lookup_array.
In this example, let’s search from the beginning by leaving it blank.So the complete XLOOKUP formula for finding the sales amount for Product B in the North region would be:
=XLOOKUP("B,North",A2:B7,C2:C7,,0)
Press Enter to calculate the formula. The result should be $700, which is the sales amount for Product B in the North region.
Here’s another example to show you how XLOOKUP can be used with a different search mode. Let’s say you have a table with employee data, and you want to find the salary for an employee with a specific ID number. The table looks like this:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1001 | John Doe | Sales | $50,000 |
| 1002 | Jane Doe | Finance | $60,000 |
| 1003 | Bob Smith | HR | $45,000 |
| 1004 | Mary Lee | IT | $70,000 |
| 1005 | Tom Brown | Marketing | $55,000 |
Let’s say you want to find the salary for employee ID 1004. Here’s how you can use XLOOKUP with a search mode of -1 to search from the end of the lookup_array:
Start by selecting the cell where you want the result to appear.
In this example, let’s say you want to find the salary for employee ID 1004, and you want the result to appear in cell D2.
Type the XLOOKUP function in the formula bar.
The syntax for XLOOKUP is the same as before, but this time we’ll use a search mode of -1 to search from the end of the lookup_array:
=XLOOKUP(1004,A2:A7,D2:D7,,0,-1)
- lookup_value: The value you want to look up. In this example, it’s 1004.
- lookup_array: The range of cells you want to search in. In this example, it’s the “ID” column of the table, which you can select by clicking and dragging from A2:A7.
- return_array: The range of cells you want to retrieve data from. In this example, it’s the “Salary” column of the table, which you can select by clicking and dragging from D2:D7.
- if_not_found (optional): The value to return if the lookup_value is not found. In this example, let’s leave it blank.
- match_mode (optional): Specifies whether to do an exact match or a closest match. In this example, let’s use an exact match by setting it to 0.
- search_mode (optional): Specifies whether to search from the beginning or end of the lookup_array. In this example, let’s search from the end by setting it to -1.
Press Enter to calculate the formula. The result should be $70,000, which is the salary for employee ID 1004.
XLOOKUP also supports array formulas, which allows you to perform the same lookup for multiple lookup_values at once. For example, if you have a list of employee IDs and you want to find their salaries, you can use XLOOKUP as an array formula like this:
- Start by selecting the range of cells where you want the results to appear. In this example, let’s say you have a list of employee IDs in cells A2:A5, and you want to find their salaries in cells B2:B5.
- Type the XLOOKUP function in the formula bar, but this time enclose the lookup_value in curly braces {} to make it an array formula:
{=XLOOKUP(A2:A5,A2:A7,D2:D7,,0,-1)}Notice that we also omitted the return_array argument, since we’re retrieving data from the same column as the lookup_array. - Press Ctrl + Shift + Enter to calculate the formula. Excel will surround the formula with curly braces to indicate that it’s an array formula. The result should be a list of salaries that correspond to the employee IDs.
And that’s how you can use XLOOKUP in Excel to quickly search and retrieve data from a table! Remember, XLOOKUP can also handle errors and multiple matches, so be sure to explore its full potential and take advantage of its versatility in your Excel projects.