Max Date in a Data set

[rhouston08]

I would like to retrieve the max number in a dataset. For example, in the table below, I would like to find the max check no and return the date associated with that check no, and I would like the cells in column D to show the date for the max set.


[TABLE="width: 220"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 48pt;" width="64"> <col style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;" width="97"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl66, width: 97, bgcolor: transparent"]C[/TD]
[TD="class: xl66, width: 68, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Client[/TD]
[TD="class: xl68, bgcolor: transparent"]Check no[/TD]
[TD="class: xl68, bgcolor: transparent"]Day deposited[/TD]
[TD="class: xl67, bgcolor: transparent"]Date[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0001[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/1/2014[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1/16/2014[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0001[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/6/2014[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1/16/2014[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0001[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/11/2014[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1/16/2014[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0001[/TD]
[TD="class: xl68, bgcolor: transparent"]4[/TD]
[TD="class: xl69, bgcolor: transparent"]1/16/2014[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1/16/2014[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0002[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/2/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0002[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/7/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0002[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/12/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0003[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/3/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0003[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/8/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0003[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/13/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/4/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/9/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/14/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]4[/TD]
[TD="class: xl69, bgcolor: transparent"]1/19/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]5[/TD]
[TD="class: xl69, bgcolor: transparent"]1/5/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0005[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/10/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0005[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/15/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0005[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/20/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

Try this: =VLOOKUP(MAX($B$2:$B$19),$B$2:$C$19,2,FALSE)


EDIT: Ignore this solution. I missed the part about Max per client.

 

[skywriter]

Confirm the formula with control shift enter and then copy down.

In D2

=MAX(IF($A$2:$A$19=A2,$C$2:$C$19))

Excel 2010
	A	B	C	D
1	Client	Check no	Day deposited	Date
2	1	1	1/1/2014	1/16/2014
3	1	2	1/6/2014	1/16/2014
4	1	3	1/11/2014	1/16/2014
5	1	4	1/16/2014	1/16/2014
6	2	1	1/2/2014	1/12/2014
7	2	2	1/7/2014	1/12/2014
8	2	3	1/12/2014	1/12/2014
9	3	1	1/3/2014	1/13/2014
10	3	2	1/8/2014	1/13/2014
11	3	3	1/13/2014	1/13/2014
12	4	1	1/4/2014	1/19/2014
13	4	2	1/9/2014	1/19/2014
14	4	3	1/14/2014	1/19/2014
15	4	4	1/19/2014	1/19/2014
16	4	5	1/5/2014	1/19/2014
17	5	1	1/10/2014	1/20/2014
18	5	2	1/15/2014	1/20/2014
19	5	3	1/20/2014	1/20/2014
Sheet1

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

 

[Teeroy]

I like skywriter's solution, getting the max date only, unless there is a possibility that checks can be deposited out of order. Consider, if check 4 is deposited after check 5 what date would you want returned?

 

[rhouston08]

Thank you, it worked!