How to Solve Complex Lookup Problems in Excel with TEXTJOIN

Watch this video to find out how to get multiple lookup results in Excel. We'll cover a complex LOOKUP problem – the task is to find the header of the lookup criteria, which is in a matrix.

This example builds on the example shown in the previous videos which has a matrix of apps which belong to different divisions. The apps are the values inside the matrix and the divisions are the headers. The aim is to find the header value based on the matrix value. Since the data in this example is not unique and an app can belong to more than one division, the aim is to come up with a formula that finds all the divisions one app could belong to.

Excel's TEXTJOIN function is a great solution to this problem. On top, when combined with the FIND & IISNUMBER function, it can help us avoid duplicate results. Watch this video as I take you through Bill's formula solution.

✔ Alternate Solutions ✔

Watch Kevin's video to see 4 different approaches of solving this

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[josephcoon]

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!!!

[IzzyRazzaque]

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

[germanlondono]

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.

[ovidiuteodorescu]

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

[sudhirhiwale]

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

[vida]

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

[barttitulaerexcelbart]

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.

[MySpreadsheetLab]

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

[edrage]

Productivity and games sounds like a nice combination.

[Chandey]

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

[markosiridzanski]

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

[Jerrel.A]

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

[BillSzysz]

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

[josephcoon]

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.

[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.

[monicakim]

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!!!

[elima]

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

[deveshkoshti]

Thanks for your excel learning videos. Its very very useful for all excel learners / beginners.

Just small 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....

[Sal_A]

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