CODEWITHSUNDEEP
excel
Herman
Herman

Reputation: 157

Excel Last Date based on account number

I currently have a formula which is supposed to identify the Last Payment Date per account number, where several payments are shown per account number. The LastPmtDate and Docdate is the same.

{MAX(COLUMN($D1:$D10000)*($D$1:$D$10000=D2))}

The data looks as follows, starting from A1 in the spreadsheet.

AccountCode Amt Amt2 DocDate DocType LastPmtDate
1            10                      2017/05/02
1            12                      2017/06/01

The formula should pick up the 2017/06/01 date in the column to the right of the LastPmtDate so I can quickly pick it up.

Upvotes: 0

Views: 24

Answers (1)

tigeravatar
tigeravatar

Reputation: 26650

Assuming a reference cell of H1 (which contains the account number you're looking for), use this formula and format it as date:

=MAX(INDEX(($A$2:$A$10000=H1)*$F$2:$F$10000,))

Upvotes: 1

Related Questions