Question about identifying a value based on a range.

[rgrocks]

I have two tables,

one table (TABLE1) contains the name and a mile point a

the other table (TABLE2) contains name, mile point 1, mile point 2, and other data

I want to be able to pull data from TABLE2, based on TABLE1's name and MP. I want to be able to select data from TABLE2 whose name and mile point's fit with the data from TABLE1

Line	MP A	SYS
100	8	?

TABLE2

SYS	Line	MP1	MP2	DATA
A	100	0	5	BOB
A	100	5	10	KON
B	100	10	15	B

i want to be able to replace the ? from table1 with the correct SYS from table2 based on the MP given

i started with something like
=if(and(name=name,mpa=>mp1,mpa=<mp2)),vlookup...

the problem is this doesnt work because im comparing one to many.

[AliGW]

Where in table 2 is MPA? What would you expect the answer in table 1 to be and why?

[rgrocks]

MPA exist in table 1. MPA's value will fall inbetween MP1 and MP2 in TABLE 2. In this case since MPA is 8 and Line is 100, it falls between 5 and 10 on Line 100 in Table 2. I want Table 1 to return the SYS value "A"

[rgrocks]

Maybe I'm doing a poor job explaining.

TABLE1 has the identifying information, in this case I have a name and mile point (MPa)

TABLE2 has name and a range of mile points (MP1 and MP2)

I want to use name from TABLE1 to match with name from TABLE2, but there is alot of instances of the same name in TABLE2 (one to many)

Then I want to single out the specific record from TABLE2 that the record from TABLE1 implies by using MPa.

MPa should fall inbetween MP1 and MP2 from TABLE2. In the example above, this would correlate the 2nd record from TABLE2 because MPa=8 and Name=100, which falls between MP1 and MP2 only (5 and 10) with the same name=100.

Does this make more sense?