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.
=INDEX(D2:D5, MATCH(1003, A2:A5, 0)) How it works: This formula uses MATCH to dynamically find the row number of 'Employee ID 1003' within the A2:A5 range. MATCH(1003, A2:A5, 0) returns 3 because '1003' is the third item in that list. This 3 is then passed as the row_num argument to INDEX. INDEX(D2:D5, 3) then retrieves the value from the third position in the D2:D5 (Salary) range, which is 65000.
Data Setup
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| 1001 | Alice Smith | Sales | 60000 |
| 1002 | Bob Johnson | Marketing | 75000 |
| 1003 | Charlie Brown | HR | 65000 |
| 1004 | Diana Prince | IT | 90000 |
Step-by-Step Guide
Identify the range containing the data you want to retrieve (e.g., the Salary column D2:D5). This will be the array argument for INDEX.
Determine the lookup value (e.g., 1003 for Employee ID).
Identify the range where your lookup value can be found (e.g., the Employee ID column A2:A5). This will be the lookup_array for MATCH.
Combine INDEX with MATCH: =INDEX(D2:D5, MATCH(1003, A2:A5, 0))
Place the formula in a cell where you want the salary to appear.
Explore More
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.
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.