Return 2nd highest value

[ste33uka]

Hi would anyone have a formula to return second highest value, where match is found, in this case the word "apple"
and a formula to return adjacent cell.
Formulas would be in G1 and H1
Have attacted imige to show what result would look like
Thanks

 

[jasonb75]

In G1, change 2 to 3 for 3rd largest, etc.

=AGGREGATE(14,6,B3:B10/(A3:A10=F1),2)

The formula for H1 is open to interpretation, in the event of ties this will give you the highest percentage where A and B match F1 and G1 respectively.

=MAXIFS(E3:E10,A3:A10,F1,B3:B10,G1)

 

[ste33uka]

In G1, change 2 to 3 for 3rd largest, etc.

=AGGREGATE(14,6,B3:B10/(A3:A10=F1),2)

The formula for H1 is open to interpretation, in the event of ties this will give you the highest percentage where A and B match F1 and G1 respectively.

=MAXIFS(E3:E10,A3:A10,F1,B3:B10,G1)

Thanks alot 1st formula is what i need
2nd formula i think i need something else

 

[jasonb75]

As I said, it is open to interpretation, it was not clear from your post if you wanted the percentage that relates to the second highest value or you wanted the second highest percentage. In your example the result for both is the same, however in other samples it may differ.

If this is still wrong then you will need to provide more information, I'm not going to spend all day guessing.

=AGGREGATE(14,6,E3:E10/(A3:A10=F1),2)

 

[ste33uka]

As I said, it is open to interpretation, it was not clear from your post if you wanted the percentage that relates to the second highest value or you wanted the second highest percentage. In your example the result for both is the same, however in other samples it may differ.

If this is still wrong then you will need to provide more information, I'm not going to spend all day guessing.

=AGGREGATE(14,6,E3:E10/(A3:A10=F1),2)

Thanks i think 2nd formula will work

 

[ste33uka]

.