Results 1 to 10 of 10
Thread: IF Problem (Excel 2002)

20050905, 13:21 #1
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
IF Problem (Excel 2002)
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
BraddyIf you are a fool at forty, you will always be a fool

20050905, 13:38 #2
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: IF Problem (Excel 2002)
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
BraddyIf you are a fool at forty, you will always be a fool

20050905, 13:40 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: IF Problem (Excel 2002)
Oops, corrected now (I hope)

20050906, 07:43 #4
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: IF Problem (Excel 2002)
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
BraddyIf you are a fool at forty, you will always be a fool

20050906, 07:53 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: IF Problem (Excel 2002)
=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)

20050906, 07:53 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: IF Problem (Excel 2002)
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.

20050906, 08:06 #7
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: IF Problem (Excel 2002)
Hi Hans
Thanks for the code and the explanation.
BraddyIf you are a fool at forty, you will always be a fool

20050906, 13:51 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: IF Problem (Excel 2002)
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

20050907, 08:04 #9
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: IF Problem (Excel 2002)
Hi Steve
Sorry it took so long to reply, thanks for your input.
BraddyIf you are a fool at forty, you will always be a fool

20050907, 08:12 #10
 Join Date
 Feb 2003
 Posts
 89
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: IF Problem (Excel 2002)
=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.