Excel Nested IF/AND

Dell / INSPIRION
March 19, 2009 at 13:25:26
Specs: Windows XP, 1.0 Gb
I'm having a problem combining IF/AND formulas. Each of the formulas below work correctly. However, I can't combine them into one formula; I get "#Value" error message.

=if(and(c8="jones",g8=1),jones1!d36, jones2!d36)

=if(and(c8="smith",g8=1),smith1!d36,smith2!d36)

Thank you.


See More: Excel Nested IF/AND

Report •


#1
March 19, 2009 at 13:36:16
What do you want the end result to be?

Since both IF's look for G8=1, we'll assume that's true for now.

Ok, so one formula checks for C8 = Jones and if it's not Jones, then return whatever is in jones2!d36

Therefore if C8 = Smith, it will return whatever is in jones2!d36.

But the other says that if C8 = Smith, return whatever is in smith1!d36

You can't have it both ways, so you can't combine them.

What do you really want the result to be?


Report •

#2
March 19, 2009 at 15:34:53
C8 is the input cell for names and G8 is the input cell for sequences of which there is "1" and "2".

If c8="jones" and g8=1, I want the target cell to return the contents of Jones1!D36. If c8="jones" and g8=2, I want the target cell to return the contents of Jones2!D36.

If c8="smith" and g8=1, I want the target cell to return the contents of Smith1!D36. If c8="smith" and g8=2, I want the target cell to return the contents of Smith2!D36.

Thank you.


Report •

#3
March 19, 2009 at 19:53:06
=IF(AND(C8="jones",G8=1),Jones1!D36,IF(AND(C8="jones",G8=2),Jones2!D36,IF(AND(etc.

Report •

Related Solutions

#4
March 20, 2009 at 10:00:37
DerbyDad03,

Thanks for your quick response. Based on it, how would I write a formula that has two sets of AND?

Here's the scenario as Green works in all four groups.

C8 is the name input cell; in this case it is Green.
G8 is the sequence cell; I can enter either 1 or 2.
G10 is the schedule cell; I can enter either A or B.

When C8= Green, G8=1 and G10 =A, I need the target cell populated with Green1!A.

When C8= Green, G8=2 and G10=A, I need the target cell populated with Green2!A.

When C8=Green, G8=1 and G10=B, I need the target cell populated with Green1!B.

When C8=Green, G8=2 and G10=B, I need the target cell populated with Green2!B.

I have tried as many variations of the formula you wrote earlier but keep on getting #VALUE errors.

Thank you.


Report •

#5
March 20, 2009 at 10:37:46
Brian, having problems with the site software, it won't do wrapping for some reason, but...

See if this works, because we’ve done this before……


In Response #2 you had the whole thing prototyped:


If c8="jones" and g8=1, I want the target cell to return the contents of Jones1!D36.
If c8="jones" and g8=2, I want the target cell to return the contents of Jones2!D36.
If c8="smith" and g8=1, I want the target cell to return the contents of Smith1!D36.
If c8="smith" and g8=2, I want the target cell to return the contents of Smith2!D36.


Add the AND function around C8 & G8,
replace the word “and” between C8 & G8 with a comma,
delete the verbage and you get:

If(and(c8="jones",g8=1),Jones1!D36,If(and(c8="jones",g8=2),Jones2!D36,
If(and(c8="smith",g8=1),Smith1!D36,If(and(c8="smith",g8=2),Smith2!D36,””))))


If the first condition fails,
do a second if statement,
if the second fails,
do a third,
if the third fails
do a fourth

NOTE: I've split the complete formula because of the wrapping problem, make sure you recombine it into one line.

MIKE


Report •

#6
March 20, 2009 at 16:02:46
Mike,

Thanks for your response. Could you please look at Response Number 4? I can't figure out the syntax when I need to have multiple ANDs?. I tried =if(C8="green",G8 =1, G10 = "A"),Green1!A, etc. but that didn't work.

Thanks,


Report •

#7
March 20, 2009 at 18:37:34
Again, you had it all prototyped

When C8= Green, G8=1 and G10 =A, I need the target cell populated with Green1!A?.

When C8= Green, G8=2 and G10=A, I need the target cell populated with Green2!A?.

When C8=Green, G8=1 and G10=B, I need the target cell populated with Green1!B?.

When C8=Green, G8=2 and G10=B, I need the target cell populated with Green2!B?.


comes out like:


IF(AND(C8=Green,G8=1,G10=A),Green1!A?,

IF(AND(C8=Green,G8=2,G10=A),Green2!A?,

IF(AND(C8=Green,G8=1,G10=B),Green1!B?,

IF(AND(C8=Green,G8=2,G10=B),Green2!B?,""))))

Replace the question mark with a number, IE A1


The hard part is getting the comma's and paren's right.

Don't forget to put it all together on one line.


I'll try below and see if it wraps right:


IF(AND(C8=Green,G8=1,G10=A),Green1!A,IF(AND(C8=Green,G8=2,G10=A),Green2!A,IF(AND(C8=Green,G8=1,G10=B),Green1!B,IF(AND(C8=Green,G8=2,G10=B),Green2!B,""))))


For what ever reason, the forum software is not wrapping the formula.
MIKE


Report •

#8
March 21, 2009 at 07:35:50
Brain, in looking over my response I forgot to quote all the Text, you might want to do that, else you'll surely get an error.

So it should look more like this:

IF(AND(C8="Green",G8=1,G10="A"),Green1!A1,

IF(AND(C8="Green",G8=2,G10="A"),Green2!A2,

IF(AND(C8="Green",G8=1,G10="B"),Green1!B1,

IF(AND(C8="Green",G8=2,G10="B"),Green2!B2,""))))

As always the "the devil is in the detail".....

MIKE


Report •

#9
March 21, 2009 at 08:46:21
Mike,

Thanks for your help but I can't get it to work.

Here's the actual formula:

=IF(AND(C8="kaporch",G8=1),'GK1'!D36,
IF(AND(C8="kaporch",G8=2),'GK2'!D36,
IF(AND(C8="monaghan",G8=1),TM1!D36,
IF(AND(C8="monaghan",G8=2),TM2!D36,
IF(AND(C8="beattie",G8=1,G10="CL"),EB1CL!D36,
IF(AND(C8="beattie",G8=2,G10="CL"),EB2CL!D36,
IF(AND(C8="beattie",G8=1,G10="HN"),EB1HN!D36,
IF(AND(C8="beattie",G8=2,G10="HN"),EB2HN!D36,""))))))))

I checked to see if the formula worked before I add the next IF statement. They worked until I get to the last IF statement which returned an error message. I used eight parens because there are eight IF statements but don't know what else to change.

Thanks.


Report •

#10
March 21, 2009 at 11:51:47
A limitation to Excel is that you cannot "nest" more than 7 IF functions.
By adding the last one, you exceeded the limit.

You'll have to get DerbyDad03 to whip up one of his magical VBA codes for you.

Although there is a way to create a "named function" I have never done it.

MIKE


Report •

#11
March 21, 2009 at 15:17:43
Brian, there are ways to exceed the 7 nested IF limit, and I found this, on YouTube of all places.
See what you think:

http://www.youtube.com/watch?v=5V_J...

MIKE


Report •

#12
March 22, 2009 at 06:04:08
Mike,

Thanks for the info. I tried the CONCATENATE "&" and was able to have more than seven IF statements.

Brian



Report •

#13
March 23, 2009 at 06:46:43
Great....

Glad to hear it.

MIKE


Report •


Ask Question