How to use the XLOOKUP function in Excel365?

The better alternative to VLOOKUP is XLOOKUP which is exclusive to Excel 365. It assists users in looking at the value in one column and extracting the value from different columns that are based on a certain condition.

It is one of the most efficient techniques for dealing with error values and flexibility in nature. This amazing feature is enhanced to manage data and deal with errors. The XLOOKUP's major advantages are its improved efficiency, accuracy, and straightforward formula.

XLOOKUP Function in EXCEL365

Step 1

Open the worksheet comprising the two tables separately and write the following data as given below −

Step 2

The main goal of this example is to find the designation of Dennis employee. Enter the formula =XLOOKUP(E2,Table2[Employee Name],Table2[Designation]) in the E3 cell.

Step 3

And then press the “Enter” tab. Therefore, the result is displayed in the E3 cell.

Nested XLOOKUP in EXCEL 365

Step 1

Create an interactive table in the range A1:D7 and fill up the proper data as shown in the below image. Write the text in the ranges F2:F4 and G2:G3. Our main task is to find the maximum profit value that occurred in quarter 2 for an employee named “David Joseph”.

Step 2

Enter the formula “=XLOOKUP(G2,Table2[Employee Name],XLOOKUP(G3,Table2[[#Headers],[Quarter 1]:[Quarter 3]],Table2[[Quarter 1]:[Quarter 3]]))” in the G4 cell.

Explanation

• G2 − The users must look at the value “David Joseph” in the first column.

• Table2[Employee Name] − The second argument denotes the first column of the table.

• XLOOKUP(G3,Table2[[#Headers],[Quarter 1]:[Quarter 3]],Table2[[Quarter 1]:[Quarter 3]])) − The third argument defines the another XLOOKUP function where users have to look the G3 cell’s value in the table2 heading Table2[[#Headers],[Quarter 1]:[Quarter 3]] indicating ranges B1:D1 and extract the specified value from the range Table2[[Quarter 1]:[Quarter 3]])) representing the range B2:D7 indirectly.

Step 3

Hence, the resulting value is obtained in the G4 cell as highlighted in the below image. As you see, in the nested XLOOKUP, we search for the two values that are specified in the G2 and G3 cells.

Conclusion

XLOOKUP is one of the excellent techniques that overcome the limitations of the HLOOKUP and VLOOKUP functions. It enhances user productivity and streamlines users through the usage of the XLOOKUP function. Note that it is not available in the earlier version of Microsoft Excel. Users either need to install Office 365 or may use the web version of Office 365.