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

#### Lyryx

##### New Member
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
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
Or,

In G2, regular formula copied down :

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

Reagrds