Excel - maximum nested IFs

Asked By Opal

09-Feb-10 03:15 PM

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$17,2,FALSE),
IF(C29942="2A",VLOOKUP(H29942,DSVLookup!$D$18:$G$37,2,FALSE),
IF(C29942="3C",VLOOKUP(H29942,DSVLookup!$D$38:$G$53,2,FALSE),
IF(C29942="3D",VLOOKUP(H29942,DSVLookup!$D$54:$G$64,2,FALSE),
IF(C29942="4A",VLOOKUP(H29942,DSVLookup!$D$65:$G$77,2,FALSE),
IF(C29942="5TR1",VLOOKUP(DSVLookup!$D$78:$G$94,2,FALSE),
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?

09-Feb-10 03:20 PM

You omitted "H29942" in the last two

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

09-Feb-10 03:32 PM

D'oh....

Thank you....been looking at it too long

09-Feb-10 03:34 PM

Great!  You spotted it before I could.  I am multitasking at the moment.

One thing:  can we help her eliminate the IF nesting?

it is not necessary.  But it might be helpful to her in the long-run.

There seems to a pattern in the ranges.  I am 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 do not have.  But I will bet you can do this in your sleep.


----- original message -----

09-Feb-10 04:43 PM

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,FALSE)

No provision yet for #NA, but that is 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 will have to adapt the column letters.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

09-Feb-10 05:10 PM

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(C29942,Codes,0),1A,2A,3C,3D,4A,5TR1,5TR2),2,0),"")

--
Biff
Microsoft Excel MVP

09-Feb-10 05:20 PM

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(C29942,Codes,0),Rng1A,Rng2A,Rng3C,Rng3D,Rng4A,Rng5TR1,Rng5TR2),2,0),"")

--
Biff
Microsoft Excel MVP