finding max value in duplicates

**khansen** · 05-30-2013, 04:45 PM

I found a posting for the problem I am trying to solve but can't seem to get it to work.. maybe it is the edition of excel that I am using (2010)??

http://www.excelforum.com/excel-gene...uplicates.html

I need to find the max value form duplicate values (in rows) and have another columns value returned for the row that contains the max value.. I have read that a pivot table will allow one to do this but I have ~64K rows of data. If pivot tables can be used in this case can someone explain, to an excel novice, how to perform the task?

Here is an example:
max lookup example.xlsx

Thanks in advance for any help.

**Ace_XL** · 05-30-2013, 05:10 PM

In H3 use this array formula (confirm with Ctrl+Shift+Enter and not just Enter)
=MAX(IF($A$2:$A$32=G3,$D$2:$D$32))

In I3
=INDEX($B$2:$B$32,MATCH(1,INDEX(($A$2:$A$32=G3)*($D$2:$D$32=J3),0),0))

Copy down