If both D2 and E2 contain "Current" then I want C2 to say yes.
if either D2 or E2 contain "Due soon" I need C2 to say "Due soon"
If either D2 or E2 say Expired then I need C2 to say expired
If both D2and E2 say expired I need C2 to say "NO"
At the moment the formula I have works for both current, one expired one current, but not one due soon, and not both expired
=IF(ISNA(VLOOKUP(D42="Current",E42="Current",1,FALSE)), "", "Yes")&IF(SUM(COUNTIF(D42:E42,"Due soon")),"Due soon","")&IF(SUM(COUNTIF(D42:E42,"EXPIRED")),"NO","")