max, min and copy entire corresponding row

[tomrus]

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

 

[DonkeyOte]

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]

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]

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]

many thanks! it works g8!