Return cell contents on a partial match

Howdy and thank you in advance...

I have a data page that lists the name of individuals under a region Prior YTD, current YTD. The names of the regions can change occassionally and not all are entered in the same format

E.g.

Region
NORTH.PRIOR YTD
NORTH.YTD
MR SMITHS.PRIOR YTD
MR SMITHS.YTD

What I need to know is this.
HOW do I search the DATA page to find each of the "North" entries (up to four of them, usually one beneath the other on the "Data" sheet) and copy the name AS IT APPEARS on the Data sheet to the named "North" sheet?

Currently I have two columns one with the Region and one with the name of the company that goes with that particular region.

Thank you for your speedy reply,

I did as you have suggested (changing the references of course to match my workbook), however it didn't work. I have attached an example to show the kind of data and the named sheets and results that I would like to get.

I would be grateful for ANY suggestions.....

Thank you again...however it isn't working. The result I got was zero..literally 0 in the cell.

Sheesh...maybe this won't work.

Is there anyone that has a solution to this issue? It is urgent that I find an answer. I just want a formula to search and return a "partial" match on a list.

I have tried the offered formula in the sample workbook, works fine in there. But the minute I transfer it to my book, (changing the cell references of course) and YES I did control, shift + enter after editing) but it still will not work no matter what I do.