Excel Magic Trick 1282: Lookup And Return Multiple Items to a Single Cell: TEXTJOIN Function

See how to use the amazing new Excel 2016 function TEXTJOIN to Find Multiple Lookup Values In First Colum AND return Multiple Items From Next Column To a Single Cell. This trick is thanks to DRS at YouTube.
Office Insider Version: 16.0.6568.1011 or 16.0.6568.2025
Return Multiple Items to Single Cell with a delimiter
DRS at YouTube
Lookup Multiple Items
Return Multiple Lookup Items to Single Cell

DRS at YouTube

Excel Magic Trick 1281: TEXTJOIN & CONCAT New Excel 2016 functions (14 Examples of Joining Items)

Excel Magic Trick 1280: How To Get Latest Excel Updates for Insider Program: Office 365

[excelfan]

I wanted to cry when i watched this it is a thing of beauty

[kjvstats]

That's a very useful solution...I've been looking for that answer for 3 years! Nice job Mike. Looks like I'm gonna have to get Excel 2016 soon :-)

[davidepstein]

This video was a dream come true...  I have a huge data set to analyze and this makes my task much easier.  Mike Girvin - you are the best - thanks for sharing your wisdom!!!

[contextures]

Wow, great use of the new TEXTJOIN function! Thanks for sharing it.

[nadermounir]

This is an amazing video awesome trick with the repeat function Absolutely spectacular 👌 fantastic job Mike

[Owl_Pellets]

This is brilliant. Extremely useful. Thank you DRS and Mike.

[krn]

Awesome formula from DRS. Thanks for sharing Mike.

[vandybhosale]

=IF(A7=A6, C6&";"&C7, C7) this formula in cell C6. Copy and paste column as values
=IF(A8<>A7, "Combined values", "") this formula in D6. Copy and paste column as values
Then sort column D in descending order. All cells in C column with text aa Combined values in D columns are the desired values.
Hope this helps

[kenank]

thats better then vlookup. realy great. thanks dude.

[jerryquiros]

Amazing!! Thank you very much for posting it

[hennessy]

Thank you so useful !!!! still works in 2019

[mikegreenberg]

Great video! I was wondering if you had any videos for doing the reverse. I want to have a cell hold multiple values. Then use a drop down header to search for those values individually. I've found a few work arounds but no step by step for implementation. Thanks!

[alberthinkle]

Is it possible to have a single cell output? Like every rows overwrites that same single cell?

[steverosenthal]

Is there a way to use this general approach with numeric values in the Item column. For example, if Item column contained numbers and I wanted to total all the matching invoice amounts? Thanks again for posting this...this may be the solution I've been looking for!

[pinnaclefinancial]

Can I know what could be the reason when using this function with XIRR, when I try to calculate the internal rate of return for stocks that I am buying on different dates its giving me #value error?
=XIRR( TEXTJOIN("; ", TRUE, IF(D5:D37=C41, -(E5:E37), "")), TEXTJOIN("; ", TRUE, IF(D5:D37=C41, C5:C37, "")))

D5:D37 is the range for Ticker Symbols while E5:E37 is the prices at which I bought & sold the stocks, C5:C37 are the dates column.

Any help would be really appreciated

[nickmckinney]

Does the lookup array have to be arranged in any particular manner? This appears to be setup like a VLOOKUP would be and I am curious if that is a coincidence or it's not needed? When I do this on my data, it works for many lookups but occasionally it reports blanks when I know there is a result.

[koshish]

say given year in Jan, 5 dates and in Feb 6 dates data feed in column if we find on other columns the Col shows values 5 under Jan 1 to 31 and Col shows in range in Feb 6 data

[nicolepatton]

If you have a moment, I have a quick question. If my TEXTJOIN is in the date format and it returns with dates in number format, how would I put the Text( "mm/dd/yyyy") in this formula? Thank You!

[VinodKumar-ktwh]

How can we use that function for Rows instead colums?

[BuddyPickleball]

Do you have a formula for Excel 2010 that does this?