Results 1 to 11 of 11

20051013, 12:32 #1
 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
BraddyIf you are a fool at forty, you will always be a fool

20051013, 13:15 #2
 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

20051013, 22:25 #3
 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

20051013, 22:28 #4
 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

20051013, 22:34 #5
 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

20051014, 08:09 #6
 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
BraddyIf you are a fool at forty, you will always be a fool

20051014, 08:10 #7
 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
BraddyIf you are a fool at forty, you will always be a fool

20051014, 09:42 #8
 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.)

20051014, 09:57 #9
 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

20051017, 15:15 #10
 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
BraddyIf you are a fool at forty, you will always be a fool

20051018, 12:38 #11
 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.
BraddyIf you are a fool at forty, you will always be a fool