Apply Advanced Filter to Range

[pto160]

I have Excel 365.
I have a Bank statement and I have a formula to advance filter on one column. Is it possible to use this with a range?

Book1
	A	B	C	D
1	Original Formula
2	Comment	Comment 2
3	House	Fees
4	Tractor	Engine
5	Withdrawal	House
6
7			House	TRUE
8
9	What I like
10	Comment	Comment 2
11	House	Fees
12	Withdrawal	House
13
14			House	FALSE
Sheet1

Cell Formulas
Range	Formula
D7	D7	=ISNUMBER(LOOKUP(9.99E+307,SEARCH($C$7,A3)))
D14	D14	=ISNUMBER(LOOKUP(9.99E+307,SEARCH($C$14,A3:B5)))

The search function does not seem to work with ranges since I want to return row 3 and row 5 since house is in both of these rows.
Is this possible?

 

[Peter_SSs]

Try this with D6:D7 as your advanced filter criteria range.

23 07 22.xlsm
	A	B	C	D
2	Comment	Comment 2
3	House	Fees
4	Tractor	Engine
5	Withdrawal	House
6
7			House	1
8
Adv Fltr

Cell Formulas
Range	Formula
D7	D7	=SEARCH(" "&C$7&" "," "&TEXTJOIN(" ",1,A3:B3)&" ")

 

[pto160]

Thanks so much. Absolutely fantastic.
The thing is that the bank report goes from column A to AJ and from row 2 to 200, so it will exceed the textjoin character limit. Column T to AJ are only relevant. I was thinking of adding another column that can look at each row from column D to E in my example and see if the word is there from the criteria list. It would put 1 or 0 or true/false if the word is there.

Book1
	A	B	C	D	E	F
2	Account	Name	Type	Comment	Comment 2	What I like
3	1000	Bank A	Checking	House	Fees	TRUE
4	1000	Bank A	Checking	Tractor	Engine	FALSE
5	1000	Bank A	Checking	Withdrawal	Computer	FALSE
6	1000	Bank A	Checking	Glass	Table	TRUE
7	1000	Bank A	Checking	Door	Bottle	TRUE
8
9						Criteria
10						House
11						Glass
12						Bottle
Sheet2

 

[Peter_SSs]

If you are just looking for the exact word in a cell then try this with the yellow criteria range.

23 07 22.xlsm
	A	B	C	D	E	F	G
2	Account	Name	Type	Comment	Comment 2
3	1000	Bank A	Checking	House	Fees
4	1000	Bank A	Checking	Tractor	Engine
5	1000	Bank A	Checking	Withdrawal	Computer
6	1000	Bank A	Checking	Glass	Table
7	1000	Bank A	Checking	Door	Bottle
8
9						Criteria
10						House	1
11						Glass
12						Bottle
Adv Fltr

Cell Formulas
Range	Formula
G10	G10	=COUNT(MATCH(F$10:F$12,D3:E3,0))

 

[pto160]

Thank you. My fault for not saying that the words in the comments columns are part of a larger text string. My apologies. Here is the revised spreadsheet.

Book1
	A	B	C	D	E	F	G
2	Account	Name	Type	Comment	Comment 2	What I am getting	What I like
3	1000	Bank A	Checking	House A	Fees	#VALUE!	TRUE
4	1000	Bank A	Checking	Tractor	Engine	#VALUE!	FALSE
5	1000	Bank A	Checking	Withdrawal	Computer	#VALUE!	FALSE
6	1000	Bank A	Checking	Glass Z	Table	#VALUE!	TRUE
7	1000	Bank A	Checking	Door	Bottle C	#VALUE!	TRUE
8
9						Criteria
10						House
11						Glass
12						Bottle
Sheet2

Cell Formulas
Range	Formula
F3:F7	F3	=SEARCH(F10:F12,D3:E3)

 

[Peter_SSs]

This should look for the whole word so should exclude items like "Household items" since the word "House" does not exist. Is that what you would want?

23 07 22.xlsm
	A	B	C	D	E	F	G
2	Account	Name	Type	Comment	Comment 2
3	1000	Bank A	Checking	House A	Fees
4	1000	Bank A	Checking	Tractor	Engine
5	1000	Bank A	Checking	Withdrawal	Computer
6	1000	Bank A	Checking	Glass Z	Table
7	1000	Bank A	Checking	Door	Bottle C
8
9						Criteria
10						House	1
11						Glass
12						Bottle
Adv Fltr

Cell Formulas
Range	Formula
G10	G10	=COUNT(MATCH("* "&F$10:F$12&" *"," "&D3:E3&" ",0))

 

[pto160]

Thank you so much. This is exactly what I want. It works great.
This will really help me extract data from a bank report.

 

[Peter_SSs]

You're welcome. Glad we got there in the end.

 

[pto160]

This is great for advanced filtering. Is there a way to use a formula to extract the same results maybe using the filter function?

Book1
	A	B	C	D	E
2	Account	Name	Type	Comment	Comment 2
3	1000	Bank A	Checking	House A	Fees
4	1000	Bank A	Checking	Tractor	Engine
5	1000	Bank A	Checking	Withdrawal	Computer
6	1000	Bank A	Checking	Glass Z	Table
7	1000	Bank A	Checking	Door	Bottle C
8
9
10
11
12
13	What I like
14	Using an Excel Fomrula
15	Account	Name	Type	Comment	Comment 2
16	1000	Bank A	Checking	House A	Fees
17	1000	Bank A	Checking	Glass Z	Table
18	1000	Bank A	Checking	Door	Bottle C
Sheet1

Once again thanks for all your help.

 

[Peter_SSs]

Is this what you mean?

pto160.xlsm
	A	B	C	D	E	F	G
2	Account	Name	Type	Comment	Comment 2		Criteria
3	1000	Bank A	Checking	House A	Fees		House
4	1000	Bank A	Checking	Tractor	Engine		Glass
5	1000	Bank A	Checking	Withdrawal	Computer		Bottle
6	1000	Bank A	Checking	Glass Z	Table
7	1000	Bank A	Checking	Door	Bottle C
8
9
10
11
12
13	What I like
14	Using an Excel Fomrula
15	Account	Name	Type	Comment	Comment 2
16	1000	Bank A	Checking	House A	Fees
17	1000	Bank A	Checking	Glass Z	Table
18	1000	Bank A	Checking	Door	Bottle C
Sheet2

Cell Formulas
Range	Formula
A16:E18	A16	=FILTER(A3:E7,BYROW(D3:E7,LAMBDA(r,COUNT(MATCH("*"&G3:G5&"*"," "&r&" ",0)))),"")
Dynamic array formulas.