Multi Criteria using Seach in a range

pto160

Active Member
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?
 

Teeroy

Well-known Member
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

Active Member
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

MrExcel MVP
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:

pto160

Active Member
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

MrExcel MVP
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))),"")
 

pto160

Active Member
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top