Filter function where a subset of conditions are met

CraigTriesHisBest

New Member
Joined
Mar 29, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1
2My DataData where 2 of 3 of the items below match
3Red Top CatRed
4Red Middle CatTop
5Blue Bottom DogCat
6Blue Top Cat
7Blue Middle CatResults below
8Purple Middle CatRed Top Cat
9Purple Top Cat
10
11This 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
14But what I want is to get a list of where any two match so the results would be
15
16Results wanted
17Red Top Catbecause it has Red and Top and Cat
18Red Middle Catbecause it has Red and Cat
19Blue Top Catbecause it has Top and Cat
20Purple Top Catbecause it has Top and Cat
21
Sheet1
Cell Formulas
RangeFormula
D8D8=FILTER(B:B,ISNUMBER(SEARCH(D3,B:B))*ISNUMBER(SEARCH(D4,B:B))*ISNUMBER(SEARCH(D5,B:B)),"-None-")
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Book1
ABCDE
1
2My DataData where 2 of 3 of the items below match
3Red Top CatRed
4Red Middle CatTop
5Blue Bottom DogCat
6Blue Top Cat
7Blue Middle CatResults below
8Purple Middle CatRed Top Cat
9Purple Top Cat
10
11This example gets results where ALL THREE data tems entered in the blue cells match
12
13
14But what I want is to get a list of where any two match so the results would be
15
16Results wanted
17Red Top Catbecause it has Red and Top and Cat
18Red Middle Catbecause it has Red and Cat
19Blue Top Catbecause it has Top and Cat
20Purple Top Catbecause it has Top and Cat
21
Sheet1
Cell Formulas
RangeFormula
D8D8=FILTER(B:B,ISNUMBER(SEARCH(D3,B:B))*ISNUMBER(SEARCH(D4,B:B))*ISNUMBER(SEARCH(D5,B:B)),"-None-")
D17:D20D17=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.
 
Upvote 0
Solution
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
ABCDE
1
2My DataData where 2 of 3 of the items below match
3Red Top CatRed
4Red Middle CatTop
5Blue Bottom DogCat
6Blue Top Cat
7Blue Middle CatResults below
8Purple Middle CatRed Top Cat
9Purple Top Cat
10
11This 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
14But what I want is to get a list of where any two match so the results would be
15
16Results wanted
17Red Top Catbecause it has Red and Top and Cat
18Red Middle Catbecause it has Red and Cat
19Blue Top Catbecause it has Top and Cat
20Purple Top Catbecause it has Top and Cat
Sheet1
Cell Formulas
RangeFormula
D8D8=FILTER(B:B,ISNUMBER(SEARCH(D3,B:B))*ISNUMBER(SEARCH(D4,B:B))*ISNUMBER(SEARCH(D5,B:B)),"-None-")
D17:D20D17=IFERROR(FILTER(B:B,(ISNUMBER(SEARCH(D3,B:B))+ISNUMBER(SEARCH(D4,B:B))+ISNUMBER(SEARCH(D5,B:B))>1)),"-None-")
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,214,561
Messages
6,120,239
Members
448,951
Latest member
jennlynn

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