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

    Another Lookup Problem (Excel 2002)

    Hi

    I have another lookup problem I am struggling with.

    Please see attached sheet.

    Many Thanks

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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Another Lookup Problem (Excel 2002)

    If you always have 4 classes for each market sector, you could use the attached. It assumes the max values are always in descending order for each sector.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Another Lookup Problem (Excel 2002)

    Is there any reason not to use the VLOOKUP() function? I find VLOOKUP extremely useful because it doesn't require your lookup table to be in order (as long as the fourth argument is FALSE). The attached spreadsheet will demonstrate. Note - depending on your use of this file, you may want to modify the formula to deal with instances where the lookup value is not found (as in Row 14).
    Aloha,
    John Jacobson

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Another Lookup Problem (Excel 2002)

    Sorry, I SOOO missed the mark. disregard previous post.
    John Jacobson

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Another Lookup Problem (Excel 2002)

    [Edit: by the time I posted, John had already realised this! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>]

    John,
    The problem with vlookup as you've used it is that there are bands within each sector whereas your vlookup just returns the first band (A). Braddy needs the formula to look up the sector from column D and then look at the value in column E to determine the band based on the min value to max value ranges in the table sheet for that sector. For example, for row 2 it has to look up retail in the table (there are 4 rows for that) then look at the value for Annualised NPS and see which of the four bands for retail that fits into.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Another Lookup Problem (Excel 2002)

    Hi Rory

    Your suggestion is perfect for my demo model, and also works fine when I used it on my actual model.

    Just one other point the formula is based worksheet names which are different in my actual model, not a problem I just changed them.

    However I need to use this formula on several different worksheets, this means changing the formula to the name of the other worksheets.

    Which means changing the formula each time, is this the only way this can be achieved?

    Many Thanks

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

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

    Re: Another Lookup Problem (Excel 2002)

    Hi John

    Thanks for trying

    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: Another Lookup Problem (Excel 2002)

    You can omit the name of the worksheet that contains the formulas - Data in the sample workbook, e.g. in cell F2:

    =CHOOSE(MATCH(E2,OFFSET(Table!$B$2,MATCH(D2,Table! $B$3:$B$30,0),2,COUNTIF(Table!$B$3:$B$30,D2),1),-1),"A","B","C","D")

    You can use Edit | Replace to change the name of the worksheet containing the table in all formulas at once.

    (It would be possible to place the name of the Table worksheet in a cell and use the INDIRECT function, but it makes the formulas much more complicated than they already are, and recalculation would become much slower.)

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Another Lookup Problem (Excel 2002)

    You could use a named range in place of the Table!$B$3:$B$30 - e.g. call that range DataTable and then substitute that into the formula. If you need to do this on multiple sheets within the same workbook, you would need to make the range name local to the worksheet you use it on - i.e. name it something like Data!DataTable.
    In the attached version, you will notice there are two ranges called DataTable, one local to the Data sheet and one to the Table sheet. The formula on the Data sheet will use the name local to it by default.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Another Lookup Problem (Excel 2002)

    Hi Rory

    I would like to apologise to you and to Hans for taking so long to reply, but I have been away for an electronic free weekend, I thank you both for your replies.

    I will be looking at them tomorrow.

    Thanks again

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

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

    Re: Another Lookup Problem (Excel 2002)

    Hi Rory

    I just got round to using your suggestion, it works very well thank you.

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

Posting Permissions

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