Multi Criteria using Seach in a range

pto160

Active Member
Joined
Feb 1, 2009
Messages
473
Office Version
  1. 365
Platform
  1. Windows
I am trying to do a search that is returning the incorrect result. Here is the data.

Sheet1

BC
2Dog CatA
3Zebra House CarB
4Mouse Computer KeyboadC
5Car HouseD
6
7Correct Result
8B
9D

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

Here is my formula:
Code:
=INDEX($C$2:$C$5,SMALL(IF(SUMPRODUCT(NOT(ISERR(SEARCH({"Car","House"},$B$2:$B$5)))*{1,2})=3=TRUE,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($B$12:$B12)))

The result I'm receiving A. The correct result is B and D since "Car" and House" are in rows 3 and 5.
The search part
Code:
SEARCH({"Car","House"},$B$2:$B$5)))*{1,2})=3
is assigning 1 to "Car" and 2 to "House", which equals 3.

How do I correct my formula or is there a solution?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Your search wasn't making complete sense to me so I used a different approach. Try:

=INDEX($C$2:$C$5,SMALL(IFERROR(((SEARCH("Car",$B$2:$B$5)*SEARCH("House",$B$2:$B$5))>0)*ROW($1:$4),9.99E+307),ROWS($B$12:$B12)))

as an array formula.
 
Upvote 0
Thank you so much. The formula works great.:)
I was trying to use the other formula below since you can use one search function for multiple criteria. So "House"=1 and "Car"=2 so the total is equal to 3.

'=SUMPRODUCT(NOT(ISERR(SEARCH({"House","Car"},$B2)))*{1,2})=3

If I wanted to look for House, Car, Zebra the the formula is

=SUMPRODUCT(NOT(ISERR(SEARCH({"House","Car","Zebra"},$B3)))*{1,2,4})=7

The formula only seems to work for one cell. I can't seem to apply it to a range to find the the TRUE lines. I don't want to use a helper column.

Is there a solution using this formula I wonder?
 
Upvote 0
Thank you so much. The formula works great.:)
I was trying to use the other formula below since you can use one search function for multiple criteria. So "House"=1 and "Car"=2 so the total is equal to 3.



If I wanted to look for House, Car, Zebra the the formula is

=SUMPRODUCT(NOT(ISERR(SEARCH({"House","Car","Zebra"},$B3)))*{1,2,4})=7

The formula only seems to work for one cell. I can't seem to apply it to a range to find the the TRUE lines. I don't want to use a helper column.

Is there a solution using this formula I wonder?

Are you counting or listing or both?

Row\Col
A​
B​
1​
2​
Dog CatA
3​
Zebra House CarB
4​
Mouse Computer KeyboadC
5​
Car HouseD
6​
7​
house
8​
car
9​
zebra
10​
11​
count
12​
2
13​
list
14​
B
15​
D
16​
17​

<tbody>
</tbody>


In A12 control+shift+enter:
Rich (BB code):

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE($A$7:$A$9),$A$2:$A$5))+0,
    ROW($A$7:$A$9)^0),1))

In A14 control+shift+enter and copy down:
Rich (BB code):

=F(ROWS($A$14:A14)<=$A$12,INDEX($B$2:$B$5,
    SMALL(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE($A$7:$A$9),$A$2:$A$5))+0,
    ROW($A$7:$A$9)^0),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$14:A14))),"")
<strike></strike>
 
Last edited:
Upvote 0
Absolutely brilliant. This formula is more powerful and dynamic than the one I gave you. Never seen anything like it.
Sometimes my search criteria list will be arranged horizontally.
Is there a way to use the formula so the Search criteria is A7:C7 instead of A7:A9?
 
Upvote 0
Absolutely brilliant. This formula is more powerful and dynamic than the one I gave you. Never seen anything like it.
Sometimes my search criteria list will be arranged horizontally.
Is there a way to use the formula so the Search criteria is A7:C7 instead of A7:A9?

In A12 control+shift+enter:
Rich (BB code):

=SUM(IF(MMULT(ISNUMBER(SEARCH(A7:C7,$A$2:$A$5))+0,
    TRANSPOSE(COLUMN(A7:C7)^0)),1))

In A14 control+shift+enter, not just enter, and copy down:
Rich (BB code):

=IF(ROWS($A$14:A14)<=$A$12,INDEX($B$2:$B$5,
   SMALL(IF(MMULT(ISNUMBER(SEARCH($A$7:$C$7,$A$2:$A$5))+0,
   TRANSPOSE(COLUMN($A$7:$C$7)^0)),ROW($A$2:$A$5)-ROW($A$2)+1),
   ROWS($A$14:A14))),"")
 
Upvote 0
Thank you so much. It works great. It's a very robust and flexible formula that can be tailored to many different situations. Didn't know Excel could do stuff like this. Once again thank you.
 
Upvote 0
Thank you so much. It works great. It's a very robust and flexible formula that can be tailored to many different situations. Didn't know Excel could do stuff like this. Once again thank you.

You are welcome. Thanks for the update.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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