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
Joined
Dec 10, 2019
Messages
2
Office Version
365
Platform
MacOS, Web
Screen Shot 2019-12-10 at 4.27.02 pm.png
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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
How about

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
Joined
Dec 10, 2019
Messages
2
Office Version
365
Platform
MacOS, Web
How about

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
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
One question though....
What happens when either D2 contains "Due Soon" and E2 contains "expired"
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,169
Office Version
365
Platform
Windows
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.
 

Forum statistics

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

Some videos you may like

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...
Top