MAX Date Value based on Unique ID#

[natetheblade2]

I am having a problem coming up with a MAX function that will return the last date in a sequence.

I have tried the =MAX(IF($A$n:$A$n=An,$B$n:$B$n)) function, although it returns the MAX date in the entire list rather than just the highest number date for the given unique ID number.

For example, here is what I would like:[TABLE="width: 500"]
<tbody>[TR]
[TD]ID#[/TD]
[TD]Year[/TD]
[TD]MAX Year[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]2012[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]2016[/TD]
[TD]2016[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]2010[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]2012[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]2013[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]56789[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[/TR]
</tbody>[/TABLE]

What I get with the previous formula is 2016 for the MAX Year, rather than 2012 for those ID#'s that repeat.

Any idea of how to make Max Year return that max year for the ID number?

 

[Dave Patton]

Excel 2010
	A	B	C
1	ID#	Year	MAX Year
2	12345	2011	2012
3	12345	2012	2012
4	23456	2016	2016
5	34567	2010	2012
6	34567	2012	2012
7	45678	2013	2013
8	56789	2014	2014
5d

Cell Formulas
Range	Formula
C2	{=MAX(IF($A$2:$A$8=A2,$B$2:$B$8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Ensure that you array enter the formula

 

[natetheblade2]

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]ID#[/TD]
[TD="bgcolor: #FAFAFA"]Year[/TD]
[TD="bgcolor: #FAFAFA"]MAX Year[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12345[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2011[/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12345[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2012[/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]23456[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2016[/TD]
[TD="align: right"]2016[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]34567[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2010[/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]34567[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2012[/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45678[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2013[/TD]
[TD="align: right"]2013[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]56789[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2014[/TD]
[TD="align: right"]2014[/TD]

</tbody>

5d​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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(IF($A$2:$A$8=A2,$B$2:$B$8))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Ensure that you array enter the formula

Ha! I thought I did that. Thanks. That worked... rookie mistake!

 

[Nishant94]

You dont need ctrl+shift+enter for this.

Unknown
	A	B	C
1	ID#	Year	MAX Year
2	12345	2011
3	12345	2012	2012
4	23456	2016	2016
5	34567	2010
6	34567	2012	2012
7	45678	2013	2013
8	56789	2014	2014
9
Sheet7

Cell Formulas
Range	Formula
C2	=IF(A2&B2=A2&MAX(INDEX(--($A$2:$A$8=A2)*($B$2:$B$8),0)),MAX(INDEX(--($A$2:$A$8=A2)*($B$2:$B$8),0)),"")

 

[Nishant94]

This will work without ctrl+shift+enter.

Unknown
	A	B	C
1	ID#	Year	MAX Year
2	12345	2011	2012
3	12345	2012	2012
4	23456	2016	2016
5	34567	2010	2012
6	34567	2012	2012
7	45678	2013	2013
8	56789	2014	2014
9
Sheet7

Cell Formulas
Range	Formula
C2	=MAX(INDEX(--($A$2:$A$8=A2)*($B$2:$B$8),0))