How to display the adjacent cell values based on the two equal or more max values and in descending order of other equal or lower values...

Question

Hello,

I am having difficulty with my max values which are the same.

These max values and the adjacent values always gives the first max adjacent value in the column.

But I need the second equal max adjacent value to be given in the next answer,

instead of the first max adjacent value in the column again...

I have collected some samples down below;

Find The Highest Value And Return Adjacent Cell Value With Formulas

Notes:

1.  If there are multiple largest values in column A, this formula is only get the first corresponding value.

What I need is a formula that is going to give me results as;

D2 = Tom, D3 = Nicol, D4 = Andrew, D5 = Peter and D6 = James  as below;

Please help,

Best regards,

Atmaz Gural

Answer 1

Array enter this using Ctrl-Shift-Enter:

=IFERROR(INDEX(A:A,SUM(IF(($B$2:$B$100+ROUND(0.1/ROW($B$2:$B$100),5))=LARGE($B$2:$B$100+ROUND(0.1/ROW($B$2:$B$100),5),ROW(A1)),ROW($B$2:$B$100)))),"")

Answer 2

Try this version:

=IFERROR(INDEX(A:A,IFERROR(1/(1/SUM(IF($B$2:$B$100>7,IF(($B$2:$B$100+ROUND(0.1/ROW($B$2:$B$100),5))=LARGE($B$2:$B$100+ROUND(0.1/ROW($B$2:$B$100),5),ROW(A1)),ROW($B$2:$B$100))))),"")),"")

Answer 3

Hi,

Had you been using Office365, the solution would have been as simple as this:

1. In cell D2, enter =MAX(B2:B11)
2. In cell E2, enter =FILTER(A2:A11,B2:B11=D2)

Hope this helps.

Answer 4

Hello Mr. Bernie Deitrick,

Once again thanks very much for your efforts and your formula.

Below is the formula that you have written to me last.

I am using it with my original work book and it's working just fine...

But now I have another condition to add to your formula, to give me the

max adjacent values in the column with the figures above number 7 seven,

by the way in my work book the data I am using is from 1 to 25.

In other words if I have 1,3,7,2,5,8,3,2,9,4,11,15,2,1,22 in my data range which is

the B2:B100 in this formula, now I need the max adjacent values in the results 

to start after 7 seven, in this sample of numbers I have given should be 22,15,11,

9,and 8 adjacent values...

=IFERROR(INDEX(A:A,SUM(IF(($B$2:$B$100+ROUND(0.1/ROW($B$2:$B$100),5))=LARGE($B$2:$B$100+ROUND(0.1/ROW($B$2:$B$100),5),ROW(A1)),ROW($B$2:$B$100)))),"")

Please help me again.

Best regards,

Atmaz

Answer 5

Try this version:

=IFERROR(INDEX(A:A,IFERROR(1/(1/SUM(IF($B$2:$B$100>7,IF(($B$2:$B$100+ROUND(0.1/ROW($B$2:$B$100),5))=LARGE($B$2:$B$100+ROUND(0.1/ROW($B$2:$B$100),5),ROW(A1)),ROW($B$2:$B$100))))),"")),"")

Hello Mr. Bernie Deitrick,

Once again thanks so much...

You are absolutely brilliant...

I can not thank you enough, this last formula is also doing just what I needed...

Good luck with everything you do...

Best regards,

Atmaz Gural