# max, min and copy entire corresponding row

#### tomrus

##### New Member
I have 4 coloums D,E,F,G and I want to find the MAX and MIN of D4:D5203, E4:E5203, F4:F5203, G4:G5203. Then find the cell where the max and min occors and copy the entire row at the bottom of the sheet (or in e new sheet if it is easier). In other words I have max of the column D, then I want copy the row where it occours with the corresponding values, max column E and copy the row and so on. The same for the minimum, in total I shoud have 4 rows for the max and 4 for the min, hope someone can help. Many thanks!

Tom

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### DonkeyOte

##### MrExcel MVP
what are the "corresponding values"

you can use MATCH(MAX(D:D),D:D,0) to find the row of the largest value in the given range -- then use in conjunction with say an OFFSET or INDEX to retrieve additional values based on given the row

=INDEX(A:A,MATCH(MAX(D:D),D:D,0),1)

would return value in A for row where MAX in D appears in D.

#### tomrus

##### New Member
thanks for the answer! What's the 0 at the end of MATCH(MAX(D:D),D:D,0)?

what are the "corresponding values"

For corresponding values I meant find max and copy all the other values in the row, regardless they're max, min or not.

you can use MATCH(MAX(D:D),D:D,0) to find the row of the largest value in the given range -- then use in conjunction with say an OFFSET or INDEX to retrieve additional values based on given the row

=INDEX(A:A,MATCH(MAX(D:D),D:D,0),1)

would return value in A for row where MAX in D appears in D.

#### DonkeyOte

##### MrExcel MVP
0 equates to boolean FALSE which forces MATCH to find first exact match in a given range.

So you may see written as MATCH(criteria,range,false) rather than MATCH(criteria,range,0)

#### tomrus

##### New Member
many thanks! it works g8!

Replies
2
Views
83
Replies
36
Views
495
Replies
1
Views
120
Replies
1
Views
94
Replies
6
Views
206

1,190,592
Messages
5,981,822
Members
439,736
Latest member
Nathan20

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