Results 1 to 10 of 10
  1. #1
    Silver Lounger
    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

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Braddy
    If you are a fool at forty, you will always be a fool

  3. #3
    Plutonium Lounger
    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)

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

    Braddy
    If you are a fool at forty, you will always be a fool

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

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

  7. #7
    Silver Lounger
    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.


    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    Silver Lounger
    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.


    Braddy
    If you are a fool at forty, you will always be a fool

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

Posting Permissions

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