lookup to replace partial strings

[flashbbeb]

Hi all,

On one worksheet (Sheet1), I've got a column of strings (location names at my company) that are relatively unique. Unfortunately most of the strings max out at a length of 22 characters. So I've got a sad list of unfinished names.

I have another worksheet (Sheet2) with a column of a unique list of the names - all display at full length.

How do I...compare the unfinished name list in Sheet1 with Sheet2, adding the finished names to a new column?

Example:
Sheet2
Column A
Telework - Springfield Operations and Admin
Telework - Bakersfield Operations and Admin
On site - Pittsburgh Internal Operations
Teleworker - Charlotte Operations and Admin
On site - Minneapolis Human Resources
Telework - Montana Outreach Location
Telework - Maryland Outreach Location
Telework - Mississippi Outreach Location
Telework - New York-New Jersey Outreach Location

Sheet1
Column A
Telework - Springfield
Telework - Bakersfield
On site - Pittsburgh I
Teleworker - Charlotte
On site - Minneapolis
Telework - Montana Out
Telework - Maryland Ou
Telework - Mississippi
Telework - New York-Ne

Column B (matches Sheet1 ColA to Sheet2 ColA and provides the rest of the string)
Telework - Springfield Operations and Admin
Telework - Bakersfield Operations and Admin
On site - Pittsburgh Internal Operations
Teleworker - Charlotte Operations and Admin
On site - Minneapolis Human Resources
Telework - Montana Outreach Location
Telework - Maryland Outreach Location
Telework - Mississippi Outreach Location
Telework - New York-New Jersey Outreach Location

Thanks,
EB

 

[flashbbeb]

Found the answer: In the Column B on Sheet2 I'd add =VLOOKUP("*"&A2&"*",Sheet2$A:$A,1,0)

Same old vlookup, but with the wildcard.