lookup an employee and return role based on date entry was made
I have been trolling through as many questions as I can and you have been super helpful before so maybe someone can help me again 🙂
I have two tables in a larger data set
Has all time entries submitted by all employees (EMPLID) and the date the entry was made (TRANSDATE)
Role table with start and end date of roles and promo's
The EMPLID will be duplicated in the EMPLID column if the person has had a 'role name' change in the system - each change is time stamped, the start (CREATED) and end date (Latest date) of the role are next to each occurance of the EMPLID in table 2.
*if the role hasnt been edited it will return todays date in latest date
What I need to do is look up the EMPLID in table 1 and TRANSDATE table 1 and match it to the correct role NAME for that EMPLID in table 2.
I used to have a lookup formula that worked fine when I didnt have EMPLID duplications in table 2 so now I need to implement this change to keep track of changing roles in a project and implement the right rate throughout the life of it.
If anyone can give me any pointers it would really help 🙂