return corresponding value within a row of a dynamic table base on a multiple criterias

[lymanting]

Hi, base on the value i enter in B2 of the "desired result" tab, i wish to draw all the corresponding entries from the "data" tab as in the manner shown in "desire result" tab (with the debit on one side and the credit on the other). the table in the "data" tab need to be a dynamic named range

Please help. thanks

[FlameRetired]

Hello lymanting. Welcome to the forum.

In the attached find these 3 dynamic named range formulas in Name Manager.

Credit_Helper	=Data!$I$2:INDEX(Data!$I:$I,MATCH("zzzzz",Data!$B:$B,1))
Debit_Helper	=Data!$H$2:INDEX(Data!$H:$H,MATCH("zzzzz",Data!$B:$B,1))
Source_Data	=Data!$B$2:$G$2:INDEX(Data!$B:$B,MATCH("zzzz",Data!$B:$B,1))

Source_Data Does what it's name suggests.

These next two refer to helper columns in H:I of 'Data' sheet. Their intent is to avoid resource hungry array formula alternatives.
Credit_Helper
Debit_Helper

In column H find this formula that returns row numbers for occasions of Debit that correspond to the 'Search' criteria in B1 of 'Balance'.

Please Login or Register  to view this content.

In column I this serves the same purpose for Credit.

Please Login or Register  to view this content.

In 'Balance' sheet A4 filled down and across column C

Please Login or Register  to view this content.

You may have to reapply formatting.

In 'Balance' sheet E4 filled down and across column G

Please Login or Register  to view this content.

Again reformatting may be necessary.

[lymanting]

thank you for the help

[FlameRetired]

You are welcome. Thank you for the feedback and marking thread Solved.