Lookup Value between 2 ranges
 

I have a table that looks like this.

From To Port#
1 263 1
264 993 1
1100 1368 2
2670 3464 3


I have another table that has random numbers. I need a formula that will
look at the start and end numbers and give me the Port #. For example if
1212 was in e2, i'd want e3 to say "3".

Can anyone help me out?

Lookup Value between 2 ranges
 

If it will be unique, you could use

=(--(A2:A200=start),--(B2:B200<=end),C2:C200)

Your example gives 2 by my calculation.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dominique Feteau" wrote in message
...
I have a table that looks like this.

From To Port#
1 263 1
264 993 1
1100 1368 2
2670 3464 3


I have another table that has random numbers. I need a formula that will
look at the start and end numbers and give me the Port #. For example if
1212 was in e2, i'd want e3 to say "3".

Can anyone help me out?

Lookup Value between 2 ranges
 

Oops, missed the function, and it should be

=SUMPRODUCT(--(A2:A20<=1212),--(B2:B20=1212),C2:C20)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
If it will be unique, you could use

=(--(A2:A200=start),--(B2:B200<=end),C2:C200)

Your example gives 2 by my calculation.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dominique Feteau" wrote in message
...
I have a table that looks like this.

From To Port#
1 263 1
264 993 1
1100 1368 2
2670 3464 3


I have another table that has random numbers. I need a formula that
will
look at the start and end numbers and give me the Port #. For example
if
1212 was in e2, i'd want e3 to say "3".

Can anyone help me out?