Hi,

I am currently in charge of creating a spreading sheet where certain groups
of zip codes correspond to individual salesmen (e.g. zips 30000-30300 are for
Bob and 30500-30700) are for John. Our database records the 5 digit zip
code, while our territories are broken down into the first 3 digits. How can
I create a lookup function to tell me that zip code 30301 is in Bob's region
and 30507 is in John's territory?

Thanks

Hi,
Let' say your zip codes are in column B and you want to know who's region
is it in another column enter

=if(and(B1=30000,B1=<30300),"John",if(and(B13030 0,B1<=30500),"Bob"))

"phd4212" wrote:

Hi,

I am currently in charge of creating a spreading sheet where certain groups
of zip codes correspond to individual salesmen (e.g. zips 30000-30300 are for
Bob and 30500-30700) are for John. Our database records the 5 digit zip
code, while our territories are broken down into the first 3 digits. How can
I create a lookup function to tell me that zip code 30301 is in Bob's region
and 30507 is in John's territory?

Thanks

Eduardo's solution works, but it depends on how many salespeople you have.
Another option is to create a table with the zip code a salesperson starts
with in the A column, and the salesperson name in the B column.

Then, a simple V lookup function could determine which salesperson the zip
refers to. This works best with many salespeople.
A B
30000 Mary
30300 Bob
30500 John

=VLOOKUP(30299,A1:B3,2,TRUE)
Here, zip 30299 would result in Mary,
zip 30501 give you John, etc.

Eduardo, thanks for the help but I suppose I oversimplified this query. There
are many more zip codes and they are not as neat as the example--so
30000-30300 might be bobs, but so are 31900,30900,30100,31300 and John has
30500-30700, 31100, 31500 and 32100
That seems like too complicated an if statement, especially if there are
more than 2 salesmen. I'd assume there is an easier way...

"Eduardo" wrote:

Hi,
Let' say your zip codes are in column B and you want to know who's region
is it in another column enter

=if(and(B1=30000,B1=<30300),"John",if(and(B13030 0,B1<=30500),"Bob"))

"phd4212" wrote:

Hi,

I am currently in charge of creating a spreading sheet where certain groups
of zip codes correspond to individual salesmen (e.g. zips 30000-30300 are for
Bob and 30500-30700) are for John. Our database records the 5 digit zip
code, while our territories are broken down into the first 3 digits. How can
I create a lookup function to tell me that zip code 30301 is in Bob's region
and 30507 is in John's territory?

Thanks

Hi,
ok you have in sheet 2 a list of zip codes in column A and in column B who
is in charge of that area, then in sheet 1 you want to enter in A2 a zip code
and in B2 to see who is in charge, if that is correct use

=INDEX(Sheet2!B10:B13,MATCH(D9,sheet2!A10:A13,0))

change range or sheet name to fit your needs

"phd4212" wrote:

Eduardo, thanks for the help but I suppose I oversimplified this query. There
are many more zip codes and they are not as neat as the example--so
30000-30300 might be bobs, but so are 31900,30900,30100,31300 and John has
30500-30700, 31100, 31500 and 32100
That seems like too complicated an if statement, especially if there are
more than 2 salesmen. I'd assume there is an easier way...

"Eduardo" wrote:

Hi,
Let' say your zip codes are in column B and you want to know who's region
is it in another column enter

=if(and(B1=30000,B1=<30300),"John",if(and(B13030 0,B1<=30500),"Bob"))

"phd4212" wrote:

Hi,

I am currently in charge of creating a spreading sheet where certain groups
of zip codes correspond to individual salesmen (e.g. zips 30000-30300 are for
Bob and 30500-30700) are for John. Our database records the 5 digit zip
code, while our territories are broken down into the first 3 digits. How can
I create a lookup function to tell me that zip code 30301 is in Bob's region
and 30507 is in John's territory?

Thanks

Hi,
use this formula instead of the one sent before

=INDEX(sheet2!B1:B100,MATCH(B2,sheet2!A1:A100,0))

"phd4212" wrote:

Eduardo, thanks for the help but I suppose I oversimplified this query. There
are many more zip codes and they are not as neat as the example--so
30000-30300 might be bobs, but so are 31900,30900,30100,31300 and John has
30500-30700, 31100, 31500 and 32100
That seems like too complicated an if statement, especially if there are
more than 2 salesmen. I'd assume there is an easier way...

"Eduardo" wrote:

Hi,
Let' say your zip codes are in column B and you want to know who's region
is it in another column enter

=if(and(B1=30000,B1=<30300),"John",if(and(B13030 0,B1<=30500),"Bob"))

"phd4212" wrote:

Hi,

I am currently in charge of creating a spreading sheet where certain groups
of zip codes correspond to individual salesmen (e.g. zips 30000-30300 are for
Bob and 30500-30700) are for John. Our database records the 5 digit zip
code, while our territories are broken down into the first 3 digits. How can
I create a lookup function to tell me that zip code 30301 is in Bob's region
and 30507 is in John's territory?

Thanks