# Thread: IF Problem (Excel 2002)

1. ## 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

2. ## 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

3. ## Re: IF Problem (Excel 2002)

Oops, corrected now (I hope)

4. ## 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

5. ## 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)

6. ## 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.

7. ## Re: IF Problem (Excel 2002)

Hi Hans

Thanks for the code and the explanation.

8. ## 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

Hi Steve

10. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•