Do a partial match of data then copy data from a different cell into a new column

[xantheK]

Hi there

I get information from various sources regarding store sales.

Each store has a unique code, but naming conventions across the data sources are different and I cannot control how they do it as I am only a user of their data sources. On some they do not use the unique codes and I also get similar but not exact names for the same store. I created a database with all the relevant store information and now I need to pull in sales figures from those sources using downloadable excel spreadsheets and interpret it for my company.

I need to be able to look for a partial match in those sources and then copy new data to (My Source) where I can add for example two columns for the value and units. See (Source 2).

Examples of how the various information is presented - "|" used to indicate columns :

FAMILY MARRIANPARK | 6001007010000 | CFU58 --> (Source 1) I can sort this and use VLOOKUP, no problem
Marrian Park Family Store | $100.00| 3 (units) | --> (Source 2) ***this is my problem***
Marrian Park | South Region| Tel | Address | CFU58 --> (Source 3) I can sort this and use VLOOKUP, no problem

Region | Type | Name | Code --> (My Source) obviously one can sort as you wish
South | Family| Marrian Park | CFU58

When using Source 2 (which do not make use of store codes) as a reference I need a formula to match the word Marrian in both sources and then copy the value or anything else from that sheet I want into a new column into (My Source).

End result in (My Source) would be :

Region | Type | Name | Code | Value | Units
South | Family| Marrian Park | CFU58 |$100.00| 3


Hope there is someone who can help.
X

[Melvinrobb]

You might want to look into using wildcards with VLOOKUP: http://chandoo.org/wp/2010/11/01/usi...-with-vlookup/
If this is not helpful, I might not be understand what you are wanting to accomplish.

[xantheK]

nope i am not looking for specific names every time.

I want the program to look in two columns (each with for example 50 names) and find 50 stores who has similar or same names. So I want a comparison check across all names in two columns and bring em back all who has a similar first word.

[Melvinrobb]

Won't there be multiple entries in Source 2 with "Marian Park Family Store" in column A? how will you know which one to use?
A sample workbook with some sample data, and a sheet with "Your Source" with an example of what you would want pulled would be useful.