Find the Max Value when a cell contain Text and Number

[sethu29]

Hi Support,

I have a huge data in coulmn A.

For Eg

In A column:
AA0001
AA0002
BA0001
BA0002
BA0003
CA0003
CA0004
Now i want to find out the maximum of BA*. Which means the answer would be BA0003
If i type CA in a cell, the answer has to CA0004.Even CA0001 and CA0002 might not be availabel. The answer has to be CA0004. Take an example if im typing AA on B1 cell, the answer need to be placed in C1 cell that AA0002. Please help me for this formula

[Teeroy]

It's not refined but it works (so long as your examples were indicative of the data).
Place this formula in C1 and enter as an array formula (using CTRL+SHIFT+ENTER)

=B1&TEXT(MAX(IF(LEFT(A1:A7,2)=$B$1,VALUE(MID(A1:A7,3,LEN(A1:A7))),"")),"0000")


Note: There is no space in the four zeros but the forum keeps inserting one.

[shrivallabha]

Assuming C1 Hold criterion, similar formula to be CSE'd:
=C1&TEXT(MAX(RIGHT(A1:A7,LEN(A1:A7)-2)*ISNUMBER(FIND(C1,A1:A7,1))),"0000")

[Teeroy]

I like Shrivallabha's FIND test better than my IF. It can be extended to a more general solution (but not necessarily simpler :rotlaugh:) which will account for any number length, if you need it. Once again CSE'd.

=INDEX(A1:A7,MATCH(MAX(RIGHT(A1:A7,LEN(A1:A7)-2)*ISNUMBER(FIND(B1,A1:A7))),RIGHT(A1:A7,LEN(A1:A7)-2)*ISNUMBER(FIND(B1,A1:A7)),0),1)