I have this sheet:

I need to do a vlookup with the refid and move in the other sheet the customerid and invoiceno but I only get the first row. I need to go back here, highlight duplicates and for each refid copy/paste the customerid and invoiceno in the other sheet. This can be automated.

What is a formula that gives me all the customerid and invoiceno where the refid matches? I've tried with a textjoin and a query but I can't make it work.

What I need to achieve in the other sheet:

refid - customerid - invoiceno

CodePudding user response：

Something alone these lines perhaps:

Formula in G2:

={TEXTJOIN(CHAR(10),1,FILTER(B2:B;A2:A=E2)),TEXTJOIN(CHAR(10),1,FILTER(C2:C,A2:A=E2))}

This will now spill both