Excel 2010 - IF/AND Statement with multiple variables

[Johnny Thunder]

Hello all,

I am working on a little bit of a crazy formula and have come to a road block. Hopefully someone knows of a solution or even another way to write this formula.

So here goes,

Formula A:

=IF(AND(B17=Logo1,D17=Market2),Logo1RSub,IF(B17=Logo1,Logo1Sub,IF(B17=Logo2,Logo2Sub,IF(B17=Logo3,Logo3Sub,IF(B17=Logo4,Logo4Sub,IF(B17=Logo5,Logo5Sub,""))))))

Formula B:

=IF(AND(B17=Logo1,S17="True"),logo1UnSub,IF(AND(B17=Logo2,S17="True"),logo2UnSub,IF(AND(B17=Logo3,S17="True"),Logo3UnSub,IF(AND(B17=Logo4,S17="True"),logo4UnSub,IF(AND(B17=Logo5,S17="True"),logo5UnSub,"")))))

Definitions:
Logo1 - Logo5 = Named ranges
Logo1 - Logo5 with "SUB" in name = Subtotal Range
Logo1 - Logo5 with "UnSUB" in name = Unlicensed Subtotal Range

So what I am trying to accomplish is having these two formulas (A & B) combined with some sort of logic that says:

IF(AND(B17=Logo1 AND S17<>"True" Return Logo1Sub But if S17="True" then Return Logo1UnSub.

Written out Properly: =IF(AND(B17=Logo1,S50<>"True"),Logo1Sub,Logo1UnSub)

My issue is, how do I apply this to all the variables in 1 formula?

This is the formula combing two variable but results in an error because how it is combined.

=IF(AND(B50=Logo1,S50<>"True"),Logo1Sub,logo1UNSub),(IF(AND(B50=Logo2,S50<>"True"),Logo2Sub,logo2UNSub))

 

[shyy]

Syntax is bit off, notice the Quotations around the text string, they are not numbers, or true or false which excel knows.


=IF(AND(B50="Logo1",S50<>True),"Logo1Sub","logo1UNSub"),(IF(AND(B50="Logo2",S50<>True),"Logo2Sub","logo2UNSub"))</pre>

 

[Johnny Thunder]

Syntax is bit off, notice the Quotations around the text string, they are not numbers, or true or false which excel knows.


=IF(AND(B50="Logo1",S50<>True),"Logo1Sub","logo1UNSub"),(IF(AND(B50="Logo2",S50<>True),"Logo2Sub","logo2UNSub"))

S50= Text String "True"

So the formula works but with more digging online and trial/error I have found that a nested formula can only be done 7 times in one instance.

So I got upto,

=IF(AND(B50=Logo1,S50<>"True"),Logo1Sub,IF(AND(B50=Logo1,S50="True"),logo1UNSub,IF(AND(B50=Logo2,S50<>"True"),Logo2Sub,IF(AND(B50=Logo2,S50="True"),logo2UNSub,IF(AND(B50=Logo3,S50<>"True"),Logo3Sub,IF(AND(B50=Logo3,S50="True"),logo3UNSub,IF(AND(B50=Logo4,S50<>"True"),Logo4Sub)))))))

And then the formula won't allow for anymore if Statements and I still had 2 more entries to complete this formula.

Any idea?

 

[shyy]

I am not sure if the INDEX and MATCH has the same limitations. But I would try that first.