TEXTJOIN Function, Like VLOOKUP with Multiple Matches - Google Sheets Tutorial, IF, TEXTJOIN, Arrays

[@AlexRecks]

Man, I was struggling all day at work trying to find this exact solution. Thank you so much!

[@RegRegi-n6q]

You are the best on Explanation. I want to work with you every day.

[@ExcelGoogleSheets]

Thanks!

[@RegRegi-n6q]

@@ExcelGoogleSheets I would be interested in exploring potential career opportunities within your organization for individuals with expertise in this domain.

[@kennethforbin5604]

This guy is a savior, i wish i can connect with him on Linkdeln or some place

[@melaniecroskrey5794]

Thank you!!! This is what I've been searching for for weeks! Very easy to understand and sharing the example spreadsheet was SO helpful.

[@takaakiyamada7294]

I had no idea that we could do multiple matches with Google Spreadsheet. Thank you! :-)

[@marliestusch7128]

This is exactly what I was looking for! Great video, simple explanation - everything works perfectly!

[@asiaboba]

I like all your lessons, even most of the time I already learned it before I play the videos, but I will still paying full attention on every things you teach me, and often repeatly watching your videos, because you always dive in every parts of your lesson very detail, let me found that I missed out a lot of easy functions which can let my job become much more easier and simple, really thanks for your tutorials, and thanks for teaching me all the skill and knowledge to strengthen my base in my programming studies, and I know all your students also loves the way you teaching and enjoy your lessons too, because the number of your students keep increasing time by time, keep up your good work, and I will always keep watching your tutorials for revisions... Little comment for you regarding the tutorial of this video: If there are multiple matches, I think it will be easier by using the filter function instead of vlookup function, and the solution will be Southern | =TEXTJOIN(“”, TRUE, FILTER(D:D, B:B=F7))

[@ExcelGoogleSheets]

In practice I would use QUERY for this, but I REALLY like the way you applied filter!

[@KristyCha]

All I was missing was the Array--why isn't that called out on the other 5000 articles I wasted my time on? Thank you!!!!! SUSCRIBED

[@msangelinejoe]

Wow it works with =arrayformula(textjoin) Thank you so very much for your help! Much love ❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️

[@AbdallahAbd-Elsamea]

God bless you man

[@shinhoyoung8426]

Hi I have only one question. I used "arrayformula function" to auto-fill values from second row to end row. If I don't want to drag down the "G7" function, How can i? e.g) region | Sales Rep Southern | =arrayformula(~~~~~) Midwestern | auto-fill result + how can i textjoin using " " in this case?

[@yjs6002]

Thank you so much, can we add one more condition?

[@coco99ca]

Thanks for the video, it helped. What happens if you get duplicates in your answers? How do you get rid of them?

[@mdrumi629]

Sir, as some new function has been added into Google Sheets, is there any work around for using Arrayformula with Textjoin or Index function to get rid of the dragging formula down like the case in this video?

[@kennethngai3236]

what formula would you use, if you have 2 creteria? let say you want the TextJoin southern region in year 2015 only?

[@eugeneestella5058]

wow, it really works, you solve my problem, I have been looking for this solution..Thank you

[@ericdwenger1464]

What is the formula if I'm trying to look through multiple sheets and compile all the data from multiple sheets into one cell with a line break?

[@alvesval]

Awesome video! perfect example. I have one question, though. Let's say for example 'Southern' and 'Paul Patrick are listed twice in multiple rows. The result would show 'Paul Patrick' as many times it appears in Column D, where Column B='Southern'. How to avoid this duplicate result and show 'Paul Patrick' only once in G7?

[@Ofer.Sheinberg]

In what situation will this IF statement be preferable to using FILTER, i.e. simplifying it to =TextJoin(", ",True,Filter(D:D,B:B=F7)) ?

[@ExcelGoogleSheets]

Don't exactly remember what I did in the video, but your solution looks good. This video should be similar to your formula kzbin.info/www/bejne/laKolGt5esyIopY

[@Ofer.Sheinberg]

@@ExcelGoogleSheets My intention isn't to shout “Hey, I've a better solution”; I take it for granted that each example is aimed at building a certain skill. Putting aside the question whether the FILTER function was even available when the video was published, I assume there _are_ situations where there's a need for that particular skill over solving the _example task_ using a different method. My intention is to understand which scenarios these may be.

[@ExcelGoogleSheets]

That's not what I thought. One of the downsides of making about 400 videos on this channel is that I don't remember what I did in most of them. The only way to find out if one way is more efficient than the other is to do some tests. Doing tests in Excel you can calculate the time it takes to do the calculation and see if there is any advantage.Unfortunately, this type of tests in Google Sheets rarely bare any noticeable results since there are variables like internet connection, browser, order of calculations etc. So I would say stick with FILTER option.

[@FilipeNanclarez]

This type of IF filter not works for me ... some changes on gsheets ?

[@ExcelGoogleSheets]

No changes here.

[@venkatanagadurgavijaykumar1884]

I learn new thing today, much thanks

[@rosomejro]

Great video! Thank you.

[@loreen731]

Hi! Just want to know on how I could do the same thing but the lookup value is a wildcard. I know wildcard cannot be used for if function. However, I am working on a report wherein I need to get all apps with No access status for a specific person. Tricky part is "no access" has ticket number next to it like "no access INC123". I tried doing Search and LEN function but it's not working. Or maybe I'm not doing it right. Many thanks!

[@SamEslamiehBISM]

I need the same thing. Did you solve this?

[@loreen731]

@@SamEslamiehBISM i did another workaround.

[@ziiz1477]

Thanks for this. Learnt something from you... How about if there's duplicates in D columns and to avoid repeating the same value when we do the text join

[@ExcelGoogleSheets]

You can use UNIQUE function to remove duplicates.

[@ziiz1477]

@@ExcelGoogleSheets can you please provide me the formula applying both unique and text join function to understand how it works… Many thanks!!!

[@ExcelGoogleSheets]

Make a sample spreadsheet with your problem and share it here.

[@ziiz1477]

@@ExcelGoogleSheets Can you please guide me how to share it. It doesn't give me an option to attach on this. Unless I reply to your message. Can I have your email address any chance to forward what exactly i am looking for as an output. Appreciate your assistance.

[@ExcelGoogleSheets]

@@ziiz1477 Under share change from restricted to anyone with a link, copy and paste the link here. Emails are security hazard for me.

[@davidhuwiler1103]

Many Many thanks!!!

[@dennis.teevee]

awesome! would definitely help me w/ my project!

[@amycarpio4030]

How to do that with multiple criteria for the IF?

[@prayagpandey1304]

THANK YOU SO MUCH...

[@elegancia3834]

Hello, its not working rather its showing FALSE On IF statement =IF($B$2:$B$7=F7,$D$2:$D$7)

[@lazalazarevic6192]

Is there an option in Google sheets to convert all formulas to values before exporting to XL?

[@ExcelGoogleSheets]

I think if you save to CSV it should do it.

[@jignapatel1728]

can we use txtjoinfunction over multiple sheets

[@ExcelGoogleSheets]

sure

[@Bestfungamer]

Hi. Nice video! Could you help, I can not find the function TEXTJOIN only TEXY and JOIN :(

[@ExcelGoogleSheets]

TEXTJOIN function doesn't show up in autocomplete as you type it, but if you just type it, it works. Keep in mind that if you just need a VLOOKUP type of function that returns multiple options, you may want to look at QUERY function instead. You may see how it works in this video kzbin.info/www/bejne/qWracmduhMaUpck

[@moheuddin_sehab]

damn.. you are so good at it 3 question how many years did it take you to know this much? how many years have you been using spreedsheet? and how much do you earn? annually!

[@sam_safari]

i keep asking my self the same question😊

[@jcarfam]

Can I expand this formula (G7) for over all cells of the G column instead of pull down or copy and paste? Like an ARRAYFORMULA in order that if I include a new row automatically the formula will be applied to the new G row cell.

[@fabielefritzen4015]

I believe this may help you kzbin.info/www/bejne/qWracmduhMaUpck

[@chocochiligaming1541]

THANKS!!!

[@Cameraon528]

im getting false when i do the simple =$B$2:$B$15=F7

[@ExcelGoogleSheets]

try =arrayformula($B$2:$B$15=F7)

[@alansavage3549]

great stuff. thanks.

[@paulloup5210]

Thanks for the video

[@EPSTomcat11]

Excellent!

[@cuneiformscript2665]

THANK YOU

[@waqaszohaib541]

❤❤❤

[@zunkman1]

You really can't do anything with the results besides read them. Why not just filter the columns?

[@kimchiyun4296]

Not works

[@Anditschristie]

Champion

[@melodiemturno2745]

topic is good but the explaining part is not quite clear.

[@riodevinda2275]

its true help me