# 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

##### New Member
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","")

#### Attachments

• 51.1 KB Views: 1

#### Michael M

##### Well-known Member

VBA Code:
``=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

##### New Member

VBA Code:
``=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

##### Well-known Member
One question though....
What happens when either D2 contains "Due Soon" and E2 contains "expired"

#### Peter_SSs

##### MrExcel MVP, Moderator
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?

xl2bb.xlam
CDE
2CurrentCurrent
3CurrentExpired
4CurrentDue Soon
5Due SoonCurrent
6Due SoonDue Soon
7Due SoonExpired
8ExpiredCurrent
9ExpiredExpired
10ExpiredDue 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.

1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...