Multi Criteria using Seach in a range

[pto160]

I am trying to do a search that is returning the incorrect result. Here is the data.

Sheet1

	B	C
Dog Cat	A
Zebra House Car	B
Mouse Computer Keyboad	C
Car House	D
Correct Result
B
D

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 164px"> <col style="WIDTH: 76px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

</tbody>

Here is my formula:

=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

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?

 

[Teeroy]

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.

 

[pto160]

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?

 

[Aladin Akyurek]

Are you counting or listing or both?

 

[Aladin Akyurek]

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?

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Dog Cat[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Zebra House Car[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Mouse Computer Keyboad[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Car House[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]house[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]car[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]zebra[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]list[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In A12 control+shift+enter:

=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:

=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>

 

[pto160]

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?

 

[Aladin Akyurek]

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:

=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:

=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))),"")

 

[pto160]

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.

 

[Aladin Akyurek]

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.