Max based on different column

[Ampleford]

My spreadsheet has two non adjacent columns:-

Col 1-Col 2-Col 3
Dog-blank-12 Jan 19
Cat-blank-13 Mar 19
Cat-blank-04 Jan 19
Cat-blank-08 July 19
Elephant-blank-22 May 19
Dog-blank-25 May 19

I'd like a formula for Col 4 that shows me the latest instance (from the dates in column 3) of each entry in Column 1. At the moment - the process does not allow me to sort the columns.

Can anyone help?

 

[Eric W]

Try:

Excel 2012

	A	B	C	D
Animal	Date	Latest
Dog
Cat
Cat
Cat
Elephant
Dog

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12-Jan[/TD]
[TD="align: right"]25-May[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]13-Mar[/TD]
[TD="align: right"]19-Jul[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4-Jan[/TD]
[TD="align: right"]19-Jul[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]19-Jul[/TD]
[TD="align: right"]19-Jul[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8-Jul[/TD]
[TD="align: right"]8-Jul[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]25-May[/TD]
[TD="align: right"]25-May[/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=AGGREGATE(14,6,$C$2:$C$7/(A2=$A$2:$A$7),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

You can use the method described here: https://exceljet.net/formula/max-if-criteria-match

So, if your data was in cells A2:C7, put this formula in D2 and copy down for all rows:

{=MAX(IF($A$2:$A$7=A2,$C$2:$C$7))}

(note that you really do not enter the squiggly brackets, this is an array formula that you must enter with CTRL-SHIFT-ENTER)