How to Solve Complex Lookup Problems in Excel with TEXTJOIN

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/textjoin-lookup-file

[@josephcoon5809]

0:20 “I don’t need another way of...” I ALWAYS need another way to do something because every situation is different. You’ll never have to twist MY arm to teach me something new. Keep them coming!!!

[@excelisfun]

Thanks to you and Bill Szysz!!!! TEXTJOIN is simply amazing!!!

[@LeilaGharani]

You're welcome Mike. It really is amazing! Thanks to Bill for the solution :)

[@BillSzysz1]

Thanks Mike and Leila :-)

[@casper3842]

You guys are brilliant!👍👍👍

[@ovidiuteodorescu8888]

Just perfect. Is my 4th attempt to find out about TEXTJOIN, and your video just saved my project. Thank you!

[@IzzyRazzaque]

I needed this more than you know and more than I imagined. Thank you!

[@LeilaGharani]

Our pleasure. 😊

[@sudhirhiwale9784]

Thank you very much Leila. You have got a lovely voice. It is like you keep on speaking and I keep on listening.

[@LeilaGharani]

I'm glad you like the videos!

[@vida1719]

Thanks for Bill Szysz for sharing his knowledge and thanks for you Leila for showing this visually. Really amazing teamwork result!

[@LeilaGharani]

Thanks Vida - Teamwork is the best :)

[@germanlondono8700]

This Lookup Problems series is genius. And I really like the fact that you're using the same blouse to keep the continuity of the series, is a very nice touch (I think that's the case because today, almost 3 years after this video's release, it doesn't seem like this was recorded the same day.

[@LeilaGharani]

Honestly I don't remember 😄 But I'm glad you like the videos!

[@barttitulaerexcelbart9400]

I never found CONCATENATE relevant compared to "&", and because of that TEXTJOIN did not get any attention. But your video changed my mind! Thank you for the explaining and raising my interest for this new function.

[@LeilaGharani]

You're very welcome Bart. I thought exactly like you - until I saw Bill's response. Thanks to him we've found other great uses for this function :)

[@josephcoon5809]

3:30 So...I learned a lot of Excel for personal reasons which means I have 365. However, my office only has 2013. I have been using CONCATENATE(text1,$row$col,text2,$row$col,text3) to create delimited joins. I was not even aware of TEXTJOIN() till just now. 😂 With my CONCATENATE(), I can easily change what my delimiter is based on what I have in my fixed reference cell ($row$col). I was using this to create automated email subject lines for the various departments that wanted standardized email headers. Because department requirements constantly changed, I wanted a cell that I could program based on whatever the current requirements were for each particular department. Then, all I have to do is copy the value of my cell and paste as plain text into my email subject line. I did the same for particular portions of the email body that required particular information formats. For those, I created a CONCATENATE() cell that retrieved information from a string of quantity and item selection drop down cells to quickly create a request statement with all the basic verbiage and dynamic information with a few clicks of my mouse instead of typing out hundreds of characters. I just thought I would mention all that for any viewers who may be in the same boat in their company and didn’t have access to the current versions of EXCEL.

[@MySpreadsheetLab]

Textjoin function really is amazing! It's a game changer! Thanks Leila for the video!

[@LeilaGharani]

You're very welcome. Yes - fully agree . Thanks to Bill's comment :)

[@Chandey57]

Thanks. I was trying to find an elegant solution to a one to many mapping. The identifying and treatment of duplicate values was most helpfully explained. Thank you very much

[@mubinamuhammadsubhanbaig]

you are great, you solved the problem

[@edrage8679]

Productivity and games sounds like a nice combination.

[@coco99ca]

Thank you both. How can I change the formulas to give the answers from a column? Your answers are from rows b4:d4. I need the range to be columns. If b5:b45 matches h5, take the answers from a5:a45.

[@rdchenweb]

Here is the code using dynamic array to identify dates for "Blend" app. =LET( dates,$A$5:$A$45, productivity,$B$5:$B$45, games,$C$5:$C$45, utility,$D$5:$D$45, TEXTJOIN("; ",TRUE,IF(OR(productivity=$H20), TEXT(FILTER(dates,productivity=$H20),"m/d/yyyy"),""), IF(OR(games=$H20), TEXT(FILTER(dates,games=$H20),"m/d/yyyy"),""),IF(OR(utility=$H20), TEXT(FILTER(dates,utility=$H20),"m/d/yyyy"),""))) The output is "1/26/2017; 2/3/2017; 2/19/2017"; Notice the output from the FILTER function is an integer and need to be formatted.

[@angiedixon134]

Thank you for a great explanation to a tough problem. I got it to work in my spreadsheet but have a slight problem. I have 2 similar names like "Sweetbriar Court" and "Sweetbriar Courtyard". Since the first is a subset of the second, it is showing a match to the first when it is in some cases only matching the second. Any help would be much appreciated.

[@nirmalankur]

Need help on combining columns and then vlookup to get my answer without duplicates, also the column position changes when we get new entries in raw data....what do you suggest? I want it to be dynamic so that even if column position changes the formula should not throw an error. (I cannot use column header as vlookup value, as I have to combine multiple columns to get the actual vlookup value)

[@coltgauman874]

Hey Leila! I've been killing myself trying to figure out how to use all of this Excel wizardy... So basically it’s a workout template. I’m at the point now where I have multiple set and rep categories (power, strength, secondary, and accessory); each of which has their own set and rep schemes (3x2, 3x3, 3x4, etc) as well as many charts/tables detailing warm-up sets. So if I have a set of “3x2”, the chart for that includes 1x10, 1x5, 1x3, and then 3x2. So these are multiple rows and columns that I need to be associated with this “3x2” single cell. All my set and rep schemes are made. I’ve used data validation to get me to the point where on my template I can choose “power” then I can choose “3x2”. But when I click that 3x2, I want that set and rep scheme table (from the set and rep database) to transfer over into the actual template. Any advice to push me in the right direction would be much appreciated!

[@LeilaGharani]

Hi Colt - wow - even my workout schedule is still just on a piece of paper. If you want to click the 3x2 and have it populate the actual template, you could use form controls - for example check boxes. Depending on how your database is setup, you can use a lookup function (index match generally works for most cases) to retrieve the data to the main template..... hope that helps...

[@DustinSchneider]

Have to go back through your whole channel to find all of the videos that have the tricks I need in them. I know you have a lot of information, I just can't remember what the videos were titled.

[@diegorobles6247]

Thanks!

[@Jerrel.A]

Szysh is a creative person, but Leila walked us through as only she can!

[@LeilaGharani]

Thanks Jerrel. That's very kind.

[@elima2002]

This is amazing, have used this formula twice today at work. Thank you very much :)

[@LeilaGharani]

That's great Emilia!

[@monicakim777]

Oh my god thank you so much for the video! have used if(concate) function to try to join the text. this is absolutely awesome!!!

[@BillSzysz1]

Thanks a lot, Leila :-) I am absolutely certain that such excelent explanations are far, far from my capabilities.

[@excelisfun]

I disagree. I know that they are within your capabilities : ) Close, close to your abilities!

[@barttitulaerexcelbart9400]

I. Aggree with Mike, Bill! A Polish student explicitly askes for your videos and she liked them very. Besides you also contribute to all the knowlledge of Excel in the world by helping other presenters....thumbs up

[@LeilaGharani]

Thanks to you Bill & I'm certain that's not true :)

[@excelisfun]

I agree with Bart also: In whatever way you contribute, we are lucky to have you on the Team, whether making videos, helping other presenters, answer text questions and all !!! Go Bill Szysz!!! Go Online Excel team : )

[@mehdihammadi6145]

Thanks to both of you Leila and Bill

[@LeilaGharani]

You're very welcome Mehdi. I'm thankful to Bill too, because since this video, I've been using Texjoin A LOT :)

[@haroldquek1655]

Would you be able to do a vba text join with if function on another workbook

[@jameshowlett4456]

Can you please take a look at cell C7 ("Fightrr"). This item was not found in the list (H5:H16). Is this correct? If it is, shouldn't it be in the list? Thank you in advance for your time.

[@F002boy]

This is awesome, Leila! Too bad I am not working on MS-365, but actually I prefer results in separate cells~ I will probably stick with the index + aggregate function. Thanks a lot for sharing!

[@LeilaGharani]

Glad you like it and found a good solution to your problem. Thank you for your comments and questions. It helps us all learn new things :)

[@deveshkoshti1830]

Thanks for your excel learning videos. Its very very useful for all excel learners / beginners. Just small request.....Pls write u r formulas in cell to view it clearly. in address bar it becomes small to view. Even after 480/720 quality, formula looks small. Thx a lot....

[@afsarAlichannel]

You have done an amazing effort. Hats off to you. Stay blessed

[@LeilaGharani]

Thank you! Glad you like the tutorial.

[@kiranjohnp]

Leila I have a question while using textjoin along with if Formula, I am having issues with it. here is my example: I have A to F blank cells and the text joining cells in J2to Y7, Now i am using the formula on G2 {=TEXTJOIN(",",TRUE,IF($A2:$F2=$J$3,$J$7:$Y$7,"-")) where J3=0 I am getting the text joined from J7 to Y7 though A to F is blank, I should get the result as blank as its blank Please let me know if this can be done or is it by design that Excel considers blank cells as 0

[@markosiridzanski2181]

Useful case where I implemented textjoin on the project was when I needed to get a string from multiple sheets where budgeted amounts are shown up

[@ajayv304]

It is the Complex problem I suppose. Thank you very much for sharing your knowledge.

[@cpono]

Thank you. I need your help. I want to add an "and" at the last cell when text joining. I have a sentence that says : I will include TVs to these rooms: Rm 1, Rm 2, Rm 3. What I want it is add an "and" between Rm 2 and Rm 3. So the sentence will read Rm 1, Rm 2, and Room 3.

[@yazantobaa3777]

I have a question please can you tell me why excel gives me #Name if i try to delete any row or column or just change any value of the cells of the array of textjoin(if) ?!

[@Sal_A]

Ah! A witty and optimal solution. I'm sure TEXTJOIN can be useful in many different circumstances.

[@LeilaGharani]

Agree - this solution from Bill has definitely opened doors for me :)

[@javierlainez8376]

100% agree...this solutions brings a whole new world of lookups.

[@jorgebriseno8967]

Thank you so much, thank thank thank you. Textjoinfuncion is my best friend.

[@jjewel6812]

That is a nice function. Can this also return names that appear more then once in a range? for example; A1 contains: apple, pear B1 contains: pear C1 contains: grape D1 contains: grape The return in E1 should be; pear, grape Is that possible with this function?

[@marceloribeirosimoes8959]

Thank you very much! Now I am looking for a way to "subtract" text that is in one cell from another that has the same text and more characters. I mean, if a cell has this: Marcelo Ribeiro Simoes ...and another cell has this: Ribeiro Simoes I want to subtract the unique portion of text from that to a third cell. I know we have LEFT, RIGHT and MID but they are very, very time consuming when our cells have not the same spacing (or whatever) format. So, I would have to fix the way those functions extract text one by one and my sheet has more than 3.300 lines... Any ideas, please?

[@COC_Kratos]

Please come up with an Example video of using Textjoin and Sumproduct together, where Sumproduct will be used inside Textjoin as a supplier of Multiple text inputs. Hope I am clear.

[@Jchriswalker71]

Thank you for sharing this has saved me so.much time...

[@winnumber101]

This is so beautiful... I'm glad I subscribed to this channel.

[@LeilaGharani]

Glad you like it. Thanks for the support :)

[@andrewmoss6449]

Great video, Leila. I would just say though that the function TEXTJOIN is a great new addition to Excel, but it only works for users with Office 365. That's why it's probably not a good idea to use it if you are distributing your workbook to people who are likely to be using older versions. I had this problem with my standalone version of Excel 2016 - I wasn't entitled to download updates for it, so TEXTJOIN would not work.

[@LeilaGharani]

Very true Andrew. Workbook sharing should be a factor to consider. Thank you for the comment.

[@trevorgrondin1512]

i faced a similar problem recently, and instead of the final FIND() function to catch multiple returns of the category, i ended up using UNIQUE() around the IF() function inside the TEXTJOIN(). is there a problem with this approach?

[@AjayKumar-nv6zu]

Great video Lila, more useful Thank you and love u

[@vivek1416]

Awsomeee....the idea of text join is superblyy amazing

[@LeilaGharani]

It can really come in handy sometimes.

[@ilyasjaffar4969]

hi Leila - thanks for the explanation. i tried using Textjoin with Unique. Results start with 'FALSE, value, value, value, etc'. wondering why the first value is FALSE. Can you help me. i can send the file to you if it is easier.

[@ernestocruz7953]

Hi Leila! Thanks for another great video! What screen recorder are you using? The way the Ctrl + X and Ctrl + Shift + Enter shows up is really nice. Or is that just additional editing?

[@LeilaGharani]

You're welcome Ernesto. I use Camtasia.

[@varunshamanna4703]

Thank You, very much for the explanation. You saved my day. Thank you once again. I have a query for you. What if I want to check 2 conditions i.e instead of checking the value in H5 and H6 together. and get the header.

[@rdchenweb]

Use this formula to check the values in H5 and H6 together: =LET( productivity,$B$5:$B$45, games,$C$5:$C$45, utility,$D$5:$D$45, TEXTJOIN("; ", TRUE,IF(OR((productivity=H5)+(productivity=H6)),$B$4,""), IF(OR((games=H5)+(games=H6)), $C$4,""), IF(OR((utility=H5)+(utility=H6)), $D$4,""))) The output is "Productivity; Games; Utility"

[@anujjain5989]

HI Leila, I'm facing some issue every day in my excel, please support. Example If some one sending me mail & they are pasting value in Mail body. While copy & paste all Value from Outlook to Excel Some Extra space Reflecting. I have to remove manually extra Space everyday. how can I fix the below issue with with excel formula.

[@kalkun009]

Its quite lengthy...

[@trueindian2108]

Great .. how do you know all this?

[@mathewallen2254]

Will this work if my data is in two long columns without a header?

[@abdulsalam935]

Madam, the above video is very good. But, how can we get the data if the column headings in in COLUMN ONE COLUMN i.e., Column A and DATA in Column B

[@maythamalmouthen5191]

Thanks A lot, great work and effort. The tutorial solved 75% of my problem. In my case the Productivity, Games & Utility row have duplication. Like Productivity, Games Productivity, Utility & Utility. I have reached to division column but could not go to the next column. Please advice if you have solution to the case when Productivity, Games & Utility row have duplication values.OR If there is any formula to remove duplication names in one excel cell which are separated by ",".Thanks once again whether you have time to see my comments and reply or not :)

[@markjosephlee5522]

Hello Maam, i'm a great fan of your tutorials, i have watch almost all of them :D . i have a question with regards to TEXTJOIN FUNCTION, It is doesn't works in my excel 2016. can you advise me on how to fix it and try this function?

[@LeilaGharani]

Hi Mark - thank you for your support! TEXTJOIN is available for 2016 for those that get updates on Office 365. If you've purchased the stand-alone version, it wouldn't be available.

[@markjosephlee5522]

ah, I see, anyway, thank you so much for your tutorials I've learned a lot. More power !!!!

[@osamasalah341]

Many thanks Liela for this videos

[@LeilaGharani]

You're very welcome Osama. Thanks for watching.

[@deveshkoshti1830]

Instead of textjoin function for excel 2013 or lower version can u pls works with other formula like concatnate and share formula in same video.. Thx...

[@vijaysharma-sk3nl]

I get results with this formula but when I tried to get the date as a result , date was found in the number format ,and I couldn't convert it in date format,pls make a problem solved video on this.

[@VinodKumar-zn8ty]

Text Join for Lookup is working without CSE. Am I Correct?. CSE also it is giving the same result. Please correct me Leila. Thanks

[@MrBassel1978]

You're the best 👌💓

[@joefromdc]

With in ur data visualization class u provided a spreadsheet where u wrked out different scenarios. I would like a full class like that. Dnt want to download a bunch of youtube videos Finally it was a great class.

[@LeilaGharani]

Thanks Joseph - do you mean a full class on complex lookup formulas?

[@joefromdc]

Leila Gharani Yes, youtube provides a great visual demonstration if u can query the right title. U can imagine how many videos you can go through just to find the right title to do the work you want to do. Example txtjoin is great if ur doing xyz. Ur class section 5 dashboard formula tool kit, provided practical examples if you're doing this use this and this and this.

[@LeilaGharani]

you're right. It's also sometimes difficult to figure out the term to search for. I'll add that to my list. Many thanks for your suggestion :)

[@ಪ್ರಸೀದದೇವೇಶ]

Really amazing, nice explanation too, thanks a lot for sharing these videos :)

[@LeilaGharani]

You're very welcome Vikram. Yes, TextJoin is really amazing :)

[@demilloyesudoss2659]

Super mam I except like this textjoin video. And we can join duplicate lookup value instead of vlookup

[@arghyadutta9091]

Great

[@bharanniashok2054]

i am using 2016 version but i am unable to use(same is not available). How to get that

[@Softwaretrain]

This video was realy useful to learn working and joining functions but maybe there is a another way to do this. We can use unpivot columns in power query and then we can do this work in an easier way.

[@LeilaGharani]

Yes - very true. Oz did a video on this with the same data set. You can find it here: kzbin.info/www/bejne/f6iliniOlrSGhc0. It's a great video!

[@Softwaretrain]

Leila Gharani woow he is a real gungester😊 I thought that Im the first person could find this way.

[@othmanelebbache3026]

how much likely to meet such problems which need such solutions ?

[@madhuk3101]

Amazing, thank you

[@be-tech2107]

Thank you so much

[@pcharl01]

I remember trying to use this function for this temp job I was working at. Unfortunately they were still at 2013 so it wouldn't work.

[@OzduSoleilDATA]

GOOD LORD!! Bill Szysz is a scary man! That is a smart ... and scary solution! 😆

[@LeilaGharani]

This made me laugh :)

[@BillSzysz1]

haha....Oz, we should meet in halloween :-))))

[@excelisfun]

Yes, a Halloween party!!! I can't wait : )

[@neodanado]

amazing. Thanks

[@mohamedchakroun4973]

Amazing videos leila thankssssss

[@LeilaGharani]

You're very welcome Mohamed.

[@SchioAlves]

Textjoin did the trick for for me, but unfortunately the "bulletproofing" using FIND only works if the value you are trying to return is in a row, not in a column, so I had to use a complex nest of formulas to solve my problem

[@Yppy26]

I tried it but for me, it returns "productivity,games, utility". Can you help me out?

[@ShashiBhushanSaurabh]

Wow, its just amazing!!!

[@LeilaGharani]

Glad you like it :)

[@javierlainez8376]

Woow so simple...great.

[@LeilaGharani]

Glad to hear that Javier.

[@janeyu332]

It’s amazing thank you

[@LeilaGharani]

You're very welcome Jane. Textjoin is really amazing!

[@hassanmanzoor4844]

great.. most helpful

[@LeilaGharani]

Thanks Hassan - & thanks to Bill for the great idea :)

[@VS-rh8rq]

Yes, true the textjoin formula is simpler and better...but, But, But ..when u started the FIND function and blah, blah.blah..needs pain killers first before understanding!!! But overall great stuff..thanks

[@LeilaGharani]

hahahaaa - you'll thank those painkillers later :) thank you for your comment. Glad you like the videos.

[@paulcarling6183]

Can this not be simplified even further using the unique function to return the unique values in a single column? =TEXTJOIN(", ",TRUE,UNIQUE(IF($B$5:$D$45=H5,$B$4:$D$4,"")))

[@LeilaGharani]

The video is from 2017. Back then there were no dynamic array functions that now make our life so easy :)

[@gauravsarin4725]

Amazing

[@khilapsingh2838]

Thanks very much!!!!!!!!!!!!

[@LeilaGharani]

You're very welcome Khilap :)

[@khilapsingh2838]

Awesome Vedio,Thanks very much!!!!!!!!!!!!

[@hosseinhosseinpoor4845]

thanks thanks

[@samuelkodjoe1645]

great!!

[@marathirecipes7545]

I needed a macro to join the textjoin

[@alpeshdoshi7827]

hi...madam...how r u ? hope all well. madam pls make income tax calculation​ vedio as per Indian tax rate....

[@LeilaGharani]

Hi Alpesh - Unfortunately I have no clue about the Indian income tax calculation....

[@Afs10161]

Leila 😍😘❤️

[@christiangeiselmann]

Am I right that this channel has been set up by Microsoft to increase their Office 365 sales?

[@LeilaGharani]

No, you are wrong.

[@christiangeiselmann]

Leila Gharani Oh, okay. Although I find your videos indeed useful, lately I stumbeled upon many that were useless to me because they feature things that eventually require Office 365... which even my 11000 work placs employer does not use.

[@meniporat3527]

"Szysz" should be pronounced "Shish" as in: "shish kebab"

[@victorzsasz5284]

🤓

[@LeilaGharani]

:)