I am looking to find a formula to show a value or text for those who have "left" if the current month is no longer listed against their name, as well as if the current month is listed they are still "active"

see image example of data

Employee Names & Dates Image:

CodePudding user response：

Give a try on below formula-

=IF(ISNUMBER(FILTER($B$2:$B$15,($A$2:$A$15=F2)*(MONTH($B$2:$B$15)=MONTH(TODAY())),"Left")),"Active","Left")

For any version of excel you can use-

=IF(COUNTIFS($A$2:$A$15,F2,$B$2:$B$15,">="&EOMONTH(TODAY(),-1) 1,$B$2:$B$15,"<="&EOMONTH(TODAY(),0))>0,"Active","Left")