Home > Enterprise >  Gsheets VLOOKUP returning multiple values
Gsheets VLOOKUP returning multiple values

Time:08-24

I have this sheet:

refid customerid invoiceno
5756588282 5364889 63849050
5756588282 5364889 62227291
5756588282 5364889 60565767
5756588303 5765682 62724304
5756588303 4137278 62721119
5756588303 4480623 62725914
5756587180 1737070 66385042
5756587180 1737070 66219284
5756587579 3008601 64004057
5756587579 3008601 62358070

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

enter image description here

CodePudding user response:

Something alone these lines perhaps:

enter image description here

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

  • Related