Return the Highest Letter in a Range

[sjones2017]

Hello All:

Longtime listener, first time caller....

I have a column of numbers with an adjacent column of letters, the first column can contain multiples of the same number but have a different letter code assigned. I need to return the highest letter in the range of the number selected. There are two issues that I am dealing with. 1)The formula that I have: {=MAX(IF(A1:A8=C1,B1:B8))} returns a value of '0'....I am assuming that a letter does not have a numeric value assigned. 2) The order in which I need to rank the letters is....AA, A, B, C....if you sort the letters, it sorts them as A, AA, B, C.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]AA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]{=MAX(IF(A1:A8=C1,B1:B8))} - Returns 0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]** Would like for value to be returned as: AA **[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]AA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DILIPandey]

1 is having values AA,A,B,C. Which you want to pick ?


Regards,
DILIPandey

 

[DILIPandey]

Also, below formula can give you the row based on your criteria but it will be last row irrespective on what value is there in column B against 1s in column A.


=MAX(IF(A1:A8=C1,ROW(B1:B8)))


Regards,
DILIPandey

 

[sjones2017]

Thank you for the reply, I would like for the return value to be 'AA'.

Thanks,

sjones2017

 

[Eric W]

Welcome to the forum.

Perhaps:

=IFERROR(INDEX({"AA","A","B","C"},AGGREGATE(15,6,IF(A1:A8=C1,MATCH(B1:B8,{"AA","A","B","C"},0)),1)),"No match")
with Control+Shift+Enter.

If you have a longer list of values, you may want to put them in a range somewhere instead of coding the 2 array constants in the formula.

 

[sjones2017]

That's getting there....I am not even going to try and decipher that function....lol.....The function did return the letter A, just as the computer would see it as the highest letter in the range....I am wanting the computer to see 'AA' as highest, 'A' as second, 'B' as third and 'C' as fourth as the hierarchy. Many thanks for your reply.

sjones2017

 

[Eric W]

It returns AA for me:

Book1
	A	B	C
1	1	AA	1
2	1	A	AA
3	1	B
4	1	C
5	2	AA
6	2	A
7	2	B
8	2	C
Sheet10

Cell Formulas
Range	Formula
C2	{=IFERROR(INDEX({"AA","A","B","C"},AGGREGATE(15,6,IF(A1:A8=C1,MATCH(B1:B8,{"AA","A","B","C"},0)),1)),"No match")}
Press CTRL+SHIFT+ENTER to enter array formulas.

The hierarchical order in this formula is determined by the order of the elements in the 2 array constants. Did you confirm the formula with Control+Shift+Enter?

 

[sjones2017]

That worked! Many thanks!

Regards,

sjones2017

 

[Eric W]

Glad we could help!