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