Filter function where a subset of conditions are met

[CraigTriesHisBest]

Hello nice genius type people.

I have a data set where I want to match 2 out of 3 words and get the results. I can see easily how you can use a plus rather than asterisk to change my spreadsheet to get any one match but can't see how to match 2 of 3 data fields.

Have tried searching but to no avail. Many thanks in advance.

Craig

Filter 2 of 3 criteria match.xlsx
	A	B	C	D	E	F	G	H
1
2		My Data		Data where 2 of 3 of the items below match
3		Red Top Cat		Red
4		Red Middle Cat		Top
5		Blue Bottom Dog		Cat
6		Blue Top Cat
7		Blue Middle Cat		Results below
8		Purple Middle Cat		Red Top Cat
9		Purple Top Cat
10
11		This example gets results where ALL THREE data tems entered in the blue cells match
12		=FILTER(B:B,ISNUMBER(SEARCH(D3,B:B))*ISNUMBER(SEARCH(D4,B:B))*ISNUMBER(SEARCH(D5,B:B)),"-None-")
13
14		But what I want is to get a list of where any two match so the results would be
15
16				Results wanted
17				Red Top Cat	because it has Red and Top and Cat
18				Red Middle Cat	because it has Red and Cat
19				Blue Top Cat	because it has Top and Cat
20				Purple Top Cat	because it has Top and Cat
21
Sheet1

Cell Formulas
Range	Formula
D8	D8	=FILTER(B:B,ISNUMBER(SEARCH(D3,B:B))*ISNUMBER(SEARCH(D4,B:B))*ISNUMBER(SEARCH(D5,B:B)),"-None-")

 

[kevin9999]

Book1
	A	B	C	D	E
1
2		My Data		Data where 2 of 3 of the items below match
3		Red Top Cat		Red
4		Red Middle Cat		Top
5		Blue Bottom Dog		Cat
6		Blue Top Cat
7		Blue Middle Cat		Results below
8		Purple Middle Cat		Red Top Cat
9		Purple Top Cat
10
11		This example gets results where ALL THREE data tems entered in the blue cells match
12
13
14		But what I want is to get a list of where any two match so the results would be
15
16				Results wanted
17				Red Top Cat	because it has Red and Top and Cat
18				Red Middle Cat	because it has Red and Cat
19				Blue Top Cat	because it has Top and Cat
20				Purple Top Cat	because it has Top and Cat
21
Sheet1

Cell Formulas
Range	Formula
D8	D8	=FILTER(B:B,ISNUMBER(SEARCH(D3,B:B))*ISNUMBER(SEARCH(D4,B:B))*ISNUMBER(SEARCH(D5,B:B)),"-None-")
D17:D20	D17	=FILTER($B$3:$B$9,(ISNUMBER(SEARCH($D$3,$B$3:$B$9))+ISNUMBER(SEARCH($D$4,$B$3:$B$9))+ISNUMBER(SEARCH($D$5,$B$3:$B$9))>1))
Dynamic array formulas.

 

[CraigTriesHisBest]

Thank you so much. I had tried to get there but that is fantastic.

I did tweak it a little because
1) The source data could be a longer data set so I wanted to check all of column B, and
2) I wanted a nice fail message if nothing is returned

But thank you so much, that saved me a lot of heartache.

Filter 2 of 3 criteria match.xlsx
	A	B	C	D	E
1
2		My Data		Data where 2 of 3 of the items below match
3		Red Top Cat		Red
4		Red Middle Cat		Top
5		Blue Bottom Dog		Cat
6		Blue Top Cat
7		Blue Middle Cat		Results below
8		Purple Middle Cat		Red Top Cat
9		Purple Top Cat
10
11		This example gets results where ALL THREE data tems entered in the blue cells match
12		=FILTER(B:B,ISNUMBER(SEARCH(D3,B:B))*ISNUMBER(SEARCH(D4,B:B))*ISNUMBER(SEARCH(D5,B:B)),"-None-")
13
14		But what I want is to get a list of where any two match so the results would be
15
16				Results wanted
17				Red Top Cat	because it has Red and Top and Cat
18				Red Middle Cat	because it has Red and Cat
19				Blue Top Cat	because it has Top and Cat
20				Purple Top Cat	because it has Top and Cat
Sheet1

Cell Formulas
Range	Formula
D8	D8	=FILTER(B:B,ISNUMBER(SEARCH(D3,B:B))*ISNUMBER(SEARCH(D4,B:B))*ISNUMBER(SEARCH(D5,B:B)),"-None-")
D17:D20	D17	=IFERROR(FILTER(B:B,(ISNUMBER(SEARCH(D3,B:B))+ISNUMBER(SEARCH(D4,B:B))+ISNUMBER(SEARCH(D5,B:B))>1)),"-None-")
Dynamic array formulas.

 

[kevin9999]

Happy to help & thanks for the feedback