Quickly Locating Employee Email Addresses
An HR manager needs to quickly find an employee's email address to send important announcements, respond to inquiries, or update records, using only their Employee ID. Manually sifting through a large employee roster is inefficient and time-consuming.
=XLOOKUP("E103",A2:A5,D2:D5,"Employee Not Found") How it works: This XLOOKUP formula searches for the specified Employee ID ("E103") within the 'EmployeeID' column (A2:A5). Upon finding a match, it retrieves and returns the corresponding email address from the 'Email' column (D2:D5). If the Employee ID is not found, the formula displays 'Employee Not Found', preventing errors and providing clear feedback.
Data Setup
| EmployeeID | Name | Department | |
|---|---|---|---|
| E101 | Alice Smith | Sales | alice.s@company.com |
| E102 | Bob Johnson | Marketing | bob.j@company.com |
| E103 | Charlie Brown | HR | charlie.b@company.com |
| E104 | Diana Prince | IT | diana.p@company.com |
Step-by-Step Guide
Input the Employee ID you need to look up (e.g., "E103").
Define the lookup array, which is the range containing all Employee IDs (A2:A5).
Define the return array, which is the range containing the corresponding Email addresses (D2:D5).
Add a custom message for 'if_not_found' (e.g., "Employee Not Found") to handle cases where the ID isn't present.
Explore More
Dynamically Finding Employee Salary by ID
An HR Manager frequently needs to look up an employee's salary based on their unique Employee ID. Manually searching through a large employee roster is inefficient and prone to errors, especially when dealing with hundreds or thousands of employees.
Calculate Employee Probation End Date
I need to automatically calculate the date 90 days after an employee's start date to schedule their review.
Track Number of Sick Days Taken
I need to count how many times 'Sick Leave' appears in an employee's annual attendance record.
Find Employee Department by ID (Reverse Search)
I need to look up an employee's department using their ID, but the ID column is to the right of the department column.