# 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

#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!d36Therefore if C8 = Smith, it will return whatever is in jones2!d36.But the other says that if C8 = Smith, return whatever is in smith1!d36You 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 prototypedWhen 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 A1The 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