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?
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,
How to search multiple names from a range and return multiple records in excel?
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)))))