How can I return the most recent account balance from a chart of multiple accounts

Lyryx

New Member
Joined
Aug 18, 2015
Messages
42
Hi everyone,

I thought this issue would be simple but it's actually proving to be quite difficult.


I need to write a formula that will return the last balance for an account within a table of multiple accounts.
Essentially the formula needs to look at the MAX date for all transactions impacting one account within my chart of accounts. Then look at it's ending running total balance and return it.



I've tried using the following formula:

Code:
=INDEX(Master[Validation],MATCH(MAXIFS(Master[Transaction Date],Master[Account '#],A31),Master[Transaction Date],0))
Where;

Master = The name of my master table
Validation = the running total column - where the figure I'd like to return resides
Transaction Date = Transaction Date - where the maximum date needs to be pulled from
Account '# = Account Number - the criteria used to distinguish which validation amount should be taken.
A31 (Column A) = the list of accounts that make up the criteria to be searched for within my master.​

The issue with this current formula is that it will look up the max date for an account but then return the first balance in the validation column that matches the date.

So even though the max date being picked up from the Nested MAXIFs function is correct. Any validation balance that matches that date can be returned, when I need it to only return a balance that matches the account number in question.



I've been wracking my brain on this one trying to use various forms of Array, Index and Lookup functions. So I'd appreciate any assistance one could provide.

Thank you.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,907
Office Version
365
Platform
Windows
One way

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
1
Transaction DateAccountValidation
2
20/08/2019​
AC1
773.04​
AC1
681.01​
{=INDEX(Master[Validation],MATCH(F2&MAXIFS(Master[Transaction Date],Master[Account],F2),Master[Account]&Master[Transaction Date],0))}
3
22/08/2019​
AC1
959.70​
AC2
240.09​
4
23/08/2019​
AC1
985.63​
AC3
626.02​
5
29/08/2019​
AC1
316.05​
6
31/08/2019​
AC1
263.46​
7
07/09/2019​
AC1
681.01​
8
23/08/2019​
AC2
207.78​
9
24/08/2019​
AC2
971.50​
10
26/08/2019​
AC2
430.09​
11
27/08/2019​
AC2
459.85​
12
01/09/2019​
AC2
107.55​
13
03/09/2019​
AC2
240.09​
14
24/08/2019​
AC3
210.40​
15
26/08/2019​
AC3
429.55​
16
27/08/2019​
AC3
285.10​
17
31/08/2019​
AC3
711.98​
18
02/09/2019​
AC3
589.78​
19
04/09/2019​
AC3
779.40​
20
06/09/2019​
AC3
626.02​
Sheet: Sheet1

ARRAY FORMULA in G2 - must be committed with {CTRL}{SHIFT}{ENTER}
=INDEX(Master[Validation],MATCH(F2&MAXIFS(Master[Transaction Date],Master[Account],F2),Master[Account]&Master[Transaction Date],0))

Entered as array is automatically enclosed in { }
{=INDEX(Master[Validation],MATCH(F2&MAXIFS(Master[Transaction Date],Master[Account],F2),Master[Account]&Master[Transaction Date],0))}
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
Or,

In G2, regular formula copied down :

=LOOKUP(9^9,Master[Validation]/(Master[Account]=F2)/(Master[Transaction Date]<>""))

Reagrds
 

Forum statistics

Threads
1,085,416
Messages
5,383,525
Members
401,836
Latest member
Bweston07

Some videos you may like

This Week's Hot Topics

Top