Extract Balance

[Steve 1962]

Hi

Just need a formula in column I, that extract the last balance (based on end-of-month) from the table in A to F. Criteria should include Bank name (col A), Month (col C), Balance (col F).

Thanks

Book1
	A	B	C	D	E	F	G	H	I
1	Bank	Year	Month	Date	What	Balance		Month	Balance
2	Big	2022	1/01/2022	5/01/2022	x	$37,445.12		1/01/2022	$39,726.53
3	Big	2022	1/01/2022	5/01/2022	x	$37,445.12		1/02/2022	$36,806.07
4	Big	2022	1/01/2022	5/01/2022	x	$39,979.03		1/03/2022	$40,000.00
5	Big	2022	1/01/2022	7/01/2022	x	$39,879.03
6	Small	2022	1/01/2022	7/01/2022	x	$39,876.53
7	Small	2022	1/01/2022	7/01/2022	x	$39,726.53
8	Big	2022	1/02/2022	2/02/2022	x	$39,761.53
9	Big	2022	1/02/2022	14/02/2022	x	$36,806.07
10	Big	2022	1/03/2024	5/03/2022	x	$40,000.00
Sheet1

 

[Cubist]

Criteria should include Bank name (col A),

Your result in I doesn't consider col A. Can you confirm?
Secondly, is it to assume the data is sorted?

 

[Steve 1962]

My bad. Yes A7 should be Big and based on Bank. Data may not be necessarily sorted.

 

[Cubist]

If A7 is Big then row 5 and 7 are duplicates based on the criteria, which one should it return?
Also, why is I4 = $40,000? the month in H4 is 1/3/22 where as C10 is 1/3/24.

 

[Steve 1962]

Thanks Cubist - I have now reviewed those data again. Just had an emergency at my place and I unfortunately, was distracted causing me to not double-check the values (rushed). All good now - I have now reposted an updated table.

To make things easier, the data in the table can be (and will be) sorted by DATE (column D).

The criteria that will be used to extract the desired balance will be -

BANK - Big or Small or other category
MONTH - Based on end-of-month DATE in column D (the required month to analyse will be in column H). The last entry in the database will be required (EG: for January, row 7).

With respect to the MONTH - the final date in the database, may not necessarily be the end-of-month date, For example, in the case of the database, the last entry for JANUARY 2022, is 7th (and not 31st).

Book1
	A	B	C	D	E	F	G	H	I
1	Bank	Year	Month	Date	What	Balance		Month	Balance
2	Big	2022	1/01/2022	5/01/2022	x	$37,445.12		1/01/2022	$39,726.53
3	Big	2022	1/01/2022	5/01/2022	x	$37,445.12		1/02/2022	$36,806.07
4	Big	2022	1/01/2022	5/01/2022	x	$39,979.03		1/03/2022	$40,000.00
5	Big	2022	1/01/2022	7/01/2022	x	$39,879.03
6	Small	2022	1/01/2022	7/01/2022	x	$39,876.53
7	Big	2022	1/01/2022	7/01/2022	x	$39,726.53
8	Big	2022	1/02/2022	2/02/2022	x	$39,761.53
9	Big	2022	1/02/2022	14/02/2022	x	$36,806.07
10	Big	2022	1/03/2022	5/03/2022	x	$40,000.00
Sheet1

 

[Cubist]

Note I've added a Bank column H for the criteria you want to filter on col A. Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Bank	Year	Month	Date	What	Balance		Bank	Month	Balance
2	Big	2022	1/1/22	5/1/22	x	$37,445.12		Big	1/1/22	$ 39,726.53
3	Big	2022	1/1/22	5/1/22	x	$37,445.12		Big	1/2/22	$ 36,806.07
4	Big	2022	1/1/22	5/1/22	x	$39,979.03		Big	1/3/22	$ 40,000.00
5	Big	2022	1/1/22	7/1/22	x	$39,879.03
6	Small	2022	1/1/22	7/1/22	x	$39,876.53
7	Big	2022	1/1/22	7/1/22	x	$39,726.53
8	Big	2022	1/2/22	2/2/22	x	$39,761.53
9	Big	2022	1/2/22	14/2/22	x	$36,806.07
10	Big	2022	1/3/22	5/3/22	x	$40,000.00
Sheet3

Cell Formulas
Range	Formula
J2:J4	J2	=XLOOKUP(1,($A$2:$A$10=H2)*($C$2:$C$10=I2),$F$2:$F$10,,,-1)

 

[Steve 1962]

Hey thanks Cubist for your time and patience. Works perfectly, first time.

Have a good week.