Hey, Thanks for writing!!
You can use the following formula:
=INDEX(A1:A9,MATCH(MAX(CODE(A1:A9)+
IFERROR(CODE(MID(A1:A9,2,1)),0)),CODE(A1:A9)+IFERROR(CODE(MID(A1:A9,2,1)),0),0))
Please change the referencing as per the requirement, in my case the values were in A1:A9.
Make sure you press Control+Shift+Enter while entering the formula, since its an array operation.
Hope this will work.
Thanks/Raj
In that case, try ..
rarmn
A B C D E F G H I 2 A D E V AB AC AJ AK 3 4 AK
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas
Cell Formula A4 =LOOKUP("ZZ",2:2)
<tbody>
</tbody>
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
For unsorted try this array formula.I have a list both sorted and unsorted.
Excel Workbook | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | 'Max': | ||||||||||||||||||||
2 | A | D | E | V | AK | AA | BK | AJ | BS | AZ | B | B | X | BB | BS | ||||||
3 | AZ | ZA | ZA | AZ | ZA | ||||||||||||||||
4 | |||||||||||||||||||||
5 | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | XX | |||
6 | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | Q | |||
rarmn unsorted |
For unsorted try this array formula.
There may be a shorter way, but I couldn't see it!
This assumes 1 or 2 UPPER CASE letters and/or blank cells in the range.
Formula in S2 is copied down (after Ctrl+Shift+Enter confirmation)
rarmn unsorted
* A B C D E F G H I J K L M N O P Q R S 1 * * * * * * * * * * * * * * * * * * 'Max': 2 A D E V AK AA BK AJ BS AZ B * * B * X BB * BS 3 * * AZ * * ZA * ZA AZ * * * * * * * * * ZA 4 * * * * * * * * * * * * * * * * * * * 5 XX XX XX XX XX XX XX XX XX XX XX XX XX XX XX XX XX * XX 6 A B C D E F G H I J K L M N O P Q * Q
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:49px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas
Cell Formula S2 {=TRIM(CHAR(LEFT(MAX(CODE(RIGHT(" "&A2:Q2,1))+100*CODE(LEFT(RIGHT(" *"&A2:Q2,2),1))),2))&CHAR(RIGHT(MAX(CODE(RIGHT(" "&A2:Q2,1))+100*CODE(LEFT(RIGHT(" *"&A2:Q2,2),1))),2)))}
<tbody>
</tbody>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4