Highest Value(s) displaying text

[Newbienew]

Hello,

I am looking for a formula that will sort a data set and return a text for the highest number out of the set in C16 down. As you see there are two 9s so the text would display for those numbers if they are the highest.

In the event, I get blindsided as it happens to me a lot. I am also asking for a second formula that would allow for the second-highest to do the same. So if there are two 9s and an a 8 a formula would be able to display text for that one as well.

I attempted to use IF and Max which didn't work out for me and a countif max which I couldn't figure out. Please help.

Awards Stats.xlsx
	A	B	C
2	j	OSVET	3
3	j	OSVET	3
4	j	OSVET	3
5	k	OSVET	3
6	k	OSVET	3
7	k	OSVET	3
8
9
10
11
12
13
14
15		osvet
16	j	9
17	k	9
18		0
19		0
20		0
21		0
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=IFERROR(VLOOKUP(B2,Sheet2!$A$2:$B$6,2,FALSE),"")
B16:B21	B16	=SUMIFS($C$2:$C$11,$A$2:$A$11,A16,$B$2:$B$11,$B$15)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B16:B21	Cell Value	contains ""	text	NO

 

[Newbienew]

Following Up on needed help

 

[Newbienew]

So, I am having an issue with my formula that I can't figure out. I formula in column d is supposed to display who would get an award based on the highest number. It is also supposed to display the second-highest and display two awards. Anything lower than this is just blank, as you see below. The second-highest is not the second-highest for the award. How do I get this formula to work for what I am trying to achieve?

AwardsS tats.xlsx
	A	B	C	D
2	J	3	9
3	K	4	12
4	K	5	15
5	k	6	18
6	k	7	21	2 AWARD
7	L	8	24
8	L	9	27
9	L	10	30
10	L	11	33	AWARD
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=IF(ISBLANK(A2),"",B2*Sheet2!$B$4)
D2:D10	D2	=IF(C2=MAX($C$2:$C$11),"AWARD",IF(C2<SMALL($C$2:$C$11,4),"",IF(C2=SMALL($C$2:$C$11,5),"2 AWARD","")))

 

[Newbienew]

So, I am having an issue with my formula that I can't figure out. I formula in column d is supposed to display who would get an award based on the highest number. It is also supposed to display the second-highest and display two awards. Anything lower than this is just blank, as you see below. The second-highest is not the second-highest for the award. How do I get this formula to work for what I am trying to achieve?

Awards Stats.xlsx
	A	B	C	D
1	OSVET
2	J	3	9
3	K	4	12
4	K	5	15
5	k	6	18
6	k	7	21	2 AWARD
7	L	8	24
8	L	9	27
9	L	10	30
10	L	11	33	AWARD
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=IF(ISBLANK(A2),"",B2*Sheet2!$B$4)
D2:D10	D2	=IF(C2=MAX($C$2:$C$11),"AWARD",IF(C2<SMALL($C$2:$C$11,4),"",IF(C2=SMALL($C$2:$C$11,5),"2 AWARD","")))

 

[FormR]

Hi, your formula is testing for 5th smallest rather than the 2nd largest.

Maybe you could try like this instead:

=IF(C2=LARGE($C$2:$C$10,1),"AWARD",IF(C2=LARGE($C$2:$C$10,2),"2 AWARD",""))

 

[Phuoc]

Try this

Book1
	A	B	C	D
1	OSVET
2	J	3	9
3	K	4	12
4	K	5	15
5	K	6	18
6	K	7	21	2 AWARD
7	L	8	24
8	L	9	27
9	L	10	30
10	L	11	33	AWARD
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=IF(C2=MAX($C$2:$C$11),"AWARD",IF(C2=AGGREGATE(14,6,$C$2:$C$11/($A$2:$A$11<>INDEX($A$2:$A$11,MATCH(MAX($C$2:$C$11),$C$2:$C$11,0))),1),"2 AWARD",""))

 

[Fluff]

@Newbienew
In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

This time I have merged both threads.

 

[Newbienew]

@FormR Thank you very much for your response and assistance on the topic

@Phuoc I would like to thank you as well for your response and assistance as well.

@Fluff I apologize for the double posting and breaking the rules. I was once told that if your posting doesn't get a response in so many days to repost it. In the event that I do make a posting and it does not get a response in so many does what is the best approach other than responding to my own posting?

 

[Fluff]

If you don't get a response you should just Bump the thread by posting to it.