How to create filtered list using formula by criteria containing asterisks

[marruz]

Dear friends, i will be very glad for your help. Here is my situation:

I know how to create such a filtered list using the array formula combining INDEX and SMALL functions but this does not allow me to use criteria containing asterisks. If I used criteria "1,2,3,4,5,6" instead of "1,*,*,*,5,6" I could simply use this:

=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$F$3;ROW($A$1:$A$8)),ROW(1:1))),"")

So my question is: How to change this formula or which formula to use if I need to have criteria which includes asterisks as wildcard characters. I know IF function does not allow wildcard characters, so is there a workaround. Thanks.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe

Array formula in F8 copied down
=IFERROR(INDEX(A$2:A$8,SMALL(IF(ISNUMBER(SEARCH(F$3,A$2:A$8)),ROW(A$2:A$8)-ROW(A$2)+1),ROWS(F$8:F8))),"")
Crl+Shift+Enter

Hope this helps

M.

 

[marruz]

Dear Marcelo, this is exactly what I needed. Thank you very much. It works perfectly.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.