Forum Discussion

bbsin's avatar
bbsin
Iron Contributor
Aug 09, 2022

find the years of serving and match back the person and display. -HOW to?

hi    I like to display if the person has been servicing from 2 to 5  to display in the 5 year column 6 to 10 years to 10 year column and so on..     can advise what is the formula and do a...
excel
Harun24HR's avatar
Harun24HR
Bronze Contributor
Aug 09, 2022

bbsin 

 

For 5 years

=IF(AND(LEFT(B3,1)*1>=2,LEFT(B3,1)*1<=5),A3,"")

For 10 years

=IF(AND(LEFT(B3,1)*1>=6,LEFT(B3,1)*1<=10),A3,"")

 For 20 years

=IF(LEFT(B3,1)*1>=11,A3,"")

 

 

  • bbsin's avatar
    bbsin
    Iron Contributor
    Aug 10, 2022

    hi Harun24HR,

    Thank you this is what I need.

    one more question, what if just want to find those in 5 yrs , 10 yrs and 20yrs, which part of the formula should be edit? Thanks
    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      Aug 10, 2022

      bbsin 

      Then you need to edit TRUE part of IF() function. Replace A3 with your desired word like

       

      =IF(AND(LEFT(B3,1)*1>=2,LEFT(B3,1)*1<=5),"5 Yrs","")

       

       

Resources