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

BC
1jerry paulFALSE
2rand grereFALSE
3paul smithFALSE
4greg paul smithTRUE

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

Spreadsheet Formulas
CellFormula
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

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?
 

Some videos you may like

This Week's Hot Topics

Top