Using Wildcards in an Index Match Formula

[Gollum9]

I have an existing Index Match formula which has been working fine when searching for a single string of text. What I'm trying to do now is to get a wildcard introduced so that if the course is called 'Subject A', it would also select the result if it's 'Subject A - North', 'Subject A - South' etc.

The current formula is (it has {} around it):

=IFERROR(INDEX(Classroom!$Z$3:$Z$9235,MATCH(1,($B214=Classroom!$AB$3:$AB$9235)*("Subject A"=Classroom!$W$3:$W$9235),0)),"")

In place of the "Subject A", I have tried putting Subject A into a cell and then referenced that by using C1&"*" but that didn't work. I've also tried using the text in the formula and going with "Subject A*" but no joy there either.

Just wondering where I'm going wrong and what the best solution might be?

Many thanks

 

[Marcelo Branco]

Maybe...

=IFERROR(INDEX(Classroom!$Z$3:$Z$9235,MATCH(1,($B214=Classroom!$AB$3:$AB$9235)*ISNUMBER(SEARCH(C1,Classroom!$W$3:$W$9235)),0)),"")
Ctrl+Shift+Enter

M.

 

[Gollum9]

Thanks very much, this worked great.

The only thing that was a little strange was that it still seemed to work even when the C1 cell was blank??? I have been through and checked it's bringing through the correct result and it is though.

Thanks again

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

About C1 blank:
Try something like
=IF(C1="","", <formula here=""></formula>formula here)

M.