asked on

Highest letter in a row/column

Hi,

In excel how to get the highest letter in a column/line. For ex. in the following exemple A1:A5 the highest letter is "D".

| A | B |
------------------------
1| A |
2| B |
3| A |
4| D|
5| A |

best regards

Shums Faruk

Assuming you have 1, 2, 3, 4, 5 in A1 to A5 & A, B, A, D, A in B1 to B5
In C1 try below formula:

=LOOKUP(2,1/(COUNTIF($B$1:$B$5,">"&$B$1:$B$5&"~")=0),$B$1:$B$5)

Open in new window

Change the range accordingly
Highest-Letter-in-a-range.xlsx

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi,

When download and open Highest-Letter-in-a-range.xlsx file from shums comment, formula works well, however if try copy/paste na error occurs.
I think is due translation.

Original
=LOOKUP(2,1/(COUNTIF($B$1:$B$5,">"&$B$1:$B$5&"~")=0),$B$1:$B$5)
Translated
=PROC(2;1/(CONTAR.SE($B$1:$B$5;">"&$B$1:$B$5&"~")=0);$B$1:$B$5)

Shums Faruk

Hi,

Is your regional setting and microsoft installed, both are in Portuguese?

Shums Faruk

Here is the link to translate Excel Formula from English to any language:
https://pt.excel-translator.de/translator/
Your translated formula is translated correctly, may be you need to check the regional setting & microsoft installed language.