|
maximum nested IFs
Help.... I understand that Excel only allows for 7
nested IF statements.... I have 7, but the following responds with #VALUE! for the last 2 items in my function: =IF(C29942="1A",VLOOKUP(H29942,DSVLookup!$D$2:$G$1 7,2,FALSE), IF(C29942="2A",VLOOKUP(H29942,DSVLookup!$D$18:$G$3 7,2,FALSE), IF(C29942="3C",VLOOKUP(H29942,DSVLookup!$D$38:$G$5 3,2,FALSE), IF(C29942="3D",VLOOKUP(H29942,DSVLookup!$D$54:$G$6 4,2,FALSE), IF(C29942="4A",VLOOKUP(H29942,DSVLookup!$D$65:$G$7 7,2,FALSE), IF(C29942="5TR1",VLOOKUP(DSVLookup!$D$78:$G$94,2,F ALSE), IF(C29942="5TR2",VLOOKUP(DSVLookup!$D$95:$G$122,2, FALSE),""))))))) It works fine on 1A, 2A, 3C, 3D and 4A, but I get that error for the last two. What am I doing wrong? |
maximum nested IFs
You omitted "H29942" in the last two
-- Kind regards, Niek Otten Microsoft MVP - Excel "Opal" wrote in message ... Help.... I understand that Excel only allows for 7 nested IF statements.... I have 7, but the following responds with #VALUE! for the last 2 items in my function: =IF(C29942="1A",VLOOKUP(H29942,DSVLookup!$D$2:$G$1 7,2,FALSE), IF(C29942="2A",VLOOKUP(H29942,DSVLookup!$D$18:$G$3 7,2,FALSE), IF(C29942="3C",VLOOKUP(H29942,DSVLookup!$D$38:$G$5 3,2,FALSE), IF(C29942="3D",VLOOKUP(H29942,DSVLookup!$D$54:$G$6 4,2,FALSE), IF(C29942="4A",VLOOKUP(H29942,DSVLookup!$D$65:$G$7 7,2,FALSE), IF(C29942="5TR1",VLOOKUP(DSVLookup!$D$78:$G$94,2,F ALSE), IF(C29942="5TR2",VLOOKUP(DSVLookup!$D$95:$G$122,2, FALSE),""))))))) It works fine on 1A, 2A, 3C, 3D and 4A, but I get that error for the last two. What am I doing wrong? |
maximum nested IFs
D'oh....
Thank you....been looking at it too long :-( :-) |
maximum nested IFs
"Niek Otten" wrote:
You omitted "H29942" in the last two Great! You spotted it before I could. I'm multitasking at the moment. One thing: can we help her eliminate the IF nesting? It's not necessary. But it might be helpful to her in the long-run. There seems to a pattern in the ranges. I'm thinking of using INDIRECT and CHOOSE within the VLOOKUP. If we can avoid volatile functions like INDIRECT, so much the better. It would take me some time to work that out, which I don't have. But I'll bet you can do this in your sleep. ----- original message ----- "Niek Otten" wrote in message ... You omitted "H29942" in the last two -- Kind regards, Niek Otten Microsoft MVP - Excel "Opal" wrote in message ... Help.... I understand that Excel only allows for 7 nested IF statements.... I have 7, but the following responds with #VALUE! for the last 2 items in my function: =IF(C29942="1A",VLOOKUP(H29942,DSVLookup!$D$2:$G$1 7,2,FALSE), IF(C29942="2A",VLOOKUP(H29942,DSVLookup!$D$18:$G$3 7,2,FALSE), IF(C29942="3C",VLOOKUP(H29942,DSVLookup!$D$38:$G$5 3,2,FALSE), IF(C29942="3D",VLOOKUP(H29942,DSVLookup!$D$54:$G$6 4,2,FALSE), IF(C29942="4A",VLOOKUP(H29942,DSVLookup!$D$65:$G$7 7,2,FALSE), IF(C29942="5TR1",VLOOKUP(DSVLookup!$D$78:$G$94,2,F ALSE), IF(C29942="5TR2",VLOOKUP(DSVLookup!$D$95:$G$122,2, FALSE),""))))))) It works fine on 1A, 2A, 3C, 3D and 4A, but I get that error for the last two. What am I doing wrong? |
maximum nested IFs
Since the reference to the table is 4 columns, while only two are used, I
assume that it is some existing table which cannot be changed. What I would do is add two columns in front of that table. First column (B): Rows 2-17 contain "1A", rows 18-37 contain "2A", etc. Second column: (C) =B2&D2, fill down to row 53 Lookup Formula: =VLOOKUP(C29942&H29942,DSVLookup!$C$2:$E$53,3,FALS E) No provision yet for #NA, but that's simple; =IF(ISNA(YourFormula),"",YourFormula). In Excel2007: =IFERROR(YourFormula,"") Also, if you had to insert columns because there was no empty spaceto the left of the table, you'll have to adapt the column letters. -- Kind regards, Niek Otten Microsoft MVP - Excel "Joe User" <joeu2004 wrote in message ... "Niek Otten" wrote: You omitted "H29942" in the last two Great! You spotted it before I could. I'm multitasking at the moment. One thing: can we help her eliminate the IF nesting? It's not necessary. But it might be helpful to her in the long-run. There seems to a pattern in the ranges. I'm thinking of using INDIRECT and CHOOSE within the VLOOKUP. If we can avoid volatile functions like INDIRECT, so much the better. It would take me some time to work that out, which I don't have. But I'll bet you can do this in your sleep. ----- original message ----- "Niek Otten" wrote in message ... You omitted "H29942" in the last two -- Kind regards, Niek Otten Microsoft MVP - Excel "Opal" wrote in message ... Help.... I understand that Excel only allows for 7 nested IF statements.... I have 7, but the following responds with #VALUE! for the last 2 items in my function: =IF(C29942="1A",VLOOKUP(H29942,DSVLookup!$D$2:$G$1 7,2,FALSE), IF(C29942="2A",VLOOKUP(H29942,DSVLookup!$D$18:$G$3 7,2,FALSE), IF(C29942="3C",VLOOKUP(H29942,DSVLookup!$D$38:$G$5 3,2,FALSE), IF(C29942="3D",VLOOKUP(H29942,DSVLookup!$D$54:$G$6 4,2,FALSE), IF(C29942="4A",VLOOKUP(H29942,DSVLookup!$D$65:$G$7 7,2,FALSE), IF(C29942="5TR1",VLOOKUP(DSVLookup!$D$78:$G$94,2,F ALSE), IF(C29942="5TR2",VLOOKUP(DSVLookup!$D$95:$G$122,2, FALSE),""))))))) It works fine on 1A, 2A, 3C, 3D and 4A, but I get that error for the last two. What am I doing wrong? |
maximum nested IFs
can we help her eliminate the IF nesting?
I'm thinking of using...CHOOSE within the VLOOKUP Using a bunch of defined names we can reduce that considerably. Defined names: Codes Refers to: ={"1A","2A","3C","3D","4A","5TR1","5TR2"} 1A Refers to: =DSVLookup!$D$2:$G$17 2A Refers to: =DSVLookup!$D$18:$G$37 3C Refers to: =DSVLookup!$D$38:$G$53 3D Refers to: =DSVLookup!$D$54:$G$64 4A Refers to: =DSVLookup!$D$65:$G$77 5TR1 Refers to: =DSVLookup!$D$78:$G$94 5TR2 Refers to: =DSVLookup!$D$95:$G$122 Then: =IF(OR(C29942=Codes),VLOOKUP(H29942,CHOOSE(MATCH(C 29942,Codes,0),1A,2A,3C,3D,4A,5TR1,5TR2),2,0),"") -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "Niek Otten" wrote: You omitted "H29942" in the last two Great! You spotted it before I could. I'm multitasking at the moment. One thing: can we help her eliminate the IF nesting? It's not necessary. But it might be helpful to her in the long-run. There seems to a pattern in the ranges. I'm thinking of using INDIRECT and CHOOSE within the VLOOKUP. If we can avoid volatile functions like INDIRECT, so much the better. It would take me some time to work that out, which I don't have. But I'll bet you can do this in your sleep. ----- original message ----- "Niek Otten" wrote in message ... You omitted "H29942" in the last two -- Kind regards, Niek Otten Microsoft MVP - Excel "Opal" wrote in message ... Help.... I understand that Excel only allows for 7 nested IF statements.... I have 7, but the following responds with #VALUE! for the last 2 items in my function: =IF(C29942="1A",VLOOKUP(H29942,DSVLookup!$D$2:$G$1 7,2,FALSE), IF(C29942="2A",VLOOKUP(H29942,DSVLookup!$D$18:$G$3 7,2,FALSE), IF(C29942="3C",VLOOKUP(H29942,DSVLookup!$D$38:$G$5 3,2,FALSE), IF(C29942="3D",VLOOKUP(H29942,DSVLookup!$D$54:$G$6 4,2,FALSE), IF(C29942="4A",VLOOKUP(H29942,DSVLookup!$D$65:$G$7 7,2,FALSE), IF(C29942="5TR1",VLOOKUP(DSVLookup!$D$78:$G$94,2,F ALSE), IF(C29942="5TR2",VLOOKUP(DSVLookup!$D$95:$G$122,2, FALSE),""))))))) It works fine on 1A, 2A, 3C, 3D and 4A, but I get that error for the last two. What am I doing wrong? |
maximum nested IFs
Well Doh!
Disregard all those names for the named ranges. They're invalid names!!! If you wanted to name them something like Rng1A, Rng2A, Rng3C, etc. Then: =IF(OR(C29942=Codes),VLOOKUP(H29942,CHOOSE(MATCH(C 29942,Codes,0),Rng1A,Rng2A,Rng3C,Rng3D,Rng4A,Rng5T R1,Rng5TR2),2,0),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... can we help her eliminate the IF nesting? I'm thinking of using...CHOOSE within the VLOOKUP Using a bunch of defined names we can reduce that considerably. Defined names: Codes Refers to: ={"1A","2A","3C","3D","4A","5TR1","5TR2"} 1A Refers to: =DSVLookup!$D$2:$G$17 2A Refers to: =DSVLookup!$D$18:$G$37 3C Refers to: =DSVLookup!$D$38:$G$53 3D Refers to: =DSVLookup!$D$54:$G$64 4A Refers to: =DSVLookup!$D$65:$G$77 5TR1 Refers to: =DSVLookup!$D$78:$G$94 5TR2 Refers to: =DSVLookup!$D$95:$G$122 Then: =IF(OR(C29942=Codes),VLOOKUP(H29942,CHOOSE(MATCH(C 29942,Codes,0),1A,2A,3C,3D,4A,5TR1,5TR2),2,0),"") -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "Niek Otten" wrote: You omitted "H29942" in the last two Great! You spotted it before I could. I'm multitasking at the moment. One thing: can we help her eliminate the IF nesting? It's not necessary. But it might be helpful to her in the long-run. There seems to a pattern in the ranges. I'm thinking of using INDIRECT and CHOOSE within the VLOOKUP. If we can avoid volatile functions like INDIRECT, so much the better. It would take me some time to work that out, which I don't have. But I'll bet you can do this in your sleep. ----- original message ----- "Niek Otten" wrote in message ... You omitted "H29942" in the last two -- Kind regards, Niek Otten Microsoft MVP - Excel "Opal" wrote in message ... Help.... I understand that Excel only allows for 7 nested IF statements.... I have 7, but the following responds with #VALUE! for the last 2 items in my function: =IF(C29942="1A",VLOOKUP(H29942,DSVLookup!$D$2:$G$1 7,2,FALSE), IF(C29942="2A",VLOOKUP(H29942,DSVLookup!$D$18:$G$3 7,2,FALSE), IF(C29942="3C",VLOOKUP(H29942,DSVLookup!$D$38:$G$5 3,2,FALSE), IF(C29942="3D",VLOOKUP(H29942,DSVLookup!$D$54:$G$6 4,2,FALSE), IF(C29942="4A",VLOOKUP(H29942,DSVLookup!$D$65:$G$7 7,2,FALSE), IF(C29942="5TR1",VLOOKUP(DSVLookup!$D$78:$G$94,2,F ALSE), IF(C29942="5TR2",VLOOKUP(DSVLookup!$D$95:$G$122,2, FALSE),""))))))) It works fine on 1A, 2A, 3C, 3D and 4A, but I get that error for the last two. What am I doing wrong? |
| All times are GMT +1. The time now is 04:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com