Add a Wildcard to an index

[Pignit1965]

Hi All, Can I add a wildcard to and index.
I have a table of data from which I am calling back pricing from all instances from the given lookup, but it will only pull exact matches but I need it ad a wildcard..

i.e. I need to search for CH12-04268-(any character) but I have to state CH12-04268-AA to get a return.

I am currently using this formula
{=IF(ISERROR(INDEX('L316'!$F$1:$K$5998,SMALL(IF('L316'!$F$1:$F$5998=$B$2,ROW('L316'!$F$1:$K$5998)),ROW('L316'!1:1)),3)),"",INDEX('L316'!$F$1:$K$5998,SMALL(IF('L316'!$F$1:$F$5998=$B$2,ROW('L316'!$F$1:$K$5998)),ROW('L316'!1:1)),3))}

Can I add wildcard to this or is there an easier or better way

Many thanks all

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=IF(ISERROR(INDEX('L316'!$F$1:$K$5999,SMALL(IF(LEFT('L316'!$F$1:$F$5999,LEN($B$2))=$B$2,ROW('L316'!$F$1:$K$5999)),ROW('L316'!1:1)),3)),"",INDEX('L316'!$F$1:$K$5999,SMALL(IF(LEFT('L316'!$F$1:$F$5999,LEN($B$2))=$B$2,ROW('L316'!$F$1:$K$5999)),ROW('L316'!1:1)),3))

...where B2 contains CH12-04268. Note, however, IFERROR can be used instead of ISERROR to trap errors. Also, the formula can be made to be more robust. So, for example, let's say that B2 contains CH12-04268 and that the formula will be entered in C2, and copied down. Try..

=IFERROR(INDEX('L316'!$F$1:$K$5999,SMALL(IF(LEFT('L316'!$F$1:$F$5999,LEN($B$2))=$B$2,ROW('L316'!$F$1:$K$5999)-ROW('L316'!$F$1)+1),ROWS($C$2:C2)),3),"")

Hope this helps!