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?
CodePudding user response：
Though 
• 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,
• 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,
CodePudding user response：
One option would be:
Formula in E7:
=FILTER(B3:C7,BYROW(B3:B7&"-",LAMBDA(a,SUM(--(IFERROR(FIND(F2:F3&"-",a),0)=1)))))