Is it a bird ?🐦🤔 is it plane?✈ 🤔 No its Chandoo with is extra saucy excel skills to make my life easier!! Thanks Chandoo content always feels so...expensive! Great job!
Sir, I have a question for you. Why the formula you used to get multi-column results in my case is not working even though I pressed CTRL + SHIFT + ENTER? I haven't got 365 but office 2019. Thanks a lot for your attention
Anybody can say that same product has multi in column ,if i use VLOOKUP formula that is showing only first value i want remain value also kindly anybody resolve this
Bro Really you are doing "Nobel Job".. The way you make people understand, The way you Speak, the depth of your knowledge.. Just Awesome. You are helping many people with your tremendous knowledge.. please keep it up. God bless you..!!
i am sorry that i am commenting the harsh comment, skill share sucks, i am mean how can any website can go directly to payment gate way just by clicking anywhere. We people first require the details of course they provide then we can opt for payment mode. But before complete imformation they directly ask for money. Such idiotic thing i have seen in skill share
You are right. Few Telugu and few English books. For more, see this video where I give a tour of my home office - ruclips.net/video/P0Wb615JVZw/видео.html
Can we just mention the table name in Vlookup function, rather than selecting the range from the table? I did not understand what "Data" & "Prod_new" were from the Vlookup syntax shown in this video I wish anybody who knows about it, answers my question!
Pattern matching looking up two tables :1:27-give iferror 2:42 multiple column results 3:26 Combine all matches-text join function 4:27 there is a better option 5:04 Xlookup
You have to improve sir in explanation in brief. First example is not working with my dataset. If i want to apply same formula for other rows then format painter is not working too. Very confusing.
Hmm... may be my channel is not right for you? If you still want to learn... 1) Not sure where you are getting the "Format Painter" from...Use copy paste or paste formulas. 2) There is an example workbook in the description. Use it and learn better. 3) Pause / Rewind the video until it is clear for you.
Many thanks to you. Excellent tutorial. I am watching your other videos also and to be honest every day during this lockdown situation. it is very much helpful and now I am becoming data analyst day by day. Go head Chandoo.
Hey Chandoo, instead of formula =VLOOKUP(K4&"*",$C$5:$H$22,4,FALSE), I have used =SUMIF($C$5:$C$22,K4&"*",$F$5:$F$22) i.e if I want the total of all names that starts with "Kai". This gives me the sum of 195.
Hi, I m looking for a excel vba query, wr I need select samples of each user higher amt value and past in my output sheet. My input sheet has more user wd diff amounts. Could you plz help wd teaching. Thank u in advance...
Hi Shabari.. You can do this kind of thing with formulas too. So use them instead of VBA as that would be simple. Just try MAX or MAXIFS (explained in the advanced if tricks video here - ruclips.net/video/-yFpzIRifK4/видео.html ) All the best.
Please listen to the portion again. I said you get free trial period of 30 days. That was at the time of recording the video, not sure if SS has changed their trial policies now.
This is an "Advanced" video as the title says. Please watch the beginner tutorial first 👉ruclips.net/video/8rtvDQVQaA0/видео.html You can also pause the video, rewind or take notes from the example file (check description) to learn better. Happy learning.
Thanks. Focus a bit on the journey of all stages of data analysis - requirements, data preparation, analysis, visualization, communication and improvement. Here is a recent video on this topic - ruclips.net/video/gVr9f1GJdZc/видео.html
Hi Chandoo, Thank you for the video, very helpful again! I have a question thou, with reference to the first example, if I want to look for any text in the column referring to #cash#, how would create the vlook-up? Thank you:)
Thanks Rhiteek... You can use FILTER to do that. =SUM(FILTER(data[[April]:[June]], data[Name]=K8)) should work (assuming K8 has the name you are looking for).
Great video! Question: Looking at the "Combine all matches with IF" example: I need to add up April column data for each Gunar Cockshoot row. Can you make a video on this?
You can use the SUMIFS formula for such things. See this video on SUMIFS ruclips.net/video/YEt-aYbDTrs/видео.html or read up about the function here - chandoo.org/wp/introduction-to-excel-sumifs-formula/
Hello Chandoo, I am happy to write to you. I have a burning need to be able to use Excel to make my dashboards to manage my activities. I need your advice on how to start from the beginning to a high level. I really need your advice. Thank you very much for your understanding.
Hi Serge.. thanks for your message mate. I suggest learning the core Excel concepts - data extraction, Pivots, formulas, charts & formatting first. Then you can go and build dashboards. If you want a structured course to take you thru this process, I suggest enrolling in Excel School program - chandoo.org/wp/excel-school-program/ If you want information, tutorials and articles, see chandoo.org/wp/advanced-excel-skills/ All the best.
Hi Chandoo, I must say you are awesome. I have a scenario where I always struggle. let say column A has item code and B has selling price. What if I need to update the prices partially. how do I do lookup for my price master? Pls comment
Thanks Shankar for your lovely words. What do you mean by "update prices *_partially_*"? Are you trying to update the prices only for certain product codes or lines but not others?
Hi Chandoo, I loved this video. Incredible work. I have a very large CSV file with 1.2 million rows and approx 300 columns. I want to extract only few particular columns from this data. How can it be done? Many thanks in advance.
Hey Amit... If you have such large files, you should either use Power Query to extract the data or even migrate the analysis to Power BI. See this video on how to handle more than a 1 mn rows in Excel - ruclips.net/video/5u7bpysO3FQ/видео.html
@@chandoo_ Hi, Thanks for your response. I have already seen this video and love all of your videos. Love the way you present the different scenarios. I am asking, how could I keep only few desirable columns and discard the rest from this file. If I have to delete all of the columns(using power query) except only few(4-5), what is the easy way. Can I pass a list of columns names some how that power query will keep only those columns and delete/discard the rest.
You can try Range.Copy method to copy selected data to a new file. Then just save it as CSV and Excel will do the needful. See my getting started to VBA tutorial here - ruclips.net/video/5k-KpSkrROw/видео.html
Hi Syed.. you can visit chandoo.org/lookups for the course details. If you want a sample file, download it here - chandoo.org/wp/wp-content/uploads/2021/07/5-lookup-tricks.xlsx
Hi Sir, thank you for all the videos and tips, they are really helpful. This Tuesday I have my last step of interview in office for a junior data analyst position and they will ask me for a case maybe percentile, revenue, percentage etc . I don't have a lot of experience in excel mostly I have been focused to pivot tables and also toolpak. What do you think is the best to focus on? Thank you in advance! Denisa
Hi Denisa... Congratulations on your interview and wish you all the best. I suggest focusing on data analysis with formulas and pivots too. See these videos. Must know formulas - ruclips.net/video/Q-XGJbHG8Vo/видео.html & ruclips.net/video/P7w6JeNS_SA/видео.html Pivot table basics - ruclips.net/video/aofsdpjvK7w/видео.html Do let me know how the interview went.
@@frenan82 suppose i have customer A 4 times with amt of 50,60,70 and 80 and if I use xlookup i would get 50 for all or any of same amount for all 4 line items. Hope I am clear with my question?
This man is a wizard. Love this!
🎩🐇💥
1000% agree, keeping my playlist of all his tutorials, every videos are so wonderful, thank you so much!!!
Most RUclipsrs forget The practice files, That would be my advice to your bro Chando... Thanks 😊😊
Oops.. Please get it from here - chandoo.org/wp/wp-content/uploads/2021/07/5-lookup-tricks.xlsx
Please check out the lookups for data analysis course here💥👉 chandoo.org/lookups
Is it a bird ?🐦🤔 is it plane?✈ 🤔 No its Chandoo with is extra saucy excel skills to make my life easier!! Thanks Chandoo content always feels so...expensive! Great job!
😊😍 Thank you.
Sir, I have a question for you. Why the formula you used to get multi-column results in my case is not working even though I pressed CTRL + SHIFT + ENTER? I haven't got 365 but office 2019.
Thanks a lot for your attention
Chandoo, crash course Lookups for Data Analysis, I can pay US$ 6 because I can't pay US $ 25, please view my request then answer me.
Anybody can say that same product has multi in column ,if i use VLOOKUP formula that is showing only first value i want remain value also kindly anybody resolve this
Bro Really you are doing "Nobel Job".. The way you make people understand, The way you Speak, the depth of your knowledge.. Just Awesome.
You are helping many people with your tremendous knowledge.. please keep it up.
God bless you..!!
Hello, SUM Vlookup is great, but is a way to sum all sales from all rows and 3 months where is Gigi Bohling ?
i am sorry that i am commenting the harsh comment, skill share sucks, i am mean how can any website can go directly to payment gate way just by clicking anywhere. We people first require the details of course they provide then we can opt for payment mode. But before complete imformation they directly ask for money. Such idiotic thing i have seen in skill share
Would you please,
explain why you use false in vlookup?
And when to use false or true?
Thank you
Hi Chandoo, big thanks for sharing tips and techniques. There is so much to learn.
Thanks
Thank you
Thank you for invaluable knowledge and skills you share with everyone.
You are welcome Zulfiqar... 😀
Sum with Vlookup is excellent trick. Thanks
Unknown things among known things. Perfect...
Hi Mr. Chandoo
Why we should use vlookup function while we have xlookup function?
Hi Ayham... That is the 5th trick in the video... All the ideas presented are still valid and useful even if you have XLOOKUP.
In the 1st trick. what if there are multiple matching values with "Kai"?
I used the new ARRAYTOTEXT function instead of TEXTJOIN at 3:44
Cool... 😎
Hello Chandoo .. why is our video quality is unavailable.
nice job. put practiced file in the description video
Thanks ... I added the practice file now. Please refer to it.
How to sum april results for a person (Consolidate) and use vlookup
Superb 👍
Hey Chanddo, What are those books behind you. Mighty curious to know(Lemme guess: Seems Telugu books) ;)
You are right. Few Telugu and few English books. For more, see this video where I give a tour of my home office - ruclips.net/video/P0Wb615JVZw/видео.html
Can we just mention the table name in Vlookup function, rather than selecting the range from the table?
I did not understand what "Data" & "Prod_new" were from the Vlookup syntax shown in this video
I wish anybody who knows about it, answers my question!
What happened to you chandoo.., you have changed drastically and not able to digest seeing you like this.., r u fit and fine?
Nothing.. That is how I used to look 2 years ago.
Quick question. Should someone with updated Excel, learn Vlookup when they can learn Xlookup instead?
Hi Chandu, could you please share the link of crash course you mentioned in the video, I am unable to locate from comments.
Sure. Please see chandoo.org/wp/lookups-for-data-analysis/
Practice files complete your teaching🙏🙏
Hi Diosdado... I have included it now. Please get it from here - chandoo.org/wp/wp-content/uploads/2021/07/5-lookup-tricks.xlsx
Is there any course for google app script
Thank You 😊
You're welcome 😊
Pattern matching
looking up two tables :1:27-give iferror
2:42 multiple column results
3:26 Combine all matches-text join function
4:27 there is a better option
5:04 Xlookup
Hello brother, thanks for your help me understand the first Vlookup trick. I have tried with laptop but unfortunately
thanks for sharing the excel files
Well Explained.Thank you very much.🙏🙏🙏🙏🙏
xloop up is so easy so why to use vlookup?
You have to improve sir in explanation in brief. First example is not working with my dataset. If i want to apply same formula for other rows then format painter is not working too. Very confusing.
Hmm... may be my channel is not right for you? If you still want to learn...
1) Not sure where you are getting the "Format Painter" from...Use copy paste or paste formulas.
2) There is an example workbook in the description. Use it and learn better.
3) Pause / Rewind the video until it is clear for you.
@@chandoo_ I think you are right. This channel is not for me and not for others who want clear and descriptive details. Thank you!
Thank you~~😎😎🙏🙏🙏🙏🙏🙏😁😁
Very good video and rhanks
Many thanks to you. Excellent tutorial. I am watching your other videos also and to be honest every day during this lockdown situation. it is very much helpful and now I am becoming data analyst day by day. Go head Chandoo.
Anthony... That is so great to hear. My mission is to make you awesome. I am glad you are finding the videos helpful. More power to you.
Thank you for super videos. Please make more videos for the power apps of MS.
Hey Chandoo, instead of formula =VLOOKUP(K4&"*",$C$5:$H$22,4,FALSE), I have used =SUMIF($C$5:$C$22,K4&"*",$F$5:$F$22) i.e if I want the total of all names that starts with "Kai". This gives me the sum of 195.
I have shown the snip of the formula in this link
Awesome features...thanks chandoo
My pleasure
Brother text join does not work for me 😭 can you help me on this?
Sorry about that bro... You need either Excel 365 or 2019 to have it work. You can also use it on Excel online.
The Sum(VLOOKUP) was just brilliant!
Chandooo ji is the best!
Thanks mr.chandoo
Hi, I m looking for a excel vba query, wr I need select samples of each user higher amt value and past in my output sheet. My input sheet has more user wd diff amounts. Could you plz help wd teaching. Thank u in advance...
Hi Shabari.. You can do this kind of thing with formulas too. So use them instead of VBA as that would be simple. Just try MAX or MAXIFS (explained in the advanced if tricks video here - ruclips.net/video/-yFpzIRifK4/видео.html )
All the best.
Hi Chandoo, I am unable to find TEXTJOIN function in Excel 2016. Is there a way to enable the function?
My bad.. I was wrong. This is introduced in Excel 2019.
You are genius man
I love your vids, but the entrance to this vid was weird
I watched it again and yeah, it is weird. Not sure what I was going for.
Chandoo, love u
You are amazing chandoo..I've learned something new today.. Thank you!!!❤
My pleasure 😊
This is simply the next to next level of excel 🙂
Thankyou²
Great video
Plz link me
Nice
Nice
Chandoo bhai jug jug jiyo ☺
Thanks Arindam ji for that blessings 🙏
Site is asking for payment... Of 1200 yr. To excess your course.
Yes.. The skillshare course are paid courses.
@@chandoo_ but you told that we get this course for free...
Please listen to the portion again. I said you get free trial period of 30 days. That was at the time of recording the video, not sure if SS has changed their trial policies now.
@@chandoo_ ok... Thanks for your support. Your content fill lots of energy ⚡ to learn more.
I just loved the TEXTJOIN !!!!
It is very fast, difficult to understand a beginner.
This is an "Advanced" video as the title says. Please watch the beginner tutorial first 👉ruclips.net/video/8rtvDQVQaA0/видео.html
You can also pause the video, rewind or take notes from the example file (check description) to learn better.
Happy learning.
@@chandoo_ Thank you for your reply and effort. All the best wishes👍
sir you are wonderful..now I will try my 150% ....please advise after excel what I need to learn as I have to be in data analysis role
Thanks. Focus a bit on the journey of all stages of data analysis - requirements, data preparation, analysis, visualization, communication and improvement. Here is a recent video on this topic - ruclips.net/video/gVr9f1GJdZc/видео.html
Hi Chandoo,
Thank you for the video, very helpful again! I have a question thou, with reference to the first example, if I want to look for any text in the column referring to #cash#, how would create the vlook-up? Thank you:)
Chandoo Anna, you always rock
Thanks Bhavani... 😍
You are toooo fast. Not interested.
Thanks and goodbye.
is there a reciprocal to trick 1?
You mean like, finding who has the quantity of 73?
You can use XLOOKUP to do that. See this video - ruclips.net/video/gpwqUc3y7ZA/видео.html
Amazing, thank you!
Glad you liked it!
Many Thanks very Helpful
awesome video, just one doubt. at 2:33 when we sum the quantity for gigi bohling, what if we need to do it across all product ids?
Thanks Rhiteek... You can use FILTER to do that. =SUM(FILTER(data[[April]:[June]], data[Name]=K8)) should work (assuming K8 has the name you are looking for).
Yes very usefull tricks
Thanks Vishal...
Great video! Question:
Looking at the "Combine all matches with IF" example:
I need to add up April column data for each Gunar Cockshoot row. Can you make a video on this?
You can use the SUMIFS formula for such things. See this video on SUMIFS ruclips.net/video/YEt-aYbDTrs/видео.html or read up about the function here - chandoo.org/wp/introduction-to-excel-sumifs-formula/
Thank you
You are welcome Kannan... 😊
Text join was awesome
Isn't it... :)
Hello Chandoo,
I am happy to write to you. I have a burning need to be able to use Excel to make my dashboards to manage my activities. I need your advice on how to start from the beginning to a high level. I really need your advice.
Thank you very much for your understanding.
Hi Serge.. thanks for your message mate. I suggest learning the core Excel concepts - data extraction, Pivots, formulas, charts & formatting first. Then you can go and build dashboards. If you want a structured course to take you thru this process, I suggest enrolling in Excel School program - chandoo.org/wp/excel-school-program/
If you want information, tutorials and articles, see chandoo.org/wp/advanced-excel-skills/
All the best.
@@chandoo_ many thanks
You are the best teacher in the world!!!
Just amazing 😀
Thanks! 😃
Hi Chandoo, I must say you are awesome. I have a scenario where I always struggle. let say column A has item code and B has selling price. What if I need to update the prices partially. how do I do lookup for my price master? Pls comment
Thanks Shankar for your lovely words.
What do you mean by "update prices *_partially_*"? Are you trying to update the prices only for certain product codes or lines but not others?
@@chandoo_ Hi, Yes, If I need to update certain lines only but not others. Many Thanks for your reply..
Hi Chandoo, I loved this video. Incredible work. I have a very large CSV file with 1.2 million rows and approx 300 columns. I want to extract only few particular columns from this data. How can it be done? Many thanks in advance.
Hey Amit... If you have such large files, you should either use Power Query to extract the data or even migrate the analysis to Power BI. See this video on how to handle more than a 1 mn rows in Excel - ruclips.net/video/5u7bpysO3FQ/видео.html
@@chandoo_ Hi, Thanks for your response. I have already seen this video and love all of your videos. Love the way you present the different scenarios. I am asking, how could I keep only few desirable columns and discard the rest from this file. If I have to delete all of the columns(using power query) except only few(4-5), what is the easy way. Can I pass a list of columns names some how that power query will keep only those columns and delete/discard the rest.
Awesome Anna !
Thanks Sudhakar... 😍
Thanks Chandoo! Awesome.. as always :)) Thumbs up!!
Thanks Wayne :)
Text join 😍
Isn't it awesome .🤩🤩🤩
Hi,
Is there any way in VBA coding to copy two cell by inputting a tab between two, to paste it in csv file
You can try Range.Copy method to copy selected data to a new file. Then just save it as CSV and Excel will do the needful. See my getting started to VBA tutorial here - ruclips.net/video/5k-KpSkrROw/видео.html
@@chandoo_ Thanks alot.
Your videos are really helpful 😊
Awesome! Thanks.
Thanks Sir 🙏
Most welcome
Link ?
Hi Syed.. you can visit chandoo.org/lookups for the course details. If you want a sample file, download it here - chandoo.org/wp/wp-content/uploads/2021/07/5-lookup-tricks.xlsx
Hi Sir, thank you for all the videos and tips, they are really helpful.
This Tuesday I have my last step of interview in office for a junior data analyst position and they will ask me for a case maybe percentile, revenue, percentage etc . I don't have a lot of experience in excel mostly I have been focused to pivot tables and also toolpak. What do you think is the best to focus on?
Thank you in advance!
Denisa
Hi Denisa... Congratulations on your interview and wish you all the best. I suggest focusing on data analysis with formulas and pivots too. See these videos.
Must know formulas - ruclips.net/video/Q-XGJbHG8Vo/видео.html & ruclips.net/video/P7w6JeNS_SA/видео.html
Pivot table basics - ruclips.net/video/aofsdpjvK7w/видео.html
Do let me know how the interview went.
@@chandoo_ Thank you very much! Sure,I will let you know on Tuesday.
Thanks Sir for taking pains & introducing compact 1 hour Lookup functions video,it will refresh me☺️
Most welcome 😊 I am planning to launch similar 1hr courses on other topics of Excel for data analysis. Stay tuned.
Awesome lookup tricks, thanks 😊
Happy to help!
Chandoo 👍👍👍
Trust me, I like your every single video you post.
Thanks 😀 for your love and support....
You are great buddy 👍
Thank you for sharing valuable knowledge
My pleasure
Awesome. Thanks. I will see the course.
My pleasure Fernando... Let me know how you like the course...
Great tips. Thank you 🙏
You're so welcome!
Thanks a lot Dear Chandoo…
You are welcome Feros...
If the lookup is same and value if different and if use xlookup or vlookup I will get first value for all the lookup, please help me on this.
with xlookup you can choose between the first and the last value of the list changing the order. I hope this help you.
@@frenan82 we have search first to last and vice versa and binary search in ascending or descending order which didn't helped
@@frenan82 suppose i have customer A 4 times with amt of 50,60,70 and 80 and if I use xlookup i would get 50 for all or any of same amount for all 4 line items. Hope I am clear with my question?
@@saahil0203 A --> (xlookup)
A 50
A 60
A 70
A 80
What are you looking for? what result do you need?
@@frenan82 I need A=50,A=60, A=70, A=80 not A=50 for all 4 A's.
3:00 love this
Thank you so much
You're most welcome
Thanks chandoo…
You are welcome Ashfaq...