return multiple values for one lookup value in the same cell

Basically it is not that easy task with built in excel functions. How about user defined function (workbook has to be macro enabled - for instance xlsm format.
And additional question - how many main accounts and how many subaccounts you need to treat this way, and ... how many subaccounts per main account you expect (max)

See if such function would do:

Please Login or Register  to view this content.

usage in a workshhet as a regular function - see cell B2:

Formula:

Please Login or Register  to view this content.

copy it down

You have to copy the VBA code and paste it into a general module before you can use it as a cell formula.

Then you have to save the file as macro enabled file in the *.xlsm file format.

Or other way round - copy your data into my file.

Of course, usually the better way is to copy the code into your file.
So open your file
press Alt+F11
in window with VBE press Alt+I
then M
module1 window shall appear - paste there the function code
press Alt+F11
save the file as macro-enabled
close and open again allowing macro execution.
Now you can use this function in your file