Nested IF AND formula

[livmun]

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,

 

[mole999]

needs a tweak

=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"))

 

[livmun]

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.

 

[mole999]

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"

 

[ukmikeb]

Try :-

=IF(AND(M5="CAFA 1-Initial Assessment (concurrent with DE)",OR(P5={"Amey","Aecom"})),P5 & "-" ,"")&M5

hth

 

[livmun]

Ooh that's perfect. Clean and simple. Thanks so much!

 

[ukmikeb]

Hi

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.

Have a nice weekend.