# Thread: How can I return the most recent account balance from a chart of multiple accounts Thanks: 0 Likes: 0

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

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.

2. ## Re: How can I return the most recent account balance from a chart of multiple accounts

One way

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
1
Transaction Date Account Validation
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))}

3. ## Re: How can I return the most recent account balance from a chart of multiple accounts

Or,

In G2, regular formula copied down :

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

Reagrds

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•