# Use formula for range and not row

#### pto160

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

 B C 1 jerry paul FALSE 2 rand grere FALSE 3 paul smith FALSE 4 greg paul smith TRUE

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 97px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>

 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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### pto160

##### Active Member
The result is row 4 or 4.

#### pto160

##### Active Member
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

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

Is there any solution to this?