How to use nested XLOOKUP in Excel

To use nested XLOOKUP in Excel, you can follow the steps below:

Step 1: Create your data tables Create two tables with the data you want to use for your XLOOKUP. In this example, we will create two tables – one with a list of products and their prices, and another with a list of customers and the products they have ordered.

Table 1: Products and Prices

Table 2: Customer Orders

Step 2: Create the first XLOOKUP formula In a new cell, type in the XLOOKUP formula with the first argument being the value you want to look up, the second argument being the range to search in, and the third argument being the range to return the result from. For example, if we want to look up the price of an Apple, we would type in:

=XLOOKUP("Apple", A2:A5, B2:B5)

This will return the value 1.5, which is the price of an Apple.

Step 3: Nest the second XLOOKUP formula To nest the second XLOOKUP formula, replace the value argument with the XLOOKUP formula itself. For example, to find the price of the product ordered by John, we can nest the second XLOOKUP formula inside the first XLOOKUP formula like this:

=XLOOKUP(XLOOKUP("John", F2:F6, G2:G6), A2:A5, B2:B5)

This will return the value 2.0, which is the price of the product ordered by John.

Here’s the final table with the nested XLOOKUP formula:

Note that in this example, we first used the XLOOKUP formula to find the product ordered by John, and then used that result as the value argument in the second XLOOKUP formula to find the price of the product.

Another example to illustrate how to use nested XLOOKUP in Excel.

Step 1: Create your data tables Create two tables with the data you want to use for your XLOOKUP. In this example, we will create two tables – one with a list of employees and their salaries, and another with a list of departments and the employees that work in them.

Table 1: Employee Salaries

Table 2: Department and Employees

Step 2: Create the first XLOOKUP formula In a new cell, type in the XLOOKUP formula with the first argument being the value you want to look up, the second argument being the range to search in, and the third argument being the range to return the result from. For example, if we want to look up the salary of John, we would type in:

=XLOOKUP("John", A2:A6, B2:B6)

This will return the value 50000, which is the salary of John.

Step 3: Nest the second XLOOKUP formula To nest the second XLOOKUP formula, replace the value argument with the XLOOKUP formula itself. For example, to find the salary of employees in the Sales department, we can nest the second XLOOKUP formula inside the first XLOOKUP formula like this:

=XLOOKUP(XLOOKUP("John", D2:D5, E2:E5), A2:A6, B2:B6)

This will return the value 60000, which is the salary of Mary.

To find the salary of all the employees in the Sales department, you can use the XLOOKUP formula in an array formula. Here’s how you can do it:

1. Select a range of cells that is large enough to hold the salaries of all the employees in the Sales department.
2. Type in the following formula:

{=XLOOKUP(IF(D2:D5="Sales",E2:E5), A2:A6, B2:B6)}

3. Press Ctrl + Shift + Enter to enter the formula as an array formula.

This will return an array of values that contains the salaries of all the employees in the Sales department.

Here’s the final table with the nested XLOOKUP formula:

Note that in this example, we first used the XLOOKUP formula to find the employees in the Sales department, and then used that result as the value argument in the second XLOOKUP formula to find the salaries of the employees. We used an array formula to find the salaries of all the employees in the Sales department.