Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell

[Andrew_da_rookie]

Thanks all in advance.



So I am trying to use Excel to automate Accounting Financial Reports, in particular, a Balance Sheet which shows the balance of all accounts as at a particular date, essentially a snapshot of accounts.

I will be pulling this data from Bank statement transaction data (not exactly double accounting method for those in the field).



The data is of very typical bank transaction format, but I have a few bank accounts so I would consolidate them into one table under the same fields and add a column Source to separate the different banks. So it looks like the below sample:



DATA:

Source Date Item description Balance Amount

BOA 2 July 2024 Salary 225,000.00 100,000.00

BOA 26 June 2024 Rent 125,000.00 -25,000.00

BOA 10 July 2023 Salary 150,000.00 100,000.00

CBA 30 June 2024 Interest 101,000.00 1,000.00

CBA 1 July 2023 Deposit 100,000.00 100,000.00





REPORT:



Balance Sheet as at:

30 June 2024



Assets

BOA 125,000.00

CBA 101,000.00



Total Assets 226,000.00







Q: How can I obtain the Balance sheet values as per above?



I suppose I need some sort of lookup formula with multiple critieria, I also noted that lookup formulas and their Match Types may require strictly sorting the data in a particular order for them to work.

I have tried vlookup, xlookup, index match but I get stuck on Criteria 2 below:



Value to return: Balance

Criteria 1: Asset name matches Source data

Criteria 2: Date must be same or the first one before the Balance Sheet date, i.e. EQUAL to or LESS than the Balance Sheet date.



Thanks everyone!

 

[SunnyAlv]

maybe you can provide an example with a picture / screenshot of your data example, to make it easier for someone to help you with your problem.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have, where it is and also what you want & where it is to be. It is then also easy for us to copy to test with.
If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.

See if this does what you want.

24 08 20.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Source	Date	Item Description	Balance	Amount			Balance Sheet Date
2	BOA	2 July 2024	Salary	225,000.00	100,000.00			30 June 2024	BOA	125,000.00
3	BOA	26 June 2024	Rent	125,000.00	-25,000.00				CBA	101,000.00
4	BOA	10 July 2023	Salary	150,000.00	100,000.00
5	CBA	30 June 2024	Interest	101,000.00	1,000.00
6	CBA	1 July 2023	Deposit	100,000.00	100,000.00
7
Balance sheet

Cell Formulas
Range	Formula
J2:J3	J2	=BYROW(I2:I3,LAMBDA(r,INDEX(SORT(FILTER(B2:D6,(A2:A6=r)*(B2:B6<=H2)),1,-1),1,3)))
Dynamic array formulas.

 

[Andrew_da_rookie]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have, where it is and also what you want & where it is to be. It is then also easy for us to copy to test with.
If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.

See if this does what you want.

24 08 20.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Source	Date	Item Description	Balance	Amount			Balance Sheet Date
2	BOA	2 July 2024	Salary	225,000.00	100,000.00			30 June 2024	BOA	125,000.00
3	BOA	26 June 2024	Rent	125,000.00	-25,000.00				CBA	101,000.00
4	BOA	10 July 2023	Salary	150,000.00	100,000.00
5	CBA	30 June 2024	Interest	101,000.00	1,000.00
6	CBA	1 July 2023	Deposit	100,000.00	100,000.00
7
Balance sheet

Cell Formulas
Range	Formula
J2:J3	J2	=BYROW(I2:I3,LAMBDA(r,INDEX(SORT(FILTER(B2:D6,(A2:A6=r)*(B2:B6<=H2)),1,-1),1,3)))
Dynamic array formulas.

Thanks Peter.

I have a few questions.

What is r? Is it I2:I3?

Also any ideas why I am getting a #NAME? error using your formula, probably because of BYROW and LAMBDA not being recognised?

I am using Office 365.

 

[Peter_SSs]

Also any ideas why I am getting a #NAME? error using your formula, probably because of BYROW and LAMBDA not being recognised?

I am using Office 365.

That would indicate your version does not have one or both those functions. However, both should be available in 365 so I suggest that you look in File -> Account and check to see if you have updates to do.

What is r? Is it I2:I3?

Sort of. It represents a row in I2:I3 as instructed by BYROW so first it will represent I2 and once that has been processed it will then represent I3.

 

[Andrew_da_rookie]

A decent solution I have found for those without the custom LAMBDA formulas or array formulas, although it still required the data to be sorted by date:

=XLOOKUP(1,(($A$2:$A$8=I2)*($B$2:$B$8<=$G$2)),$D$2:$D$8)

Thanks for the help

 

[Peter_SSs]

... for those without the custom LAMBDA formulas or array formulas

XLOOKUP is an array formula.
Also not sure what you mean by "custom LAMBDA formulas" as the functions I used are just functions that are available in some Excel versions and not others - just like XLOOKUP.

Never-the-less your formula should work fine provided, as you mention, the data is sorted appropriately. If it is sorted appropriately then you could also use this
=VLOOKUP(I2,FILTER(A$2:D$6,B$2:B$6<=G$2),4,0)

BTW, did you manage to update your 365 to get all the latest functions that are available in that version?

 

[Andrew_da_rookie]

I did not get it working unfortunately, my Office does not show any updates available yet does not show the latest functions.

By array formula, I mean formulas that require Ctrl + Alt + Enter to work.
So XLOOKUP is not an array formula in that definition, just a formula that looks up arrays and works with a simple Enter like many other look up formulas such as VLOOKUP, INDEX MATCH etc.

 

[Peter_SSs]

By array formula, I mean formulas that require Ctrl + Alt + Enter to work.

I think that you mean Ctrl+Shift+Enter, but with MS365 - which your profile says you have - no formulas require Ctrl+Shift+Enter confirmation.

my Office does not show any updates available yet does not show the latest functions.

That does seem odd as the functions mentioned have been released to all users as far as I am aware. In Excel if you go File -> Account do you see 365 as the version?