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
1	Animal		Date	Latest
2	Dog		12-Jan	25-May
3	Cat		13-Mar	19-Jul
4	Cat		4-Jan	19-Jul
5	Cat		19-Jul	19-Jul
6	Elephant		8-Jul	8-Jul
7	Dog		25-May	25-May

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet13​

Worksheet Formulas Cell Formula D2 =AGGREGATE(14,6,$C$2:$C$7/(A2=$A$2:$A$7),1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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)