SOLVED

# Combining two lookup functions?

Highlighted
Occasional Contributor

# Combining two lookup functions?

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.

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

13 Replies
Highlighted

# Re: Combining two lookup functions?

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

# Re: Combining two lookup functions?

@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

# Re: Combining two lookup functions?

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

# Re: Combining two lookup functions?

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

Highlighted

# Re: Combining two lookup functions?

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

# Re: Combining two lookup functions?

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

# Re: Combining two lookup functions?

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

Highlighted

# Re: Combining two lookup functions?

Highlighted
Best Response confirmed by Joannerw (Occasional Contributor)
Solution

# Re: Combining two lookup functions?

@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

# Re: Combining two lookup functions?

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

# Re: Combining two lookup functions?

Thank you. This worked perfectly.
Highlighted

Highlighted

# Re: Combining two lookup functions?

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