# Excel Workout 28 - Mastering the XLOOKUP Formula

Title: Excel Proficiency: Mastering the XLOOKUP Formula

Description:

The `XLOOKUP` function in Excel allows you to search a range or array, find the right value, and return the associated data from a different range or array. Dive deep into this formula, understanding its nuances and applications.

Scenario:

You’re working in a company’s finance department, and you have two tables in Excel. One contains employee IDs and names, while the other has employee IDs and their respective salaries. You’re tasked with matching the names to the salaries using the `XLOOKUP` function.

Objectives:

By the end of this workout, you should be able to:

1. Understand the syntax and components of the `XLOOKUP` function.

2. Efficiently retrieve data from one table based on another table’s values.

3. Handle potential errors or mismatches using `XLOOKUP`.

Given the aforementioned tables and the following tasks, describe how you’d use the `XLOOKUP` formula:

1. Retrieve the salary of an employee named “John Doe”.

2. Get the name of an employee with an ID of “E12345”.

3. For a list of names in column A, populate column B with their respective salaries.

• Your Formula for cell B2: ________________________

Questions:

1. What advantage does `XLOOKUP` have over the traditional `VLOOKUP` in Excel?

• i) `XLOOKUP` can only search vertically.

• ii) `XLOOKUP` allows for both horizontal and vertical lookups.

• iii) `XLOOKUP` requires sorted data.

• iv) `XLOOKUP` cannot handle errors.

2. If the `XLOOKUP` function doesn’t find a match, what will it return by default?

• i) An error.

• ii) The closest value.

• iii) Zero.

• iv) The last value in the lookup array.

Duration: 20 minutes

Difficulty: Intermediate

Period :
This workout will be released on Wednesday, September 20, 2023, and will end on Thursday, October 05, 2023. But you can always come back to any of the workouts and solve them.

Please find the solution to this workout

1. Retrieve the salary of an employee named “John Doe”.

In this formula:

• `"John Doe"` is the name you want to look up.
• `B2:B100` represents the range of employee names in the second table.
• `C2:C100` represents the range of salaries in the second table.
1. Get the name of an employee with an ID of “E12345”.

=XLOOKUP(“E12345”, A2:A100, C2:C100)
In this formula:

• `"E12345"` is the ID you want to look up.
• `A2:A100` represents the range of employee IDs in the first table.
• `C2:C100` represents the range of employee names in the second table.
1. For a list of names in column A, populate column B with their respective salaries.

In this formula:

• `A2` is the cell containing the name you want to look up.
• `D2:D100` represents the range of employee names in the second table.
• `F2:F100` represents the range of salaries in the second table.
• `"Not Found"` is the value to return if the name is not found in the first table.

Questions:

1. What advantage does `XLOOKUP` have over the traditional `VLOOKUP` in Excel?
• ii) `XLOOKUP` allows for both horizontal and vertical lookups.
1. If the `XLOOKUP` function doesn’t find a match, what will it return by default?