MATCH SUBSTITUTE formula with sentence of text

[NJS1982]

Hi all, wonder if anyone can help me with this one please. I have a formula, listed in full below, but the part I am trying to amend is in blue:

=IF(A5="","",IFERROR(INDEX('GMS Throughput'!$B$2:$Y$350,MATCH($A5,'GMS Throughput'!$A$2:$A$350,0),MATCH(SUBSTITUTE($BM5,"Report","Throughput"),'GMS Throughput'!$B$1:$Y$1,0)),"No Figures"))

Now, if BM5 is '24 Month Report' and I want to retain the '24 Month' and search and return the contents of the column that says 'CASE MANAGER 24 MONTHS COMMENT', how do I do this (presumably with wildcards)? I have tried the below but it doesn't work:

=IF(A5="","",IFERROR(INDEX('GMS Throughput'!$B$2:$Y$350,MATCH($A5,'GMS Throughput'!$A$2:$A$350,0),MATCH(SUBSTITUTE($BM5,"Report","CASE MANAGER ** MONTHS COMMENT"),'GMS Throughput'!$B$1:$Y$1,0)),"No Figures"))

Any ideas?!

 

[istiasztalos]

Hi,

That substitute in your second formula turns '24 Month Report' into '24 Month CASE MANAGER ** MONTHS COMMENT'

Match allows wildcards, * can be any number of characters you only need one and you have to get rid of the first '24 Month' part. Maybe with a RIGHT if that's always a 2 digit number.... (wildcards simlate additional characters but can't take away)

If you need more help, we'd need to know how exactly you'd like to convert it, what values could BM5 typically have and what do you have in general in the header row when you look this up. One option would be to append the same prefix to headers too and do the match on that, this could be done with an Array formula.