maximum nested IFs

O

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?
 
J

Joe User

Niek Otten said:
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 -----
 
N

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

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top