Use formula for range and not row

pto160

Active Member
Joined
Feb 1, 2009
Messages
330
Office Version
365
Platform
Windows
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
 

Some videos you may like

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
Joined
Feb 1, 2009
Messages
330
Office Version
365
Platform
Windows
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
Joined
Feb 1, 2009
Messages
330
Office Version
365
Platform
Windows
I think the sumproduct is creating the issue because it can't handle arrays.

Is there any solution to this?
 

Watch MrExcel Video

Forum statistics

Threads
1,089,966
Messages
5,411,556
Members
403,378
Latest member
nadirefe

This Week's Hot Topics

Top