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:

ID#	Year	MAX Year
12345	2011	2012
12345	2012	2012
23456	2016	2016
34567	2010	2012
34567	2012	2012
45678	2013	2013
56789	2014	2014

<tbody>
</tbody>

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

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

5d​

Array Formulas Cell Formula C2 {=MAX(IF($A$2:$A$8=A2,$B$2:$B$8))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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