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

In D2, Array-Enter this formula (enter using Ctrl-Shift-Enter)

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$100=MAX($B$2:$B$100),ROW($B$2:$B$100)),ROW(A1))),"")

and copy down until it returns blanks.

If your Excel doesn't have IFERROR (not sure about 2007), use

=IF(ISERROR(INDEX(A:A,SMALL(IF($B$2:$B$100=MAX($B$2:$B$100),ROW($B$2:$B$100)),ROW(A1)))),"",INDEX(A:A,SMALL(IF($B$2:$B$100=MAX($B$2:$B$100),ROW($B$2:$B$100)),ROW(A1))))

also entered using Ctrl-Shift-Enter

Answer 4

Hello Bernie Deitrick

Thanks for your effort...

I have tried both formulas but all I get is the results for just the max equal numbers

and the adjacent names only...

I also need the rest of the adjacent names for the max in descending order...

Just like the fig I sent...  92, 92,     90, 86, 80 and so on...

Thanks a lot so far so good.

If you can combine it all together in one formula, it will be what I need.

Please help,

Best regards.

Atmaz

Answer 5

Hello Bernie Deitrick,

Thanks so much...

Doing exactly Just what I needed...

Once again I can't thank you enough...

Good luck with what ever you do...

Best regards,

Atmaz Gural