SOLVED

Combining two lookup functions?

Highlighted
Occasional Contributor

I'm not sure how to articulate this, or what formula I need to use but i'm thinking lookup?

I need to find the effective date for the employee's current position, so, in this example, for ID 1, I need to find the most recent position code, which is 1211, and the first date that this occurred, which is 1/8/2016.  Those in red are the dates I need to find for each ID and place them in another tab.

Joannerw_0-1589357335423.png

 

EDIT: for each ID number, I'm trying to find the most current position code, and the first effective date for this code. 

 

A number of employees can/could have held a position code so it will appear for more than one ID number currently, and can also appear as a previous position code. 

eg position code 1022

Joannerw_0-1589365741437.png

 

 

 

 

 

13 Replies
Highlighted

@Joannerw 

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.

Highlighted

@Joannerw

Please find the attached workbook with a solution.

I used the following formulas in a table to get the results for all the IDs

=INDEX(C:C,MATCH(G3,B:B,0)+COUNTIF(B:B,G3)-1)

Alan

Highlighted

@Joannerw 

Instead of lookup, you can place your all unique Ids in separate tab.

Then in the original tab, sort the 2 columns of Effective Date (Newest to Oldest) and Position Code (Largest to Smallest). By this way the combination of these 2 for a particular Id will be at top. After this you can do VLOOKUP on ID present in second tab to fetch Effective Date and Position Code.

Thanks.

Highlighted

Hi @Riny_van_Eekelen. Thank you for your suggestion, however, it doesn't seem to work with my data.

Highlighted

Hi @Alan Murray . Thank you for your help. The query works great, but we can have several staff with the same Position Code so it isn't working for my data.

Highlighted

Hi @ShishirKumar . Thank you for your help. However, a number of employees can have the same position code, so this method isn't working for my data.

Highlighted

@ShishirKumar  @Alan Murray  @Riny_van_Eekelen  I've added more info to the question to hopefully provide a bit of clarity. Thanks again.

Highlighted

@Joannerw in case u have latest version of excel you can use 'MAXIF' and 'MINIF' function for your task.

Highlighted
Best Response confirmed by Joannerw (Occasional Contributor)
Solution

@Joannerw

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.

Highlighted

Hi@Joannerw 

Though same position code is there for employees but date is different for a combination of Id and position code. I guess sorting and then VLOOKUP can solve your problem.

Highlighted
Thank you. This worked perfectly.
Highlighted

Thanks for your suggestion. I've had a response which worked.

Highlighted

@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.