Index and Match multiple matches

Question

Please answer the following question for me. There are two Excel sheets, and I need to determine which accounts are matched in Sheet 1's Column A. Sheet 2 is the reference.

I'm trying to find a formula that will tell me every account on sheet 1 that corresponds to the position number. Sheet 2 contains the solution. Could someone please assist?

eg. 5001 = should give me 41150100, 41150101, 41200000

Position	Account
5001	41150100, 41150101,41200000
5031	43010101, 43256856
5051	78589545, 89659845

Account	Position
41150100	5001
41150101	5001
78589545	5051

I am looking for a formula, which can give me all the accounts in sheet 1 in corresponds to position nr. The answer is in sheet 2. Can someone please help?

Answer 1

You can try the following (formula 1) assuming there are no Excel version restrictions according to the tags provided in the question:

=LET(pos, A2:A4, accnt, B2:B4, REDUCE({"Account","Position"}, pos, LAMBDA(ac,p,
 VSTACK(ac,LET(f,TEXTSPLIT(@FILTER(accnt,pos=p),,","), HSTACK(f, IF(f=f, p)))))))

Here is the output: