Clash Royale CLAN TAG#URR8PPP

Excel - Location and prioritisation of values in a range

Hi and thanks very much for taking the time to read/respond.

I'm struggling with trying to adapt a very advance formula given by tigeravatar here: tigeravatar

I have an almost identical issue, but have the following possible states:
A, B, C, D, and corresponding levels of priority.

In a given range, multiple entries in any of these four categories could be made. But I need to return only the highest value regardless of all other entries.

Here's the original formula.

=INDEX("","D","C","B","A",MATCH(SUMPRODUCT(4,3,2,1,--(COUNTIF('Sheet 002'!E29:E32,"A","B","C","D")>0)),0,1,2,3,4))


The only problem being that in the formula above if B and C co-occur it displays A, and if B and C only display if they occur in isolation.

Thanks in advance for your and any inputs you're willing to share!

1 Answer
1

If you were willing to have a helper column, you could convert the letters to numbers using =CODE(). This column can then be ranked by taking the minimum (A=65, B=66, etc.) Note that CODE() is case-sensitive. You can find the highest ranking letter by using a formula like this:

=CODE()

CODE()

=INDEX(E29:E32,MATCH(MIN(F29:F32),F29:F32,0))

=INDEX(E29:E32,MATCH(MIN(F29:F32),F29:F32,0))

Screenshot

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.