how to look up an approximate value in two columns

D

Deise BR

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
 
M

Max

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.
 
D

Deise BR

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

Max said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top