Formula Needed to match 2 ranges of data and populate cell with answer

I have been working on this all night and i've reached my limit...
Hopefully someone can help me with this....

I have a central list of address with St# and Street Names
Each of those addresses is associated with a Postal walk.
Often a particular street (Example: Main St) crosses into several postal walks
Thus 1-50 Main Street is Postal walk SS0011 while 51-100 Main street is Postal Walk SS0022 and so on...upto 4 times.


I have another data set on sheet2 that needs to search for the street address in sheet 1 and also search the range of Street #'s to determine which Postal Walk code to enter. Thus if the address in Sheet 2 is 74 Main Street, then it should return SS0022

Thanks for everyones help in advance

I've uploaded the data with two tabs.
The first is the list of addresses - I used a sample of 3 addresses
The second sheet is my main sheet, but has two empty columns where i'd like them to auto populated with a formula that identifies the street name in Sheet 1 and gives me the corresponding Postal walk (SS00??) and the corresponding community name (ex. Sonoma)

Hope this helps clarify.

And thanks to the forum for all your help...

The street name was only one factor that needed to be searched. The other was the range of street #'s.
for example:
* 1-10 Main street is Postal walk SS0001
* 11-30 Main Street is Postal Walk SS0002
* 31-50 Main Street is Postal Walk SS0003
The formula I need has to give me the correct postal walk based not only on the name of the street, but also on the range of Street #'s.
As in this example, Main street may cross into several postal walks.

Does someone know how to make this formula?

I see the confusion... I used Main Street as an example only...
Here is the criteria based on the workbook:
Thus I'm looking for a formula that would take the name of the street first, then give me a result (example: SS0002) based on the range of street numbers, thus here is the data and the corresponding result.
1 - 115 Keystar Crt Result = SS0002
116 - 200 Keystar Crt Result = SS0001
1 - 75 John Desmond Dr Result = SS0003
76 - 200 John Desmond Dr Result = SS0001
1-15 Vellore Dr Result = SS0002
16-100 Vellore Dr Result = SS0003

Hi Dilipandey,

This thread died for a while...
I was just looking through my emails and saw your last post.

The Ciriteria can be in a new worksheet within the workbook.
Does that answer your question..
Zan Pal