Forum Discussion

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, N...

Formulas and Functions

office 365

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.

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)
      )
    )
  )

ntruong1994

Copper Contributor

May 25, 2023

Thank you very much for your help Peter!