IF Problem (Excel 2002)

[braddy60]

Hi

=IF(C5="Bunzl - BCS",VLOOKUP(B7,DC_BCS,2,IF(C5="Greenhams",VLOOKUP (B7,DC_Greenham,2,0))))

If C5 contains Bunzl - BCS this formula works, However if C5 contains "Greenhams" this returns False, If I use this IF(C5="Greenhams",VLOOKUP(B7,DC_Greenham,2,0)) it returns correctly.

I need to add 4 more IF's to this

Any Ideas please

Braddy

[braddy60]

HI Hans

Thanks for that, I enjoyed the missing comma test <img src=/S/grin.gif border=0 alt=grin width=15 height=15>


Many thanks

Braddy

[HansV]

Oops, corrected now (I hope)

[braddy60]

Hi Hans

I expanded this formula with your help, but I would like to get ISNA into it, because some of the codes are not required to lookup.


=IF(C5="Bunzl - BCS",VLOOKUP(B7,DC_BCS,2,0),IF(C5="Greenhams",VLOO KUP(B7,DC_Greenham,2,0),IF(C5="3663 Others",VLOOKUP(B7,DC_3663,2,0),IF(C5="Bunzl - BCHS",VLOOKUP(B7,DC_BCHS,2,0),"")))

Many thanks

Braddy

[HansV]

=IF(ISNA(IF(C5="Bunzl - BCS",VLOOKUP(B7,DC_BCS,2,0),IF(C5="Greenhams",VLOO KUP(B7,DC_Greenham,2,0),IF(C5="3663 Others",VLOOKUP(B7,DC_3663,2,0),IF(C5="Bunzl - BCHS",VLOOKUP(B7,DC_BCHS,2,0),""))))),"",IF(C5="Bu nzl - BCS",VLOOKUP(B7,DC_BCS,2,0),IF(C5="Greenhams",VLOO KUP(B7,DC_Greenham,2,0),IF(C5="3663 Others",VLOOKUP(B7,DC_3663,2,0),IF(C5="Bunzl - BCHS",VLOOKUP(B7,DC_BCHS,2,0),"")))))

<img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

It's basically a a formula of the form =IF(ISNA(X),"",X) where X is your formula without the = (and with an extra closing parenthesis)

[HansV]

Error corrected by HansV

I think it should be

=IF(C5="Bunzl - BCS",VLOOKUP(B7,DC_BCS,2,0),IF(C5="Greenhams",VLOO KUP(B7,DC_Greenham,2,0),""))

You can replace the "" with further IFs.

[braddy60]

Hi Hans

Thanks for the code and the explanation.


Braddy

[sdckapr]

Another way to do this is to use just a vlookup to get the range name and use the indirect function:

If you create a ookup table for the named formulas:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center valign=bottom>1</td><td valign=bottom>Bunzl - BCS</td><td valign=bottom>DC_BCS</td><td align=center valign=bottom>2</td><td valign=bottom>Greenhams</td><td valign=bottom>DC_Greenham</td><td align=center valign=bottom>3</td><td valign=bottom>3663 Others</td><td valign=bottom>DC_3663</td><td align=center valign=bottom>4</td><td valign=bottom>Bunzl - BCHS</td><td valign=bottom>DC_BCHS</td></table>

Then you can use a formula like:
=IF(ISNA(VLOOKUP(B7,INDIRECT(VLOOKUP(C5,$A$1:$B$4, 2,0)),2,0)),"",VLOOKUP(B7,INDIRECT(VLOOKUP(C5,$A$1 :$B$4,2,0)),2,0))

This won't "choke" with increased IFs. You can have as many "tables" as desired to lookup in. The value in C5 determines what table and the value in B7 gives the value.

Steve

[braddy60]

Hi Steve

Sorry it took so long to reply, thanks for your input.


Braddy

[maxflia10]

=VLOOKUP(B7,CHOOSE(MATCH(D5,{"T1","T2","T3","T4"," T5","T6","T7","T8"},0),T1,T2,T3,T4,T5,T6.T7.T8),2, 0))

Where T1, T2 etc are table names.