Advanced VLOOKUP with multiple values in cell

moakes

New Member
Joined
Nov 5, 2002
Messages
3
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
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

New Member
Joined
Nov 5, 2002
Messages
3
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEFG
148000Sue
2
321000..25999,59000..59999Jim2100025999590009999
437501..37501,48000..38599Sue3750137501480008599
538000..38500,60000..61000Ken38000385006000061000
6fourintermediarycolumns
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 5, 2002
Messages
3
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDE
148002sourcedata
2SueFALSEcriteria
3FALSEresult
4
5zipCodeSalesRep
621000..25999,59000..59999Jim
737501..37501,48000..48099Sue
838000..38500,60000..61000Ken
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
 

Forum statistics

Threads
1,144,274
Messages
5,723,441
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top