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

Lyryx

New Member
Joined
Aug 18, 2015
Messages
45
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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))}
 
Upvote 0
Or,

In G2, regular formula copied down :

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

Reagrds
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top