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.
I have also attached the excel file and the working formula that I thought would work but is returning the "value" error. : =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!
@Benvett14 Consider making column K a helper column without the last element, and then enter the formula in L as shown in the picture. It produces a consistent result, though not the not the same as in your picture. Note that the third one doesn't match.
Accepted Solutions
@Benvett14 Consider making column K a helper column without the last element, and then enter the formula in L as shown in the picture. It produces a consistent result, though not the not the same as in your picture. Note that the third one doesn't match.