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.
If your Excel version supports the FILTER function, you may want to have a look at the attached workbook. Note that this function is currently available to Microsoft 365 subscribers in the Monthly channel. It will be available to Microsoft 365 subscribers in the Semi-Annual channel starting in July 2020.
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.
