Forum Discussion
Combining two lookup functions?
Here is an updated solution which I believes answers the question.
It is an array formula so you need to press Ctrl + Shift + Enter to run it on your Excel version.
Hi Riny_van_Eekelen. Thank you for your suggestion, however, it doesn't seem to work with my data.
Joannerw Was about to post another solution when I noticed that you had already accepted another one. I would like to offer now anyway. Feel free to use or discard it.
It does not require the list to be sorted in any way, so that you can just keep on adding ID's, Job codes and Effective dates at the bottom of a (structured) table. Ranges used in the formulae expand automatically with the expanding table.
One extra step that determines the latest effective date for the employee, finds the current job code and then finds the first date for the employee with that code. The attached revised workbook contains two solutions. One using FILTER (which may not yet work for you) and another with more traditional functions.