How would you filter out a dynamic array using a dynamic array?

Question

As the image shows, there is a dynamic array consisting of the words "One" and "Two" in column C. Being a dynamic array, it could be one criteria, could be hundreds of criteria. Then, there is the dynamic array list next to the input cell. This also could consist of one cell or hundreds of cells. I want to get an output (which is shown in red) where the only cells that are kept from the input dynamic array are ones that contain the words from the user input dynamic array. However, I want the output to also be a dynamic array, so that no matter how many items are in the input dynamic array, the output dynamic array will automatically be the same size. Is this possible?

Answer 1

Edit :

Use XLOOKUP( ) as suggested by Scott Craner Sir.

Sir made it simpler and sleeker.

---

---

• Formula used in cell I1

=XLOOKUP(F1#,C1#,C1#,"")

---

Try something along the lines using IF( ) with ISNA( ) & XMATCH( )

---

• Formula used in cell I1

=IF(ISNA(XMATCH(F1#,C1#)),"",F1#)

---

Where:

• Formula used in cell C1

=TEXTSPLIT(A2,,",")

---

And formula used in cell F1

={"Test";"Test";"Other";"Test";"One";"Other";"One";"Test";"Two"}

---

You can also use MMULT( ) with SEQUENCE( )

---

=IF(MMULT(N(F1#=TOROW(C1#)),SEQUENCE(ROWS(C1#),,1))=0,"",F1#)

---

Few examples:

---