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
  1. 365
Platform
  1. MacOS
  2. 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

  • Screen Shot 2019-12-10 at 4.28.59 pm.png
    Screen Shot 2019-12-10 at 4.28.59 pm.png
    51.1 KB · Views: 10

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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"))))
 
Upvote 0
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
 
Upvote 0
One question though....
What happens when either D2 contains "Due Soon" and E2 contains "expired"
 
Upvote 0
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
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top