Filter on multiple criteria values

[kobusb]

Hi
I am using Office 365 with Dynamic Arrays and find it amazing especially the FILTER function but I am struggling to filter a table based on multiple values. I would like to include all rows of the table based on any of the values in another table.
The following example illustrates the expected result:
Table1: Table to filter

No	A
Row1	a1
Row2	a2
Row3	a3
Row4	a2
Row5	a5
Row6	a4
Row7	a6
Row8	a8
Row9	a6
Row10	a3
Row11	a9
Row12	a1
Row13	a0
Row14	a3
Row15	a6
Row16	a4
Row17	a8
Row18	a5
Row19	a9
Row20	a2
Row21	a8
Row22	a4
Row23	a6
Row24	a1
Row25	a2
Row26	a0

Table2: The values to filter on

B
a5
a6
a7

The expected result. I cannot get this result using FILTER, XMATCH, XLOOKUP or IF

No	A
Row5	a5
Row18	a5
Row7	a6
Row9	a6
Row15	a6
Row23	a6

Can someone please give me some guidance how to achieve this?

Regards

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	No	A			B
2	Row1	a1			a5		Row5	a5
3	Row2	a2			a6		Row7	a6
4	Row3	a3			a7		Row9	a6
5	Row4	a2					Row15	a6
6	Row5	a5					Row18	a5
7	Row6	a4					Row23	a6
8	Row7	a6
9	Row8	a8
10	Row9	a6
11	Row10	a3
12	Row11	a9
13	Row12	a1
14	Row13	a0
15	Row14	a3
16	Row15	a6
17	Row16	a4
18	Row17	a8
19	Row18	a5
20	Row19	a9
21	Row20	a2
22	Row21	a8
23	Row22	a4
24	Row23	a6
25	Row24	a1
26	Row25	a2
27	Row26	a0
Master

Cell Formulas
Range	Formula
G2:H7	G2	=FILTER(Table1,ISNUMBER(XMATCH(Table1[A],Table2[B],0)))
Dynamic array formulas.

 

[kobusb]

Thanks. It seems so obvious but somehow I could not get it right.

 

[Fluff]

You're welcome & thanks for the feedback.

I would also suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)