Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Lookup Table / (Excel 2003)

    Hi

    Hans kindly showed me how to create a lookup table, I am trying to re-create it in the attached workbook rows 5,6,7 work then I get a #ref error.

    Many Thanks

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Table / (Excel 2003)

    In G5 to G7, VLOOKUP(A6,status,2,0) evaluates to "distr" which is the name of a defined range (see Insert | Name | Define). Hence INDIRECT(VLOOKUP(A6,status,2,0)) results in a reference to the "distr" range.
    In G8, however, VLOOKUP(A6,status,2,0) evaluates to "Dump Acc" which is NOT the name of a defined range. Hence INDIRECT(VLOOKUP(A6,status,2,0)) results in a #REF error.

    I don't understand what you want to accomplish with this formula. so I cannot suggest an improvement.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lookup Table / (Excel 2003)

    Hi Hans

    What I am trying to achieve is the following.

    If Mkt Sector = 275 or 265 or 17Y, or 56 then Status should = Distr, If Key Account3 = 17675, or another 13 numbers then status should = Dump Acc, If QS>0 and NIV = 0 then Status should = Free/NSI, else Keep.

    I hope you can understand this

    Thanks

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Table / (Excel 2003)

    What are Mkt Sector, Key Account3, QS and NIV? There is no mention of them anywhere in the spreadsheet...

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lookup Table / (Excel 2003)

    Hi Hans

    I have attached the few rows from the actual Workbook.

    Thanks

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Table / (Excel 2003)

    Sorry, I still don't understand. In row 3 of the workbook, Key Accounts 3 is 17675, so according to your description, Status should be "Dump Acc", but in the same row, Mkt Sector is 275, so Status should be "Distr". I'm confused.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lookup Table / (Excel 2003)

    Hi Hans

    Thats because they were entered manually this is why I would like the code, I am looking for to automate them.

    I have attached again with nothing in the status.

    Thanks

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Table / (Excel 2003)

    Still confused. Your description of what the status should be is not clear.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lookup Table / (Excel 2003)

    Hi Hans

    Please see Attached.

    Thanks for your continued patience.

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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Table / (Excel 2003)

    Should the second and third IF in your description be ELSE IF ?

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lookup Table / (Excel 2003)

    Hi Hans

    I am not really sure but it sounds like it should because the all the ifs are required in each row ???

    Regards

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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Table / (Excel 2003)

    Unless you can give a clear and unambiguous description of the precedence of the various rules, it is impossible to come up with a solution.

  13. #13
    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: Lookup Table / (Excel 2003)

    How about this. First check is in COl A, if not a match then chec in D if not A or D, then check the G.H combo, if none then use the else:

    =IF(OR(A2=17675,A2=17933,A2=18244),"Dump Acc",IF(OR(D2=275,D2=265,D2="17Y",D2=56),"Distr",I F(AND(G2>0,H2>0),"Free/NSI","Keep")))

    Steve

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lookup Table / (Excel 2003)

    Hi Hans

    I will try to explain the precedence.

    IF(C5=275,"Distr",IF(C5=265,"Distr",IF(C5="17Y","D istr",IF(C5=56,"Distr",IF(and(G5>0 H5=0,"Free/NSI","Keep"))))))

    Keep is if none of the IF's apply

    I have left out the Key Account3 because there are to many Variables


    Sorry to try your patience

    Many Thanks

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

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lookup Table / (Excel 2003)

    Hi Steve

    That works just fine, I wonder if I could impose on you to take out the the IF(OR(A2=17675,A2=17933,A2=18244),"Dump Acc, as there will be a lot more numbers added to this

    and I will have to use a seperate column for this.

    Many Thanks

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

Page 1 of 2 12 LastLast

Posting Permissions

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