send back cell to the right of max :)

**HeikEve** · 05-23-2013, 09:52 PM

Hi,
I am sorting some data in rows. I need to find the max value and then the next two values after that, whatever they are, and then reference all three in a row. I know how to do =MAX but how do I do an offset of some sort to find the two cells to the right of the max?

ex:
output
max - value after - value after that

data
0 10 45 35 40 20 15 12 10

**Tony Valko** · 05-23-2013, 10:01 PM

Try this...

With your data in the range A1:I1...

Enter this formula in A5 for the max value:

=MAX(A1:I1)

Enter this formula in B5 and copy across to C5:

=INDEX($A1:$I1,MATCH($A5,$A1:$I1,0)+COLUMNS($B5:B5))

This assumes there will always be 2 cells to the right of the max cell.

**HeikEve** · 05-23-2013, 10:09 PM

Biff. Thanks. Worked!

**vlady** · 05-23-2013, 10:11 PM

how about offset maybe.

=MAX(OFFSET(A1:H1,0,MATCH(MAX(A1:H1),A1:H1,0)+1))

**HeikEve** · 05-23-2013, 10:14 PM

Wait, actually - when the first value in the row was the max, the formula =INDEX($A1:$I1,MATCH($A5,$A1:$I1,0)+COLUMNS($B5:B5)) shoots back #N/A for the second two values.

Any ideas?

**Tony Valko** · 05-23-2013, 10:16 PM

You're welcome. Thanks for the feedback!

**Tony Valko** · 05-23-2013, 10:24 PM

Works OK for me.

The only time it won't work is if there are not 2 cells in the range to the right of the cell that contains the max value.

Here's a small sample file that demonstrates this:

HeikEve.xls