IF and IF(And Formula

[Koda7]

I'm currently using the following formula.

=IF($A13="LM Coordinated Audiences","STG Led",IF($A13="Workload Optimized Infrastructure","STG Led",IF($A13="Workload Optimized Systems","STG Led",IF($A13="STG - Competitive & Install","STG Led",IF($A13="LM Targeted Need-States","STG Led"," ")))))

I need to add to this formula the following:

If column A = Industry Leadership & column B = one of the following countries, then call it "STG Led"

Countries:
GMU-ASEAN
GMU-CEE
GMU-GCG
GMU-India-South Asia
GMU-Korea
GMU-LA-Brazil
GMU-LA-Mexico
GMU-LA-SSA
GMU-MEA
NEE-Alps
NEE-Nordic
SWE-France
SWE-Italy

1st, I don't know how to do the formula, and 2nd I think the formula would be too long. So, I'm struggling with what would be my best option. I've attached a spreadsheet. Thanks for any help you can provide.

[MrExcelPro]

Hi,

What you have is a large nested formula. If it contains more than 7 IF's it won't work.

Its best to create two separate lists, one for countrys and one for the other bit and get the formula to check to see if they are on the list.

I have created two lists for in another sheet and inserted the formula below. If you need new country just add them to the lists

Please Login or Register  to view this content.

[Koda7]

Thank you so much!

[scottylad2]

=IF(AND(OR(B7=CountryCodes),A7="industry leadership"),"STG Led","")

i'm assuming you now want a new statement to replace the one you're using just now, i've named your country list CountryCodes for ease of use. The formula needs to be entered with Ctrl + Shift + Enter If you'd rather not have it as an array formula, use the above but substitue the word country codes and replace with the country reference typed directly into the formula.

see example. I've not typed all the country codes into the non array, just some to give you an example

[Koda7]

I'm actually looking for one formula. The programs below all need to be labeled in another column as "STG Led" regardless of what country its in.

LM Coordinated Audiences
Workload Optimized Infrastructure
Workload Optimized Systems
STG - Competitive & Install
LM Targeted Need-States

AND, if then Industry Leadership should also be "STG Led" IF they are in the following countries.

Countries:
GMU-ASEAN
GMU-CEE
GMU-GCG
GMU-India-South Asia
GMU-Korea
GMU-LA-Brazil
GMU-LA-Mexico
GMU-LA-SSA
GMU-MEA
NEE-Alps
NEE-Nordic
SWE-France
SWE-Italy

I'm looking for the easiest path here as I will need to copy this formula into 6 different spreadsheets (with 40K+ rows) every week. So if at all possible I want one formula to accomplish it all. Hope I made that clearer.

[MrExcelPro]

Hi,

Replace the last formula I gave you with this one:

=IF(ISERROR(VLOOKUP(A7,List!A:A,1,FALSE))=FALSE,"STG Led",IF(ISERROR(VLOOKUP(B7,List!B:B,1,FALSE))=FALSE,"STG Led",""))

Lets see if im right

[Koda7]

We're getting much closer.....problem is, there are other programs in column A that don't get an "STG Led", such as BAO, AOI. Those should end up with nothing in that column. Sorry for the confusion. I really appreciate all your help.....I just know the next one you come up with will be the right one.

[scottylad2]

ok, got you now. maybe a slightly shorter formula, name your ranges as i've shown and enter with control + shift + enter

[Koda7]

=IF(A7=STGLed,"STG Led",IF(AND(OR(B7=CountryCodes),A7="industry leadership"),"STG Led",""))

This is close, but reversed. If its STG Led program, it doesn't matter what country it is it will still be STG Led. However, for Industry Leadership, it needs to be one of the country codes I gave.

[MrExcelPro]

Hi Again,
Do all these get an STG Led?

LM Coordinated Audiences
Workload Optimized Infrastructure
Workload Optimized Systems
STG - Competitive & Install
LM Targeted Need-States

And do all Industry Leadership in these countries get an STG Led?

GMU-ASEAN
GMU-CEE
GMU-GCG
GMU-India-South Asia
GMU-Korea
GMU-LA-Brazil
GMU-LA-Mexico
GMU-LA-SSA
GMU-MEA
NEE-Alps
NEE-Nordic
SWE-France
SWE-Italy

If so then my last formula is correct (see the attachement), if not then I need more information

[scottylad2]

This is close, but reversed. If its STG Led program, it doesn't matter what country it is it will still be STG Led. However, for Industry Leadership, it needs to be one of the country codes I gave.

I stupidly named a range a range STG Led which is a little confusing What the first part of the formula is saying

IF(A7=STGLed,"STG Led"is that if the contents of the cells in column a match what i named STG Led (silly I know) which is the list below
LM Coordinated Audiences
Workload Optimized Infrastructure
Workload Optimized Systems
STG - Competitive & Install
LM Targeted Need-States

then STG Led

the next part says if any of the column B countries match CountryCodes And cells in Col A match Industry Leadership then STG Led also.....is that not what the sample is doing?

if not, can you add a sample with a few highlighted cells that match the results you require

[Koda7]

Hi Again,
Do all these get an STG Led?

LM Coordinated Audiences
Workload Optimized Infrastructure
Workload Optimized Systems
STG - Competitive & Install
LM Targeted Need-States

And do all Industry Leadership in these countries get an STG Led?

GMU-ASEAN
GMU-CEE
GMU-GCG
GMU-India-South Asia
GMU-Korea
GMU-LA-Brazil
GMU-LA-Mexico
GMU-LA-SSA
GMU-MEA
NEE-Alps
NEE-Nordic
SWE-France
SWE-Italy

If so then my last formula is correct (see the attachement), if not then I need more information

Yes, but there are other programs as well, that won't get an STG Led. For instance, the program named BAO or AIO or Business Agility, will not recieve an STG Led. In the formula you gave me, when I use it on a Business Agility or BAO or AIO row, it still comes up as STG Led and it should be blank.

[Koda7]

Here's a new spreadsheet that is showing the way it should look in col D, the formula is in col C.

[MrExcelPro]

Maybe third time lucky

See attached

[scottylad2]

try

=IF(OR(A7=STGLed),"STG Led",IF(AND(OR(B7=CountryCodes),A7="industry leadership"),"STG Led",""))

i've just run it alongside your sample and it matches exact

[Koda7]

Maybe third time lucky

See attached

That worked!!!!! THANK YOU THANK YOU THANK YOU!!!!!