Max Date in a Data set

rhouston08

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

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.

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

In D2
Code:
``=MAX(IF(\$A\$2:\$A\$19=A2,\$C\$2:\$C\$19))``

Excel 2010
ABCD
1ClientCheck noDay depositedDate
2111/1/20141/16/2014
3121/6/20141/16/2014
4131/11/20141/16/2014
5141/16/20141/16/2014
6211/2/20141/12/2014
7221/7/20141/12/2014
8231/12/20141/12/2014
9311/3/20141/13/2014
10321/8/20141/13/2014
11331/13/20141/13/2014
12411/4/20141/19/2014
13421/9/20141/19/2014
14431/14/20141/19/2014
15441/19/20141/19/2014
16451/5/20141/19/2014
17511/10/20141/20/2014
18521/15/20141/20/2014
19531/20/20141/20/2014
Sheet1
Cell Formulas
RangeFormula
D2{=MAX(IF(\$A\$2:\$A\$19=A2,\$C\$2:\$C\$19))}
Press CTRL+SHIFT+ENTER to enter array formulas.

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?

Thank you, it worked!

Replies
0
Views
92
Replies
9
Views
375
Replies
16
Views
273
Replies
1
Views
201
Replies
1
Views
596

1,206,756
Messages
6,074,758
Members
446,084
Latest member
WalmitAal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back