Advanced VLOOKUP with multiple values in cell

[moakes]

I understand and have used basic VLOOKUP. This one is (at least to me) a bit trickier. The source ss is a listing of zipcode ranges (column 1) with their corresponding sales reps (column2). Now, I want to be able to enter a zipcode and have it return the name of the corresponding sales rep. No problem if EVERY zipcode is listed, but there are thousands. Also, they are NOT in a nice sorted logical order. AND there are "ranges" in each cell. A sample of the data is below. SO, is there anyway to lookup a range within a cell???? Make sense? Or any other ideas to take this EXISTING ss and make it usable

COL1 COL2
21000..25999, 59000..59999 Jim
37501..37501,48000..38599 Sue
38000..38500, 60000..61000 Ken

Notes:
1. there are multiple zipcode ranges in each cell
2.In each cell the zipcode ranges are separated beg to end with a ".."
3.the ranges are then separate with a ","
4.I could sort COL2 based on the value of the FIRST entry, but don't think this helps because within a cell there are many ranges, HOWEVER, within a cell the ranges are in lowest to highest order.

 

[maxflia10]

Is there more than one salesperson per zip code? If not a column of zip codes and a column of corresponding salesperson would be the simplest.

 

[moakes]

Thanks, maxflia10. Yes, I agree that would make it very simple. However, three problems: #1. There are THOUSANDS of zips in the US, so it would require a SS thousands of rows long with only a handfull of reps, which would be OK, but #2. the main issue is this is the format that I was given from our finance department, and it is very specific and accurate, so I'm trying to see if there is a way to look at that data the way it is already formatted. #3. This lookup ss will change quarterly, so I need to be able to use the new table I will be given . . which will be in the same format. Any ideas? Thanks again. MO.

 

[Yogi Anand]

Hi moakes:

Welcome to the Board!

Is the ZipCode_Salesrep table fixed as you have depicted, with every line having two sets of zip code ranges separated by commas?

Using the structure as you have depicted, we do have a starting point ... I have started with introducing four intermediary columns with formulas (that can be hidden) to help lookup the salesrep's name.

If this works for you, and if the need be, we can simplify the process further. For now, see the following simulation

Book1
	A	B	C	D	E	F	G
1	48000	Sue
2
3	21000..25999,59000..59999	Jim		21000	25999	59000	9999
4	37501..37501,48000..38599	Sue		37501	37501	48000	8599
5	38000..38500,60000..61000	Ken		38000	38500	60000	61000
6				fourintermediarycolumns
7
Sheet6

</SPAN>

Please post back if it works for you -- otherwise explain a little further and let us take it from there.

Regards!
Yogi Anand

Hi moakes: as I mentioned it is a starting point, we still have to take care of looking up salerep's name for a range of zipcode values -- so please keep in mind this is toward's building the solution -- this is not the final solution yet!

Edit: Hi moakes ... I have just posted a solution using DGET function -- I prefer that one instead of continuing to develop this one further.
This message was edited by Yogi Anand on 2002-11-06 18:03

 

[Dave Patton]

On 2002-11-06 15:07, moakes wrote:
Thanks, maxflia10. Yes, I agree that would make it very simple. However, three problems: #1. There are THOUSANDS of zips in the US, so it would require a SS thousands of rows long with only a handfull of reps, which would be OK, but #2. the main issue is this is the format that I was given from our finance department, and it is very specific and accurate, so I'm trying to see if there is a way to look at that data the way it is already formatted. #3. This lookup ss will change quarterly, so I need to be able to use the new table I will be given . . which will be in the same format. Any ideas? Thanks again. MO.

can you carefully structure the lookup table
and use a regular lookup such as

25000 Jim =VLOOKUP(A1,A3:B11,2)

21000 Jim
26000 XXX
37000 Sue
37502 XXX
38000 Ken
48000 Sue
59000 Jim
60000 Ken
61001 XXX

You could consider a function from Morefunc a free Add-in to help extract the numbers

Example for just the first number

=WMID(WMID($D$3,1,1,","),1,1,"..")


You can set the upper limits and assign a name like XXX so extraneous areas do not get asigned to people.
This message was edited by Dave patton on 2002-11-06 15:34

 

[moakes]

Thanks, Yogi.
#1. No, each cell could have just one, OR many (say 20) zip code ranges.
#2. You have given me something to think about, I'm going to go check the usage of VALUE, MATCH and INDEX so understand your beginning solution better.
THANKS!

 

[Yogi Anand]

Hi moakes:

Please look at the use of DGET function. I have used the DGET function on your sample data to accomplish what you want ... see the following simulation:

Book1
	A	B	C	D	E
1	48002				sourcedata
2	Sue	FALSE			criteria
3		FALSE			result
4
5	zipCode	SalesRep
6	21000..25999,59000..59999	Jim
7	37501..37501,48000..48099	Sue
8	38000..38500,60000..61000	Ken
Sheet6 (3)

</SPAN>

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!
Yogi Anand

Edit: In reference to your statement in your last posting, use of many ranges in a row of the zipcode_salesrep table should not be a problem in using the DGET function approach.
This message was edited by Yogi Anand on 2002-11-06 18:00