how to look up an approximate value in two columns

[Guest]

Hello,

I have a zip code table and need to look up a number in a range (column from
and column to) to return the transit time. The content is not necessarily
exact. Example:

A B C
1 Zip From Zip To Transit Time
2 1000000 9999000 1
3 10000000 19999999 3
4 20000000 23869999 5
5 23870000 23999999 4
6 24000000 24799999 3
7 24800000 24999999 8

The number to look up is 20771004. It would fit in line 4 (higher than
20000000 and lesser than 23869999). My returned transit time is 5.

Is there a way to put this into a formula? Almost 50k lines to look up.

Thanks for any idea or help in advance!
Deise

 

[Guest]

Assume lookup values are listed in E1 down
put this in F1, then array-enter the formula by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER:
=INDEX(C$2:C$50000,MATCH(1,(E1>=A$2:A$50000)*(E1<=B$2:B$50000),0))
Copy F1 down. Adapt the ranges to suit.

 

[Guest]

Thak you, Max!! This is exactly what I needed.

Assume lookup values are listed in E1 down
put this in F1, then array-enter the formula by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER:
=INDEX(C$2:C$50000,MATCH(1,(E1>=A$2:A$50000)*(E1<=B$2:B$50000),0))
Copy F1 down. Adapt the ranges to suit.

 

[Max]

Delighted to hear that, Deise.