Use formula for range and not row

[pto160]

I got this great formula from Exceluser.com (thank you Charley Kyd) to do multi search criteria.
Sheet1

	B	C
jerry paul
rand grere
paul smith
greg paul smith

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 97px"> <col style="WIDTH: 64px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]TRUE[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C1 =SUMPRODUCT(NOT(ISERR(SEARCH({"smith","paul","greg"},B1)))*{1,2,4})=7 C2 =SUMPRODUCT(NOT(ISERR(SEARCH({"smith","paul","greg"},B2)))*{1,2,4})=7 C3 =SUMPRODUCT(NOT(ISERR(SEARCH({"smith","paul","greg"},B3)))*{1,2,4})=7 C4 =SUMPRODUCT(NOT(ISERR(SEARCH({"smith","paul","greg"},B4)))*{1,2,4})=7 <tbody> </tbody>

<tbody>
</tbody>

I'm trying to apply this formula by using a range B1:B4 without copying down the formula for each row in C1:C4. I would like to use a small(if function if possible so I can get multiple matches.
The formula would be something like this:
=SUMPRODUCT(NOT(ISERR(SEARCH({"smith","paul","greg"},B1:B4)))*{1,2,4})=7

I'm getting an error message when I try and do this.
Is there a way to do this?

Excel tables to the web >> Excel Jeanie HTML 4

 

[pto160]

The result is row 4 or 4.

 

[pto160]

I was trying to use this formula:
=SMALL(IF(SUMPRODUCT(NOT(ISERR(SEARCH({"smith","paul","greg"},$B$1:$B$4)))*{1,2,4})=7=TRUE,ROW($B$1:$B$4)),1)
but I'm still getting zero. The result that I want is row 4 since all the conditions are met there.

 

[pto160]

I think the sumproduct is creating the issue because it can't handle arrays.

Is there any solution to this?