lookup function Question

[phd4212]

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]

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(B1>30300,B1<=30500),"Bob"))

 

[Tom]

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.

 

[phd4212]

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]

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

 

[Eduardo]

Hi,
use this formula instead of the one sent before

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