Forum Discussion

ntruong1994's avatar
ntruong1994
Copper Contributor
May 24, 2023
Solved

Formula to find current manager of staff by the lastest date

Hi guys ,

From table 1, I want to get the current manager of staff by the latest date as the screenshot below. Could you please help me with the excel formula? Thanks ! 

 

 Best Regards,

   NT

Formulas and Functions
office 365
  • OliverScheurich's avatar
    OliverScheurich
    May 24, 2023

    ntruong1994 

    =INDEX($C$2:$C$10,MATCH(LARGE(IF($B$2:$B$10=B2,$A$2:$A$10),1),$A$2:$A$10,0))

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

4 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 24, 2023

    ntruong1994 

    If you can guarantee that each name is in date order then simply returning the last match will work

    = XLOOKUP(WesternName, WesternName, Manager, , ,-1)

    You could include a sort in the formula, but an alternative would be to search for the largest date associated with the member of staff

    = BYROW(WesternName,
    LAMBDA(w,
      LET(
        filteredDates, IF(WesternName=w, Date),
        XLOOKUP("max", filteredDates, Manager, ,-1)
      )
    )
  )

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 24, 2023

    ntruong1994 

    =INDEX($C$2:$C$10,MATCH(LARGE(IF($B$2:$B$10=B2,$A$2:$A$10),1),$A$2:$A$10,0))

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

Resources