how to look up an approximate value in two columns
 

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

how to look up an approximate value in two columns
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Deise BR" wrote:
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

how to look up an approximate value in two columns
 

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

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Deise BR" wrote:
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

how to look up an approximate value in two columns
 

Delighted to hear that, Deise.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Deise BR" wrote in message
...
Thank you, Max!! This is exactly what I needed.