Excel Workout 28 - Mastering the XLOOKUP Formula

Workouts Excel
1

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.

Interactive Task:

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”.

    • Your Formula: ________________________

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

    • Your Formula: ________________________

  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.

2

Hi @EnterpriseDNA ,

Please find the solution to this workout

Interactive Task:

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

Answer: =XLOOKUP(“John Doe”, B2:B100, C2:C100)
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”.

Answer:
=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.

Answer: formula in cell B2
=XLOOKUP(A2, D2:D100, F2:F100, “Not Found”)
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?
    Answer:
  • 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?
    Answer:
  • i) An error.

Thanks for the workout.
Keith