If both cells contain Current return yes, if both cells contain expired return expired AND if either cell contains due soon return due soon

[Lizziem]

So I want C2 for example return one of four things depending on what is in D2 and E2.
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","")

 

[Michael M]

How about

=IF(AND(D2="expired",E2="expired"),"NO",IF(AND(D2="Current",E2="Current"),"yes",IF(OR(D2="Due Soon",E2="Due Soon"),"due Soon",IF(OR(D2="Expired",E2="Expired"),"Expired"))))

 

[Lizziem]

How about

=IF(AND(D2="expired",E2="expired"),"NO",IF(AND(D2="Current",E2="Current"),"yes",IF(OR(D2="Due Soon",E2="Due Soon"),"due Soon",IF(OR(D2="Expired",E2="Expired"),"Expired"))))

You're a wizard! THANKYOU

 

[Michael M]

One question though....
What happens when either D2 contains "Due Soon" and E2 contains "expired"

 

[Peter_SSs]

Welcome to the MrExcel board!

To be perfectly clear, can you provide the expected result for each row below with the logic for each result?

Book1
	C	D	E
2		Current	Current
3		Current	Expired
4		Current	Due Soon
5		Due Soon	Current
6		Due Soon	Due Soon
7		Due Soon	Expired
8		Expired	Current
9		Expired	Expired
10		Expired	Due Soon
Sheet2 (2)

Note: You will generally get better/faster results in the forum if you provide your sample data in a form that we can copy to test with. My signature block below has help with that.