Filter alphanumeric values and find maximum

[dcuthill]

I have a column of alphanumeric data and want to determine the maximum value in the range but only for values within the range that have a prefix of "EP".

I have been able to develop this which gives me the maximum alphanumeric but cannot figure out how to filter for only the EP prefix. The data is currently in the static column of I6 to I26 but the lower range could extend beyond row 26 in future.

=LOOKUP(2,1/(COUNTIF(I6:I26,">"&I6:I26)=0),I6:I26)

any guidance would be appreciated. Thank you

 

[Alex Blakenburg]

I am not clear on what you are trying to do an some sample data using XL2BB would be useful..
Instructions on using this tool can be found here: XL2BB Add-in

In the meantime does this do what you want ?

=TAKE(SORT(FILTER($I$6:$I$26,LEFT($I$6:$I$26,2)="EP")),-1)

 

[Phuoc]

Try change to:

=LOOKUP(2,1/(COUNTIFS(I6:I26,"EP*",I6:I26,">"&I6:I26)=0)/(LEFT(I6:I26,2)="EP"),I6:I26)

 

[dcuthill]

=TAKE(SORT(FILTER($I$6:$I$26,LEFT($I$6:$I$26,2)="EP")),-1)

Thanks this achieves what I want. As to what I am trying to do. The column has data that starts with EP0000 and RDL0000 - if I use the formula, I posted then the RDL values are always considered as the maximum, but I wanted to know which the EP values was the greatest.

 

[dcuthill]

Try change to:

=LOOKUP(2,1/(COUNTIFS(I6:I26,"EP*",I6:I26,">"&I6:I26)=0)/(LEFT(I6:I26,2)="EP"),I6:I26)

Thanks I should have also commented that this formula also achieved the desired result. Many ways to "skin a cat" so to speak.

 

[Alex Blakenburg]

Thank you for testing and giving feedback on all the options offered. Glad we could help.