Wildcard and vlookup

  • Thread starter Thread starter Kopel
  • Start date Start date
K

Kopel

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,

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)
 
Back
Top