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.
=XLOOKUP(E2, C2:C5, B2:B5) How it works: XLOOKUP is ideal for this 'reverse lookup' scenario because it allows you to specify independent lookup and return arrays, unlike VLOOKUP which requires the lookup column to be the first in the range. Here, we look for the Employee ID (103) in column C (C2:C5) and return the corresponding value from column B (B2:B5), which is the Department.
Data Setup
| "Employee Name" | "Department" | "Employee ID" | "" | "Lookup ID" | "Result" |
|---|---|---|---|---|---|
| "Alice Smith" | "HR" | 101 | "" | 103 | "Marketing" |
| "Bob Johnson" | "Sales" | 102 | "" | "" | "" |
| "Charlie Brown" | "Marketing" | 103 | "" | "" | "" |
| "Diana Prince" | "HR" | 104 | "" | "" | "" |
Step-by-Step Guide
Identify the employee ID you want to look up. In this example, it's in cell E2 (103).
Specify the range containing all employee IDs (the lookup array). This is C2:C5.
Specify the range containing the departments (the return array). This is B2:B5.
Combine these into the XLOOKUP formula: `=XLOOKUP(E2, C2:C5, B2:B5)` and enter it into the cell where you want the result (e.g., F2).
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.
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.
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.