Excel index search using a wildcard character (“*”)

[irrefutable14]

I'm trying to use an INDEX search instead of a VLOOKUP because I want to display all associated cells with one String in a cell. For example, if my String is "Bob", I want to be able to search Column B for all instances of "Bob", and then produce all associated values from Column C. This would be displayed elsewhere vertically in different column. Here's the formula from a website that I currently use to do this:

<code>=IFERROR(INDEX($B$1:$C$1188,SMALL(IF((D$2=$B$1:$B$1188),ROW($B$1:$B$1188)-MIN(ROW($B$1:$B$1188))+1,""),ROW(A1)),2),"")
</code> But, my data isn't perfect and very large, so I want to be able to use the wildcard character '', so I search 'Bo' & "" and return all values in Column B that start with "Bo". The formula would then be modified to:

<code>=IFERROR(INDEX($B$1:$C$1188,SMALL(IF((D$2 & "*"=$B$1:$B$1188),ROW($B$1:$B$1188)-MIN(ROW($B$1:$B$1188))+1,""),ROW(A1)),2),"")
</code> Unfortunately, that doesn't seem to work. I've tried this on a much smaller scale as well, and it still isn't functioning.

I'm a noobie. Any idea on how to fix this?

 

[Jonmo1]

You can't use wildcards in a straight A=B comparison.

Try
=IFERROR(INDEX($B$1:$C$1188,SMALL(IF((LEFT($B$1:$B$1188,LEN(D$2))=D$2),ROW($B$1:$B$1188)-MIN(ROW($B$1:$B$1188))+1,""),ROW(A1)),2),"")

 

[Eric W]

You can also use SEARCH if you want to find D2 anywhere within the cell:

=IFERROR(INDEX($B$1:$C$1188,SMALL(IF(ISNUMBER(SEARCH(D$2,$B$1:$B$1188)),ROW($B$1:$B$1188)-ROW($B$1)+1,""),ROW(A1)),2),"")

 

[irrefutable14]

Worked great!! Thanks!!

You can also use SEARCH if you want to find D2 anywhere within the cell:

=IFERROR(INDEX($B$1:$C$1188,SMALL(IF(ISNUMBER(SEARCH(D$2,$B$1:$B$1188)),ROW($B$1:$B$1188)-ROW($B$1)+1,""),ROW(A1)),2),"")