B.,

First, you need to change your data entry:

City No. Street Area Map
Coordinates
Ourville 123 Rue de Us 321 C10
Youville 81-765 Them St 246 B12

Your street addresses can't have dashes in them - change the number
following the dash into a separate field - leave it blank for those without
the dashes.

City No. Unit Street Area
Map
Coordinates
Ourville 123 Rue de Us 321
C10
Youville 81 765 Them St 246
B12

And on your coordinate sheet, you need to split out the street name and the
numbers. To do that put these formulas into cells D2:G2 (assuming your
three columns are A to C, and that street names are unique to a town, with
street name and number in A, Area in B, and Coords in C)

=MID(A2,FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))),1)+1,200)
=TRIM(SUBSTITUTE(A2,D2,""))
=VALUE(LEFT(D2,FIND("-",D2)-1))
=VALUE(MID(D2,FIND("-",D2)+1,100))

Then you can copy that down to match.

Then, to get your Area and Map Coords, use a formula like

=SUMPRODUCT((B2='Data Sheet name'!$E$2:$E$6000)*(A2='Data Sheet
name'!$F$2:$F$6000)*(A2<='Data Sheet name'!$G$2:$G$6000)*'Data Sheet
name'!$B$2:$B$6000)
=SUMPRODUCT((B2='Data Sheet name'!$E$2:$E$6000)*(A2='Data Sheet
name'!$F$2:$F$6000)*(A2<='Data Sheet name'!$G$2:$G$6000)*'Data Sheet
name'!$C$2:$C$6000)

Where A2 is the cell that has the number, B2 has the street name, and the
formulas from above are on a sheet named "Data Sheet name", with area in
column B, and Map Coords in column C.

Again, this assumes that street names are unique...

HTH,
Bernie
MS Excel MVP



"B. Meincke" wrote in message
...
I have done a little programming in Access but am new to it in Excel. I
have
been reading earlier posts for a solution to my situation and am not at
all
sure which function would suit it best. Here it is:

I have a workbook with two sheets. The first sheet is a list of addresses
a
real estate agent will present buyers looking to visit open houses. As
well
as the address fields for each record (street number and street and city)
it
also includes a field for map lookup coordinates, so a record might look
like
this:

City No. Street Area Map
Coordinates
Ourville 123 Rue de Us 321 C10
Youville 81-765 Them St 246 B12

The second sheet contains a list of all streets in the area sorted by city
and their corresponding coordinates. This file is sent to us from the
realestate board.

What we would like to be able to do is to enter the street addresses for
the
week's open houses and have the spreadsheet automatically enter the area
and
coordinate data.

There are really two problems that I can see:
1. Some address numbers include a unit numbers divided from the address
by
a hyphen
2. The coordinate sheet has multiple entries for long streets and lists
house numbers in ranges...for example:

Street Area Coordinates
Ru de Us 100-450 321 A10
Ru de Us 451-900 322 A11
Ru de Us 901-1275 323 A11

I have looked at lookups and I don't think that will work. I have read
about
match arrays but am not sure I completely understand what that means or
whether or not it is what I need to do in my case.

If I can make this clearer by providing any further information, I'd be
happy to. Thanks in advance for any suggestions or advice.
--
BJM
ACE Assistant
Gary Allan High School