Excel Formula that Yields the Most Recent Date

[Macpop]

Good day All,

I am trying to derive a formula that will lookup ID#s then retrieve the recent date aligned to it. IDs will be repeated with different dates. I want to retrieve the most recent.

Example:

A B C

3406 3/1/12
4500 3/4/12
3406 4/1/14
3406 3/12/13
4500 1/1/14

Based on the example, I should be able to create a formula in column C and drag down that would yield the dates 4/1/14 for ID 3406 and 1/1/14 for ID 4500.

Can anyone assist with this please? I will be eternally grateful. Thanks!

 

[Weazel]

not sure this is what you mean but...

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]3406[/TD]
[TD="align: right"]3/1/2012[/TD]
[TD="align: right"]4/1/2014[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]3/4/2012[/TD]
[TD="align: right"]1/1/2014[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3406[/TD]
[TD="align: right"]4/1/2014[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3406[/TD]
[TD="align: right"]3/12/2013[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[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"]C2[/TH]
[TD="align: left"]=MAX(INDEX(($A$2:$A$6=A2)*$B$2:$B$6,))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Macpop]

not sure this is what you mean but...

Excel 2010

	A	B	C
2
3
4
5
6

<TBODY>
[TD="align: right"]3406
[/TD]
[TD="align: right"]3/1/2012
[/TD]
[TD="align: right"]4/1/2014
[/TD]

[TD="align: right"]4500
[/TD]
[TD="align: right"]3/4/2012
[/TD]
[TD="align: right"]1/1/2014
[/TD]

[TD="align: right"]3406
[/TD]
[TD="align: right"]4/1/2014
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3406
[/TD]
[TD="align: right"]3/12/2013
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4500
[/TD]
[TD="align: right"]1/1/2014
[/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="bgcolor: #dae7f5"]C2
[/TH]
[TD="align: left"]=MAX(INDEX(($A$2:$A$6=A2)*$B$2:$B$6,))
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

Good Job! this works perfectly! One question, can you briefly describe why this formula works? Thanks for solving this.

 

[Weazel]

the index is taking the range based on the condition of column A that is equal to a1 and multiplying it by the row to return the latest date to the max function.
writing it this way doesn't require control shift enter. the formula below essentially does the same thing and, IMO is a little more straight forward but would require control shift enter to calculate correctly.

=MAX(IF($A$1:$A$5=A1,$B$1:$B$5))

 

[Macpop]

the index is taking the range based on the condition of column A that is equal to a1 and multiplying it by the row to return the latest date to the max function.
writing it this way doesn't require control shift enter. the formula below essentially does the same thing and, IMO is a little more straight forward but would require control shift enter to calculate correctly.

=MAX(IF($A$1:$A$5=A1,$B$1:$B$5))

Thank you, I understand much better now.