Trial Balance, INDEX/MATCH, Multiple Criteria, and SPILL

[excelqueenintraining]

Hi everyone, I'm trying to automate some reconciliations at my accounting job and running into some trouble. I saw some great threads here, but could not find my use case, so I wanted to ask. For some reason, I feel like it is a stupid error, as I usually can navigate INDEX/MATCH relatively well. I've added screenshots, randomized data and listed the goal. I would be really thankful for y'alls help!

Goal: Pull in debit/credit balances for each ledger account to cross-reference with the balances recorded.

Issue: I am pulling the ledger account name instead of the amount, and also this cannot be dragged. I tried playing around with the columns and formula, but am not getting the proper return values. I want to ensure that both the month and type (credit/debit) are being properly pulled. It has provided the SPILL error, returned NA or just the text values being returned.

Using Excel 2021 - Windows OS.

Happy to answer questions!! Thank you so much <3

 

[awoohaw]

Welcome to the forum.
My suggestion would be to use power query to un-pivot your trial balances so you only have 4 columns. 1. Acct 2. Date 3. Credit/Debit 4. Amount. Then you can build your other reports.

 

[Cubist]

Try:

Book1
	A	B	C	D	E
1		Jan-23		Feb-23
2		Debit	Credit	Debit	Credit
3	1001 - Cash	$ 100,000		$ 110,000
4	1002 - Account Receivable	$ 10,000		$ 11,000
5	1006 - Acrrued Expenses		$ 3,000		$ 25,000
6
7
8		1006 - Acrrued Expenses
9		Jan-23		Feb-23
10		Debit	Credit	Debit	Credit
11		-	3,000	-	25,000
12
13		1001 - Cash
14		Jan-23		Feb-23
15		Debit	Credit	Debit	Credit
16		100,000	-	110,000	-
Sheet8

Cell Formulas
Range	Formula
B11:E11	B11	=INDEX(FILTER($B$3:$E$5,$A$3:$A$5=$B$8),SEQUENCE(,2,2*MONTH(B9)-1))
B16:E16	B16	=INDEX(FILTER($B$3:$E$5,$A$3:$A$5=$B$13),SEQUENCE(,2,2*MONTH(B14)-1))
Dynamic array formulas.