# copy cells left and right of max

#### tomrus

##### New Member
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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Andrew Poulsom

##### MrExcel MVP
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

##### MrExcel MVP
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

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

Tom

Replies
1
Views
154
Replies
4
Views
249
Replies
15
Views
303
Replies
7
Views
2K
Replies
5
Views
353

1,190,919
Messages
5,983,585
Members
439,852
Latest member
balasat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back