Modifying Array Formula to Use Wildcards

[d_tech]

I've tried to modify the following formula to use wildcards but I can't seem to figure it out.

=IF(ISERROR(INDEX($B$1:$C$1000,SMALL(IF($C$1:$C$1000=$D$1,ROW($C$1:$C$1000)),ROW(1:1)),1)),"",INDEX($B$1:$C$1000,SMALL(IF($C$1:$C$1000=$D$1,ROW($C$1:$C$1000)),ROW(1:1)),1))

I'm trying to get the results of "*"&$D$1&"*" into here.

What's the solution?

 

[Marcelo Branco]

Welcome to Mr Excel forum

If you are using Excel 2007 or higher try this
=IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH($D$1,$C$1:$C$1000)),ROW($C$1:$C$1000)),ROWS(E$1:E1))),"")

Assumes the first formula in cell E1 (adjust to suit) then copied down to extract all instances that meet criterion.

Hope this helps

M.

 

[d_tech]

Yeah that helps. Works perfect!
Thanks!!

 

[Marcelo Branco]

You are welcome. Glad to help

M.