Formula needed to return values for partial or full match

Hi,

New to this forum so hope this explanation of my excel challenge is clear.

I have one workbook with 2 sheets of data.

Basically I am trying to copy information (using a formula if possible as I have no idea how to use Macros or VBA - although willing to try them out) from specific cells in a single row from Sheet2 to multiple rows on Sheet1 based on certain criteria.

Sheet 2 has list of approx. 2,000 unique company names in Column B. The info I want to copy pertains to each company and is in columns C to I.

Sheet 1 has thousands of rows of data that have multiple entries (sorted A to Z) of each of the 2,000 company names from Sheet2.

Each row (for every company name on Sheet 1) needs to be auto-populated with the data from the specific rows from Sheet 2 (cells C to I) pertaining to that company. Sheet 1 Column C contains company names so the data from sheet 2 will be pasted into columns D to J.

The problem is the company names on Sheet 1 are not an exact match of the ones on Sheet 2. For example the first entry on Sheet 2 is "20:20 Mobile" but on Sheet 1 it is just "20:20".

So I'm looking for a formula that will take the company name from Sheet 1 as the value, and search for it in Column B in Sheet 2. For every partial or full match I need it to copy the info from the each specific row (C to I) in to every row (D to J on Sheet 1) where the company names match or just partially match.

Hope this is clear and any help much appreciated!

Rgds
IntRes

Hi

I thnk we can do this, using formulae but(at least me) need a sample workbook to work on this.

Hi IntRes,

Welcome to the forum.

Please upload a sample workbook. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

I think you may well be challenged on your thread title so please have a quick read of the forum rules and edit it to something more specific. Maybe, "need to return values for partial or full match formula".

Regards, TMS

TMShucks - good advice on Thread title, thanks.

Rgds
IntRes

Good advice but you didn't use this!

See also the rule number 7 of the forum. Pls, do it.

7. Don't ignore requests by Administrators, Moderators, or senior members of the forum. If you are unclear about their request or instructions, then send a private message to them asking for help. Do not post a reply in a thread where such a request (e.g., title change, code tags) is pending.

In the meantime we'll see your sample workbook.

You have to edit the thread title of your 1st post not the last post you typed in.
I have done it for you this time.

Arlette, OP asked me with PM if change is ok and i said "YES".

So it's not IntRes mistake, but mine!

Also i need help on this thread. So if someone has an idea, pls feel free to advice.