Lookup multiple values and combine in a cell

[Ateeb Ali]

Dear Excel Ninjas
I need one help, attaching file, I am using excel 2010 so textjoint function for me.
Need a formula for a big data (around 15000 rows), look up multiple values against a match and show result in a cell with ","
Also if duplicate entries then show only once.


Regards

 

[bosco_yip]

Try this helper column formula solution of which suit with your Excel 2010,

1] In "Rvd" sheet helper column C2, formula copied down :

=IF(A3="","",VLOOKUP(A3,Rcvd!B:C,2,0))

2] In "Data" sheet C3, formula copied down :

=IF(A3="","",VLOOKUP(A3,Rcvd!B1:C16000,2,0))

Remark : Hide helper column or, set helper column cell font to white color as per your own need.

 

[Ateeb Ali]

Sir, thank you so much
Still there is issue, as I mentioned I have big data and I have done some examples in attachment, the date is not sorted out so It is giving wrong results

 

[bosco_yip]

Sir, thank you so much
Still there is issue, as I mentioned I have big data and I have done some examples in attachment, the date is not sorted out so It is giving wrong results

My revised formula result can obtain the result as same as the Pivot Table result (Col. F to Col G),

but unable to get your expected result (Col D) as show in D3 and D4

Sorry, I don't had the other way to solve your problem, and expected somebody can join us to give you help

 

[AlanSidman]

You can accomplish using Power Query. Join your two tables and add a text.combine function. See the attached.