# Find maximum data for set of cells at a time in excel

I want to find Max value for a point (eg. A03) from column "DR" and return the corresponding value from column (Combination). I can do that for single point at a time but I have 500 points with each having multiple values in different rows and want to do in a single run. I have included an image of the what I have as a raw data and another image of what actually I want my result to look like.

## 2 Answers

Copy column A (Point) and paste it where you want the result and Remove Duplicates (Data, Remove Dupliates) and Blank rows for example column E, say E3 is the First value of column A, in F3 write the following:

=IFERROR(INDEX($B$3:$B$19,SUMPRODUCT(--($A$3:$A$19=E3)*(--$C$3:$C$19=MAX(IF($A$3:$A$19=E3,$C$3:$C$19,0)))*ROW($B$3:$B$19))-2,1),"")

You have to Press Ctrl + Shift + Enter at the same time (Array Formula)

B3:B19 is the column Combination from which you need to return the result
A3:A19 is the column Point of A
C3:C19 is the column DR of number to get their maximum
$to make the array reference fix You can drag the formula down The sumproduct find the corresponding Row of the Max of each similar A in Point Index will return the Combination result you need of the corresponding Row Remember to change the references according to your data before proceeding: A3:A19 --->$A$3:$A$500 for example B3:B19 --->$B$3:$B$500 C3:C19 --->$C$3:$C$500 • Thank you very much for the help. I have included an image to the question to what I want as my result. I am a beginner to excel and sorry for the trouble. Commented Mar 21, 2017 at 0:34 • I have finally completed. Commented Mar 21, 2017 at 0:39 My Excel notation is a bit rusty, but you designate ranges like so: • A1:B2 will include all these cells: A1 A2 B1 B2 Modify that as needed. Vis–à-vis, you probably need =max(C3:C556) To make them absolute definitions, preceed the co-ordinate with a $. I.e., if you copy the contents of a cell, then paste to a different cell, references made within the cell's data, such as A1, will adjust by offsetting the column or row designated: those are known as “relative” references because they are internally handled as relative to the cell in which they occur.
To have that example always reference cell A1, it must be $A$1.

• thank you for your answer but I am sorry my question was not properly represented in text format. So i have uploaded an image for better reference. Commented Mar 17, 2017 at 2:41