I compiled a large database of city blocks in an area. Now I need to be able
to pull information from a city block by entering a street address on that
block.
The database looks something like this:
A B C D-E-F
1 From To Street Name Various info. regarding block
2 2000 2099 Webster
3 2100 2199 Webster
4 2200 2099 Webster
5 2000 2099 Clybourn
6 2100 2199 Clybourn
7 2200 2099 Clybourn

I need to write up a formula so that when I write:
A B
8 2134 Damen
The formula searches to determine which range the number falls in and what
street, to give me the answer:
A
9 3 €“ for row 3
Then I could use the row number and run the INDEX function to fill the
information from cells D-F for that row.

Ive tried using the MATCH function in an array formula, but have not been
able to get it to work. I need to be able to test if A8=A2:A7 and A8<=B2:B7
(though you flip this in an array formula) and match it up to B8=C2:C7.

Is there a way to do this without using macros?

Any help on this would be extremely appreciated!

=MATCH(1,(A2:A7<=A8)*(B2:B7=A8),0)

ctrl+shift+enter, not just enter


"Patryk" wrote:

I compiled a large database of city blocks in an area. Now I need to be able
to pull information from a city block by entering a street address on that
block.
The database looks something like this:
A B C D-E-F
1 From To Street Name Various info. regarding block
2 2000 2099 Webster
3 2100 2199 Webster
4 2200 2099 Webster
5 2000 2099 Clybourn
6 2100 2199 Clybourn
7 2200 2099 Clybourn

I need to write up a formula so that when I write:
A B
8 2134 Damen
The formula searches to determine which range the number falls in and what
street, to give me the answer:
A
9 3 €“ for row 3
Then I could use the row number and run the INDEX function to fill the
information from cells D-F for that row.

Ive tried using the MATCH function in an array formula, but have not been
able to get it to work. I need to be able to test if A8=A2:A7 and A8<=B2:B7
(though you flip this in an array formula) and match it up to B8=C2:C7.

Is there a way to do this without using macros?

Any help on this would be extremely appreciated!