'Excel - how to filter a list based on another list of partial values

How to extract / filter an inventory list (in range B3:C7) based on a lookup list with partial strings (in range F2:F3)?

I know that if the filtering strings were complete (not partial) the formula would be =FILTER(B3:C7, COUNTIF(F2:F3, B3:B7)).

With a single partial string (for example in cell F2) the formula would be =FILTER(B3:C7,ISNUMBER(SEARCH(F2,B3:B7))).

But how to combine the two, i.e. have partial and multiple filtering strings?

excel excel-formula

Solution 1:^[1]

Though JvdV Sir, has already provided a proper solution to your query, however here is few other alternatives, which you may also give a try

• Formula used in cell E7

=FILTER(B3:C7,MMULT(--ISNUMBER(SEARCH(TRANSPOSE(F2:F3),B3:B7)),ROW(F2:F3)^0)=1)

• Formula used in cell H7

=LET(list,B3:C7,
c,B3:B7,
i,F2:F3,
x,--BYROW(c,
LAMBDA(a,(SUM(COUNTIF(a,"*"&i&"*"))>0))),
FILTER(list,x=1))

• Formula used in cell K7

=LET(list,B3:C7,
p,B3:B7,
q,F2:F3,
x,MAP(p,LAMBDA(a,IF(SUM(COUNTIF(a,"*"&q&"*"))>0,a,""))),
FILTER(list,x<>""))

You can also refer that this query has already been solved sometime earlier in StackOverflow here is the link which you may refer as well,

Edit

• Formula used in cell E7

=FILTER(B3:C7,MMULT(--ISNUMBER(SEARCH("*"&TRANSPOSE(F2:F3)&"*",B3:B7)),ROW(F2:F3)^0))

However, the other two alternatives which i have shared it works without any change, kindly note that!

One more edit to show formula works when the Lookup Values are partial like Fr for France and Ge for Germany,

One option would be:

Formula in E7:

=FILTER(B3:C7,BYROW(B3:B7&"-",LAMBDA(a,SUM(--(IFERROR(FIND(F2:F3&"-",a),0)=1)))))

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Solution	Source
Solution 1
Solution 2	JvdV