Wildcard and vlookup

I have on list 1 with the name of the streets (no number)
on column A and on column B the name of the city.

I have another list 2 with the name of the strees (with
number).

If a use Vlookup to find the street of list 2 on list 1 and
give the name of the town will not work because the streets
on list 2 have numbers.

Can I use wild card on the formula so I can keep the street
number? If yes what is the formula? If no, any suggestion?

Thank You

Kopel

Hi

personally i'ld be tempted to use data / text to columns to split the sheet2
addresses into a number and a street column. However, a formula along the
lines of the following should give you what you want without having to split
the number and street name up.

=VLOOKUP(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND("
",A1)),Sheet1!$A$1:$B$1000,2,0)

where A1 is the number & street combination that you want to lookup on sheet
1 to find the town for.

Hope this helps
Cheers
JulieD


"Kopel" <[email protected]> wrote in message
news:[email protected]...
>I have on list 1 with the name of the streets (no number)
> on column A and on column B the name of the city.
>
> I have another list 2 with the name of the strees (with
> number).
>
> If a use Vlookup to find the street of list 2 on list 1 and
> give the name of the town will not work because the streets
> on list 2 have numbers.
>
> Can I use wild card on the formula so I can keep the street
> number? If yes what is the formula? If no, any suggestion?
>
> Thank You
>
> Kopel

Kopel,

How about something like

=VLOOKUP(MID(A1,FIND(" ",A1)+1,99),D8:E14,2,FALSE)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kopel" <[email protected]> wrote in message
news:[email protected]...
> I have on list 1 with the name of the streets (no number)
> on column A and on column B the name of the city.
>
> I have another list 2 with the name of the strees (with
> number).
>
> If a use Vlookup to find the street of list 2 on list 1 and
> give the name of the town will not work because the streets
> on list 2 have numbers.
>
> Can I use wild card on the formula so I can keep the street
> number? If yes what is the formula? If no, any suggestion?
>
> Thank You
>
> Kopel