Array with Max for repeated value?

[George J]

I'm going to give the long explanation as this is confusing me - my apologies.

In a workbook I have a client name in column A. I am trying to lookup this name (minus last character) on my "Evidence" sheet. I want to return the data from Column L on that sheet, but if the client name is repeated more than once, to look at column G and return column L's text for the highest value in column G for that client.

I hope that makes sense.
Due to the data comping from a program, I need to drop the last character of the client when searching, otherwise it does not get found. This is even after using clean and trim to get rid of any extra characters (with a client of 20 characters on both sheets it did not find the text even though i checked the string length for both was 20 - hence my dropping the last character, which got that part to work).

This is what I am left with so far.
=SUMPRODUCT(--(ISNUMBER(MATCH(F2&"*",Extracted!$M$6484:$M$6488,0)))*(MAX(Extracted!$G$6484:$G$6488)),(Extracted!$L$6484:$L$6488))

Any suggestions?

 

[George J]

No longer need to drop the last character, but the original data has extra characters still.

Still no closer as I need the match to look for the partial client name, but not sure how to put that into an array.
=MAX(IF(INDEX(Extracted!$G$1:$G$7000,MATCH(LEFT(A29,LEN(A29))&"*",Extracted!$M$1:$M$7000,0),1),Extracted!$G$1:$G$7000))

 

[George J]

Thanks to all who had a look at this.
It's probably a bit messy and could be simplified, but it works (will test it more later)

=SUMPRODUCT(MAX(--(Extracted!$M$2:$M$7000=INDEX(Extracted!$M$1:$M$7000,MATCH(A46&"*",Extracted!$M$1:$M$7000,0)))*(Extracted!$G$2:$G$7000)))