# 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

 B C 2 Dog Cat A 3 Zebra House Car B 4 Mouse Computer Keyboad C 5 Car House D 6 7 Correct Result 8 B 9 D

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

##### MrExcel MVP
Are you counting or listing or both?

##### 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 Cat A 3​ Zebra House Car B 4​ Mouse Computer Keyboad C 5​ Car House D 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?

##### 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.

##### MrExcel MVP
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.

### 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#...