Forum Discussion

Joannerw's avatar
Joannerw
Copper Contributor
May 13, 2020
Solved

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 ne...
excel
Formulas and Functions
  • Alan_Murray's avatar
    Alan_Murray
    May 13, 2020

    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.

ShishirKumar's avatar
ShishirKumar
Brass Contributor
May 13, 2020

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.

Joannerw's avatar
Joannerw
Copper Contributor
May 13, 2020

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.

  • ShishirKumar's avatar
    ShishirKumar
    Brass Contributor
    May 14, 2020

    HiJoannerw 

    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.

    • Joannerw's avatar
      Joannerw
      Copper Contributor
      May 14, 2020

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

    • bhushan_z's avatar
      bhushan_z
      Iron Contributor
      May 13, 2020

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

Resources