Look up valued based on two values

[Guest]

In the company I work for we have group managers who are responsible for
multiple locations. Each location has a 6 digit identification number. All
of the location numbers for a specific group are within a range of values.
An example of this is that all of the locations in Alabama are numbered
between 200500 and 299500.

What I need to be able to do is match a location number within that range of
values and return the group manager's name.

The look up table looks something like this:

Start End Mgr
200500 299500 Abe Alabama
300500 399500 Gordan Gulf
400500 499500 Tom Tennessee
500500 599500 Frank Florida
600500 699500 Grant Georgia
700500 799500 Eddy East

I tried using vlookup with "TRUE" in the "range_lookup" but it didn't quite
give me the results I wanted. Example is that if I try to look up a location
like 400000 it would be a closer match with "Gordan Gulf" and should be "Tom
Tennessee".
Formula I used was: =VLOOKUP(A2,Locations!A2:$C$7,3,TRUE)

In logical terms here is what I want to do:

IF location_number >=start AND location_number <= end THEN lookup
group_manager

Any help you can provide would be appreciated.

Thanks so much,

Scott

 

[Guest]

I'm a bit puzzled by your example....

You're looking up 400000, but that value sits in a gap between two ranges.
It's greater than the 300500-399500 range
but less than the 400500-499500 range

However, it looks like, for your purposes, the ranges are really
200000 299999
300000 399999
400000 499999
500000 599999
600000 699999
700000 799999

Consequently, still using your posted data in A1:C7
Start End Mgr
200500 299500 Abe Alabama
300500 399500 Gordan Gulf
400500 499500 Tom Tennessee
500500 599500 Frank Florida
600500 699500 Grant Georgia
700500 799500 Eddy East

Try something like this:
D1: 400000
E1: =INDEX(C2:C7,MATCH(E1,INDEX(ROUND(A2:A7,-4),0),1))

In this example, the formula returns Tom Tennessee

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

One way (using your range in A1:C6 and check cell = F1- adjust for your
actual):

{=INDEX(C1:C6,MATCH(1,(A1:A6<=F1)*(B1:B6>=F1),0))}

Has to be entered with Crtl-Shift-Enter.

Only thing I don't understand is why, in your example, are you looking up a
location that is not within any of the ranges? If 400000 is a valid lookup,
your ranges shouldn't start at x00500.

HTH

 

[Guest]

That worked perfectly!