Filter function to filter an array of conditions

[CErnst]

Long time listener, first time caller.

Please see attached image. I'm trying to use the filter function to essentially return the content column (C) based on a variable list from the key column (B). That variable list would be contained in the List column (E). Column G is what I'm expecting the results to look like. I2 is the formula that I have in my head, but not sure why it isn't working. I read the formula in I2 as "I'm filtering column c, when column B contains any word found in an array constructed from column E that has a value"


I'm willing to bet the answer will be relatively easy, but I'm just not seeing it. I would appreciate any help.

 

[JamesCanale]

MrExcelPlayground5.xlsx
	B	C	D	E	F	G
1	Key	Content		List		Result
2	cat	cat line 1		cat		cat line 1
3	cat	cat line 2		dog		cat line 2
4	cat	cat line 3		bird		cat line 3
5	dog	dog line 1				dog line 1
6	dog	dog line 2				dog line 2
7	dog	dog line 3				dog line 3
8	bird	bird line 1				bird line 1
9	bird	bird line 2				bird line 2
10	bird	bird line 3				bird line 3
11	snake	snake line 1
12	snake	snake line 2
13	snake	snake line 3
Sheet13

Cell Formulas
Range	Formula
G2:G10	G2	=FILTER(C2:C13,NOT(ISNA(MATCH(B2:B13,E2:E4,0))))
Dynamic array formulas.

This is if col B equals something in col E. If you wanted something that contains something in Col E, that can be managed too.

 

[Fluff]

Hi & welcome to MrExcel.
Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1		Key	Content		List		Result
2		cat	cat line 1		cat		cat line 1
3		cat	cat line 2		dog		cat line 2
4		cat	cat line 3		bird		cat line 3
5		dog	dog line 1				dog line 1
6		dog	dog line 2				dog line 2
7		dog	dog line 3				dog line 3
8		bird	bird line 1				bird line 1
9		bird	bird line 2				bird line 2
10		bird	bird line 3				bird line 3
11		snake	snake line 1
12		snake	snake line 2
13		snake	snake line 3
14
Data

Cell Formulas
Range	Formula
G2:G10	G2	=FILTER(C2:C20,(C2:C20<>"")*(COUNTIFS(E2:E4,B2:B20)<>0))
Dynamic array formulas.

 

[CErnst]

oh I forgot about match to find the rows. I knew it was something simple.

I like to use whole columns, so James' formula cleaned up to: =FILTER(C:C,NOT(ISNA(MATCH(B:B,E:E,0))))

 

[JamesCanale]

As long as one of the keys isn't the word "List".

 

[Fluff]

I like to use whole columns,

I would strongly advise against that unless you want a workbook that is potentially very very slow.

 

[CErnst]

James, data set is just a sample to get the mechanics working.

Fluff, data set is fairly large, about 1100 rows. I could make the list of column E look at a chunk of 50 cells. I'm playing with real data set now. Making a dynamic BOM based on SKUs. The whole system is not really great, but trying to make things as flexible as possible based on what is provided to work with.

 

[JamesCanale]

Format everything as a table, and let excel do the hard work?

MrExcelPlayground5.xlsx
	B	C	D	E	F	G
1	Key	Content		List		Result
2	cat	cat line 1		cat		cat line 1
3	cat	cat line 2		snake		cat line 2
4	cat	cat line 3		bird		cat line 3
5	dog	dog line 1				bird line 1
6	dog	dog line 2				bird line 2
7	dog	dog line 3				bird line 3
8	bird	bird line 1				snake line 1
9	bird	bird line 2				snake line 2
10	bird	bird line 3				snake line 3
11	snake	snake line 1
12	snake	snake line 2
13	snake	snake line 3
Sheet13

Cell Formulas
Range	Formula
G2:G10	G2	=FILTER(Table1[Content],NOT(ISNA(MATCH(Table1[Key],Table2[List],0))))
Dynamic array formulas.

 

[Fluff]

I would say that 1100 rows is quite small & you can always expand the range to more than is enough without using whole column references, like

=FILTER(C2:C5000,(C2:C5000<>"")*(COUNTIFS(E2:E5000,B2:B5000)<>0))