Use XLOOKUP to Find Employee Details Based on Their ID
The XLOOKUP function in Excel is a powerful tool that allows you to search for a specific value in a range or array and return a corresponding value from another range or array. This function can be incredibly useful for looking up employee details based on their ID. In this article, we will walk you through an example of how to use XLOOKUP for this purpose.
Example
Here is an example table that we will use to demonstrate the XLOOKUP function. This table contains a list of employees with their IDs, Names, Departments, and Email addresses.
| Employee ID | Name | Department | |
|---|---|---|---|
| 101 | John Doe | HR | [email protected] |
| 102 | Jane Smith | Finance | [email protected] |
| 103 | Mike Johnson | IT | [email protected] |
| 104 | Emily Davis | Marketing | [email protected] |
Exercise
Use XLOOKUP to find the details of an employee based on their Employee ID.
- Enter the Employee ID in cell D9.
- Use the XLOOKUP function in cells B8, B9, and B10 to find the Name, Department, and Email, respectively.
Solution
Here is how you can set up the XLOOKUP functions:
Name Lookup
=XLOOKUP(D9, A2:A5, B2:B5, "Not Found")This formula looks up the Employee ID entered in D9 within the range A2:A5 and returns the corresponding Name from the range B2:B5.
Department Lookup
=XLOOKUP(D9, A2:A5, C2:C5, "Not Found")This formula looks up the Employee ID entered in D9 within the range A2:A5 and returns the corresponding Department from the range C2:C5.
Email Lookup
=XLOOKUP(D9, A2:A5, D2:D5, "Not Found")This formula looks up the Employee ID entered in D9 within the range A2:A5 and returns the corresponding Email from the range D2:D5.
By entering an Employee ID in cell D9, the XLOOKUP function will automatically retrieve and display the Name, Department, and Email of the employee in cells H2, I2, and J2, respectively.
Explanation of the Solution
In the solution, the XLOOKUP function searches for the Employee ID in the given range. If it finds a match, it returns the corresponding value from the specified column. If no match is found, it returns "Not Found". This ensures that users get accurate and relevant details about the employee they are looking up.
Feel free to enter different Employee IDs in the embedded sheet to see how the XLOOKUP function dynamically retrieves and displays the employee details.