Selective extraction of data from a 'master' table to sub-tables for 'T Accounts'

[absolutelyprobable]

Hi,

I have a transactions table from which I need to extract the relevant data for 'T Account' tables (the reason for having the big list as the driver is my use of multiple-currencies).

I found a fantastic formula that has gotten me part of the way (from cell A7):

=IFERROR(INDEX($P$1:$P$4967,SMALL(IF($X$1:$Y$4967=A$3,ROW($X$1:$Y$4967)),ROW()-ROW($A$7)+1)),"")

(and the 'magic' from this which I don't understand at all is that I need to use Shift-ctrl-enter for it)

Using this works for the first three columns whereby it finds txs for the T Account in question and lists the data that is valid both for dr and cr txs.

Then I come unstuck due to not being able to decipher from this copied formula how to add additional conditions and only getting results from the same row.

I've come up with three possible approaches to solve this. I think my preferred would be to add a hidden column that simply gives me row numbers of matched txs and lets me use simpler understandable (for me) formulae to populate the rest of the row.

The next, would be to get to understand the formula I'm using well-enough, with help, to be able to:

1. Have the Account column D, where the the T Account name (A3) is in the Debit column (X) to show the corresponding Credit account name (Y) - and the same for Account col. K where the T Account name is in H3.
2. Have the DR column E populate from the correct row from Col T where the T Account name is in the Debit col. (X) and zero if it is in the Credit col. (Y) - and vice versa for the CR col. F (and the corresponding for cols L & M).

The third approach, which I'm also intrigued by is how, if we ignore the part-solution I've got, given what I want to accomplish, some experts here might approach the problem.

This is the mini-sheet I've put together with some data to play with

TrustedKeyFinancesDashboard2022-10-08.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	BTC T Accounts
2																Data source
3	dr acc. 1			combined for bal sheets:	0.000	0.000		cr acc. 1			combined for bal sheets:	0.000	0.000							(converted)
4				c/f:	0.000	0.000					c/o:	0.000	0.000
5				dr & cr totals:	0.000	0.000					dr & cr totals:	0.000	0.000			TX ref	Date	Currency	Amount	BTC	USD	GBP	Description	Debit	Credit
6	TX ref	Date	Description	Account	DR	CR		TX ref	Date	Description	Account	DR	CR			1	2022-08-23	BTC	0.50	0.5	10740	9129	a valid one	dr acc. 1	cr acc. 1
7	1	2022-08-23	a valid one	?	?	?		1	2022-08-23	a valid one	?	?	?			2	2022-08-27	USD	5,000.00	0.232775	5000	4250	an ignored one	dr acc. 2	cr acc. 2
8	3	2022-08-31	another valid one	?	?	?		3	2022-08-31	another valid one	?	?	?			3	2022-08-31	BTC	0.20	0.2	4296	3651.6	another valid one	dr acc. 1	cr acc. 1
9				?	?	?					?	?	?			4	2022-09-04	GBP	2,000.00	0.109541	2352.94	2000	another ignored one	dr acc. 3	cr acc. 3
Sheet2

Cell Formulas
Range	Formula
E3,L3	E3	=IF(E4+E5>F4+F5,E4+E5-(F4+F5),0)
F3,M3	F3	=IF(F4+F5>E4+E5,F4+F5-(E4+E5),0)
L5:M5,E5:F5	E5	=SUM(E7:E167)
H7:H9,A7:A9	A7	=IFERROR(INDEX($P$1:$P$4967,SMALL(IF($X$1:$Y$4967=A$3,ROW($X$1:$Y$4967)),ROW()-ROW($A$7)+1)),"")
I7:I9,B7:B9	B7	=IFERROR(INDEX($Q$1:$Q$4967,SMALL(IF($X$1:$Y$4967=A$3,ROW($X$1:$Y$4967)),ROW()-ROW($B$7)+1)),"")
J7:J9,C7:C9	C7	=IFERROR(INDEX($W$1:$W$4967,SMALL(IF($X$1:$Y$4967=A$3,ROW($X$1:$Y$4967)),ROW()-ROW($C$7)+1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Incidentally, thanks to the team at Mr. Excel for your incredibly long service Also a massive thanks to the community who (even if there are very few of the same faces as from back then) have jointly kept this as somewhere valuable to find solutions for so many years such that you're still here when I need you I saw when I eventually managed to log in with ancient credentials that it was in 2006/7 I was last active here