Use formula for range and not row

pto160

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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
I think the sumproduct is creating the issue because it can't handle arrays.

Is there any solution to this?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top