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,
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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"))
 

livmun

New Member
Joined
Feb 6, 2014
Messages
8
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 10, 2009
Messages
2,757

ADVERTISEMENT

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

hth
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.

Have a nice weekend.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,957
Messages
5,599,046
Members
414,281
Latest member
Engjamal2021

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
Top