TEXTJOIN Function, Like VLOOKUP with Multiple Matches - Google Sheets Tutorial, IF, TEXTJOIN, Arrays
Вставка
- Опубліковано 15 гру 2016
- Learn how to use TEXTJOIN Function to return multiple matching results, which is something you wouldn't be able to do using VLOOKUP function.
TEXTJOIN VLOOKUP replacement
docs.google.com/spreadsheets/...
Google Sheets
www.google.com/sheets/about/
Man, I was struggling all day at work trying to find this exact solution. Thank you so much!
Thank you!!! This is what I've been searching for for weeks! Very easy to understand and sharing the example spreadsheet was SO helpful.
This is exactly what I was looking for! Great video, simple explanation - everything works perfectly!
This guy is a savior, i wish i can connect with him on Linkdeln or some place
You are the best on Explanation.
I want to work with you every day.
Thanks!
@@ExcelGoogleSheets I would be interested in exploring potential career opportunities within your organization for individuals with expertise in this domain.
I had no idea that we could do multiple matches with Google Spreadsheet. Thank you! :-)
wow, it really works, you solve my problem, I have been looking for this solution..Thank you
Great video! Thank you.
Wow it works with =arrayformula(textjoin)
Thank you so very much for your help! Much love ❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️
great stuff. thanks.
Excellent!
awesome! would definitely help me w/ my project!
I learn new thing today, much thanks
Thanks for the video
God bless you man
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
Many Many thanks!!!
THANK YOU SO MUCH...
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))
In practice I would use QUERY for this, but I REALLY like the way you applied filter!
THANK YOU
THANKS!!!
Thanks for the video, it helped. What happens if you get duplicates in your answers? How do you get rid of them?
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?
Thank you so much, can we add one more condition?
❤❤❤
Champion
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?
what formula would you use, if you have 2 creteria? let say you want the TextJoin southern region in year 2015 only?
How to do that with multiple criteria for the IF?
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!
i keep asking my self the same question😊
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?
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?
Is there an option in Google sheets to convert all formulas to values before exporting to XL?
I think if you save to CSV it should do it.
Hi. Nice video! Could you help, I can not find the function TEXTJOIN only TEXY and JOIN :(
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 ua-cam.com/video/s9wA48Oaaog/v-deo.html
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!
I need the same thing. Did you solve this?
@@SamEslamiehBISM i did another workaround.
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
You can use UNIQUE function to remove duplicates.
@@ExcelGoogleSheets can you please provide me the formula applying both unique and text join function to understand how it works… Many thanks!!!
Make a sample spreadsheet with your problem and share it here.
@@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.
@@ziiz1477 Under share change from restricted to anyone with a link, copy and paste the link here. Emails are security hazard for me.
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)) ?
Don't exactly remember what I did in the video, but your solution looks good. This video should be similar to your formula ua-cam.com/video/_qEc8CEgUl4/v-deo.html
@@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.
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.
Hi. I have tried a vlook up and successfully return multiple values, however, the conditional formatting and the formula is not working on those returned values. Do you know how to fix it? For example from cell C2:I2, there is a data that has been pulled out using vlookup, however, I want to get the average of those values and this formula =Average(C2:I2) is not working.
This type of IF filter not works for me ... some changes on gsheets ?
No changes here.
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.
I believe this may help you
ua-cam.com/video/s9wA48Oaaog/v-deo.html
can we use txtjoinfunction over multiple sheets
sure
Hello, its not working rather its showing FALSE On IF statement
=IF($B$2:$B$7=F7,$D$2:$D$7)
Not works
its true help me
You really can't do anything with the results besides read them. Why not just filter the columns?
im getting false when i do the simple =$B$2:$B$15=F7
try
=arrayformula($B$2:$B$15=F7)
topic is good but the explaining part is not quite clear.