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

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