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.

A	B	C	D
Client	Check no	Day deposited	Date
0001	1	1/1/2014	1/16/2014
0001	2	1/6/2014	1/16/2014
0001	3	1/11/2014	1/16/2014
0001	4	1/16/2014	1/16/2014
0002	1	1/2/2014
0002	2	1/7/2014
0002	3	1/12/2014
0003	1	1/3/2014
0003	2	1/8/2014
0003	3	1/13/2014
0004	1	1/4/2014
0004	2	1/9/2014
0004	3	1/14/2014
0004	4	1/19/2014
0004	5	1/5/2014
0005	1	1/10/2014
0005	2	1/15/2014
0005	3	1/20/2014

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

 

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