# Thread: Another Lookup Problem (Excel 2002)

1. ## Another Lookup Problem (Excel 2002)

Hi

I have another lookup problem I am struggling with.

Many Thanks

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

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

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

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

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

7. ## Re: Another Lookup Problem (Excel 2002)

Hi John

Thanks for trying

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

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

11. ## Re: Another Lookup Problem (Excel 2002)

Hi Rory

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