Function to find the maximum in range considering the artificial hierarchy placed on prefixed letter values

[TonyChestnut]

Hi,

Hopefully the title of this thread makes some sense. Let me explain what I am trying to do...

I have a range of values, A1:A5. In this hypothetical situation say that they are the following:

A1 = P1
A2 = T1
A3 = C4
A4 = PC1
A5 = C6

The letter/s on the left relate to a hierarchy where P is the lowest, T is next, then C, and PC is the 'highest'.

What I need is a function (cannot be a macro, and not possible to use helper cells due to the extent of the project) that will return the 'highest' result, first considering the Letter/s on the left, and then the numbers on the right.

The function should return, in the given scenario, the value of A4. If that wasn't there then the value of A5.

I've been ripping my hair out trying various combinations of MAX, IF, SUBSTITUTE, LARGE, VALUE, N, LOOKUP, etc, but I fail everytime.

Please help me win.

Kind regards,

 

[TonyChestnut]

My suggested formula should work with any column of data

Hi Harry,

Sorry for the confusion, you're right, but I meant to specify a row...F28:DA28.

How can I edit it to get the 'maximum' from a row?

I've tried breaking down the formula to help me understand what to change, but arrays are very new to me.

Kind regards,

Tony

 

[barry houdini]

OK try this version

=LOOKUP(2,1/COUNTIF(F28:DA28,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&MATCH(2,1/MMULT((F28:DA28=LOOKUP(2,1/COUNTIF(F28:DA28,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&ROW(INDIRECT("1:999")))+0,TRANSPOSE(COLUMN(F28:DA28)^0)))

 

[TonyChestnut]

AmAzInG! Thanks Barry Houdini.

Thanks to Marcol/Marcelo too...I'll keep a record of your solutions for my own learning and future troubleshooting.

This forum is the best by far!