Search code examples
arraysexcelexcel-formuladynamic-arrays

How would you filter out a dynamic array using a dynamic array (Non exact match)?

This is a follow up to the question I posted yesterday. I have two dynamic arrays in excel, as shown in the image. One is C1#, which is the criteria for the search, and one is F1#, which is the dynamic array I want to search in. I want to get the dynamic array output as shown in the Wanted Output. I am able to get this output, however I am not sure how to get it to output "None", which includes the word "One", I am currently only able to get it to output exact matches. Is there any way to do this without requiring exact matches? Thank you.

enter image description here

Solution

  • Try using MMULT( )

    enter image description here

    • Formula used in cell I1

    =LET(
     a,TOROW("*"&C1#&"*"),
     b,SEQUENCE(ROWS(C1#),,,0),
     IF(MMULT(N(ISNUMBER(SEARCH(a,F1#))),b),F1#,""))