Need help with a match formula that returns the greatest value out of the matches.

[benvett14]

I am trying to create a formula that will search text and return only the value that has the greatest number at the end, but the rest of the value prior to the last number must match. I have attached a photo below and the file to this post.

I am trying to search column K and have a formula in column L that returns the same value if the last number in the column K value is the greatest out of any value in column K when the rest of the value matches. I have typed out the result I would like for a few example rows below.
Excel help1.PNG

The working formula that I thought would work but is returning the "value" error is. : =IF(MAX(--(LEFT($K$2:$K$5360,16)=LEFT(K2,16))*RIGHT($K$2:$K$5360,1))=--RIGHT(K2,1),K2,"")


Any help is greatly appreciated!

[Bo_Ry]

Please try at L2

=IF(MAX(IF(LEFT($K$2:$K$5360,LEN(K2)-1)=LEFT(K2,LEN(K2)-1),--RIGHT($K$2:$K$5360)))=--RIGHT(K2),K2,"")

Array formula is slow for large data

If you can change Revision column to number or use helper column
M2
=--D2

then at N2
=IF(MAXIFS($M$2:$M$5360,$K$2:$K$5360,LEFT(K2,LEN(K2)-1)&"*")=M2,K2,"")
This is a lot faster than array formula