Array when cell contains specific text.

[Godders199]

I am trying to get this array to work.
=MAX(IF(Submissions!$K:$K=Sheet1!$A2,IF(Submissions!$P:$P="*"&Sheet1!$P$5&"*",Submissions!$M:$M),))
I believe the P5 element is incorrect, but I can’t get it towork, I am looking for the latest date in column M, when column P contains theword in P5. If I do an exact match itworks, i.e P:P = P5, however in majority of cells there is more than one wordin column P.
Is there an alternative I can use for contains the text inP5?

 

[FormR]

Hi, one option.

=MAX(IF(Submissions!$K1:$K100=Sheet1!$A2,IF(ISNUMBER(SEARCH(Sheet1!$P$5,Submissions!$P1:$P100)),Submissions!$M1:$M100),))

Note: for efficiency reasons you should avoid full column references with array formula, you could consider using sensible maximums, dynamic named ranges, tables and structured references or the MAXIFS() function (if you have it available) as alternatives.

 

[Godders199]

thanks that works, I try and restrict , just trying to get it all to work correctly first.