maximum nested IFs

[Opal]

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?

 

[Niek Otten]

You omitted "H29942" in the last two

 

[Opal]

D'oh....

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


:-(

 

[Joe User]

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]

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'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

 

[T. Valko]

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

 

[T. Valko]

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