check if text in cell is available in a column

I am using excel 2003.

I have the following -

Column A

Column A has the following –
A1 - 3
A2 - 5
A3 - 7
A4 - 4

Column B has the following –
B1 roger
B2 kenny
B3 harry
B4 bernard

Column C - Is not static as I keep pasting new lines of text into column C once everyday.
Column C example in first paste is -
C1 big roger
C2 where is kenny
C3 harry went away
C4 lion


Column D - Column D is where I want to use the formulae.
I want each row in the text in Column C to be compare with Column B. If there is a section of text in column C1 that matches cell range B1:B4, then the value of the corresponding row in column A1 need to be displayed in Column D1 .

For example -
I paste “big roger” in cell C1. Now the formulae need to check if either of the words “big” or “roger” is contained within cell range B1:B4. So if just the word “roger” is there in B1, the corresponding A column’s value “3”. Will be displayed in cell D1.

I have come close with the following formulae –

=IF(COUNTIF($A$1:$A$4,C1)=1,D1,FALSE)

However C1 in the formulae needs to consider “big roger” and then compare it with B1:B4 “roger”. I need to be able to paste new text in C1 and still get the comparison results displayed in cell D1

There are many websites explaining how to insert “ big roger” into the formulae itself and then compare it with other columns but that is not what I want.