copy cells left and right of max

[tomrus]

I have a row of numbers: 1, 5, 8, 2, 4, 6, I want to find the max say =max(A1:A6) (which is 8) and copy the values of the cell adjacent of the max (5 and 2), I tried to play with match and hlookup but I wasn't able to make it work. The results should be in new cells like: 5, 8, 2. Any hints much appreciated!

TOM

 

[Andrew Poulsom]

Try:

=INDEX(A1:A6,MATCH(MAX(A1:A6),A1:A6,FALSE)-1)
=INDEX(A1:A6,MATCH(MAX(A1:A6),A1:A6,FALSE))
=INDEX(A1:A6,MATCH(MAX(A1:A6),A1:A6,FALSE)+1)

 

[DonkeyOte]

if this as trivial in "real" life as your example suggest I'd suggest the following assuming you wanted 5 in B1, 8 in B2 and 2 in B3

do B2 first (just max)

=MAX($A$1:$A$6)

then do the others, preceeding value

=OFFSET($A$1,MATCH($B$2,$A$1:$A$6,0)-2,0)

latter value

=OFFSET($A$1,MATCH($B$2,$A$1:$A$6,0),0)

If it's more complex than this you could use a CHOOSE based on row position with a dynamic offset based on Match of MAX.

 

[tomrus]

guys, you are something! you solved the problem in 5 minutes, your input was so much appreciated!

Tom