Nested IF AND formula

livmun

New Member
Joined
Feb 6, 2014
Messages
8
Hi,
I've got another formula which doesn't seem to be working. I want it to return one result if both 2 conditions are met, a different result if both another 2 conditions are met and then another result if they are both false.
Here's my attempt:
=IF(AND(M5="CAFA 1-Initial Assessment (concurrent with DE)",P5="Amey","Amey-CAFA 1-Initial Assessment (concurrent with DE)"),IF(AND(M5="CAFA 1-Initial Assessment (concurrent with DE)",P5="Aecom"),"Aecom-CAFA 1-Initial Assessment (concurrent with DE)",M5))

In simpler terms:
If M5 = yes and P5 = Amey return Amey-yes
But if M5 = yes and P5 = Aecom return Aecom-yes
If none of those conditions are met just return the text in cell M5

Many thanks,
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
needs a tweak

Code:
=IF(AND(LEFT(M5,4)="CAFA",LEFT(P5,4)="Amey"),"Amey-CAFA 1-Initial Assessment (concurrent with DE",IF(AND(LEFT(M5,4)="CAFA",LEFT(P5,4)="Aecom"),"Amey-CAFA 1-Initial Assessment (concurrent with DE"))
 
Upvote 0
Thanks, that's really helpful. You're blowing my mind with new stuff! I just have one problem. It works for the first part but the second part returns FALSE.
 
Upvote 0
I did say it needed a tweak, but without a set to test against its really just a pointer

actually

LEFT(P5,4)="Aecom"

should either be

LEFT(P5,5)="Aecom"

or

LEFT(P5,4)="Aeco"
 
Upvote 0
Try :-
Code:
=IF(AND(M5="CAFA 1-Initial Assessment (concurrent with DE)",OR(P5={"Amey","Aecom"})),P5 & "-" ,"")&M5

hth
 
Upvote 0
Hi

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.

Have a nice weekend.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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