Great tips as always and a BIG thanks for your videos. It has helped me alot however for the last formula i think you could have used "=TAKE(FILTER(Table2[Training],Table2[Code]=D2),-1)" to get the last record irrespective of the sorting. Well it might not be good with large data set as you mentioned.
Would you please tell which screen capture application are you using? I like how some words appeared in front of your body and the others behind. The contents of your videos are very valuable. Thank you
@@danielgoncalveslima9350 The last two tricks do work in Excel 2016. You just have to enter the second to last one as an array formula with Ctrl+Shift+Enter. For the others, may just have to revert back to INDEX and MATCH
Nice to see some excel, I think I've seen Bill S do the fast Lookup, but you explain things so well, loved the last trick to force jumping to last record.
Cool tricks. I really want to join your video courses on Power Query but its out of my budget. I guess you should give some discounts to students on bundled courses.
An Excel expert published few years ago a report/article that stated as follows: VLOOKUP is number 3 Excel most used function, after SUM and AVERAGE, especially is users select the data and check the results in the status bar.
Hi Goodly, the stuff and content you provide is amazing ! I'm really excited to follow your chanel. I'm currently stuck in a simple problem,. I'm seeking for a value in a column and if found I need the values of the next column (5 times). Please let me know in which of your excellent videos I can find a solutiion for this use case, thank you very much in advance.. Regards from Switzerland ;-)
Thank you. I learned some things. But for the last example with the "9999", wouldn't you get wrong results for E1 if you also had an E19 in your employee list for example?
I don't prefer approx match because your code breaks anytime which will be hard to find with a large volume of data. It's useful when the input is range and output is single value based on range
Using VLOOKUP instead of XLOOKUP in 2023 is like using a typewriter to write a letter and send it through the mail instead of emailing someone. Yes it's more personal but much more complicated.
=TEXTJOIN(",",TRUE,IF(E3=Training[Code],Training[Training],"")) , This is working very fine(Working for all the rows). But =IF(E3=Training[Code],Training[Training],"") , this part is working for the first row only if I try to use it without textjoin. Why it is so ?
Actually I am little bit disappointed with this video. Your videos are always fantastic and teach me something new, in this video it is mainly vlookup with approx match. The problem with that is it requires sorted data, so better to use xlookup Plus combination of filter, sort, take
So right! If it cannot be ensured that the current data isn’t resorted and future changes/additions doesn’t “break” the sorting requirement, then the result cannot be trusted.
Why should I not use XLOOKUP intead of using choosecol and all that
Thanks, Goodly for the added perspective on Vlookup. I found the Vlookup on Duplicated Data to be helpful. Thanks again for the tips and tricks.
Awesome tricks. Will definitely use these going forward.
Amazing . You are at joss level . Thank you
Great, well explained. Thanks for your efforts... regard.
The “Left” VLOOKUP is very clever. I like how the CHOOSE function was used in this solution. Thanks!
amazing hacks! they helped me a lot! thanks for sharing!
Excellent new tricks 👌
Thanks sir..... Superb. Thanks a lot.
Last one was new to me and awesome, thanks
Great Work Chandeep
Your all Tricks are Very helpful..
You are just Amazing
Awesome 😊 as usual. Alsokeep in mind that if with text join you are working as Array function with other MO version's which don't has 365 version.
Thanks a lot last tip was owesome
vlookup tutorial is real treat .thx alot sir keep up good work
Great work, so simply explained.
Brilliant as usual. Greetings from South Africa.
This video was awesome.
Thank you 🙏🏼!!!!!
Love your videos. Awesome teacher. deep knowledge. Envy your work
Great tips. I got to start using new formulas in Excel
Yes it is 👍next we expect a video for an id its record behind a colom
Great tips as always and a BIG thanks for your videos. It has helped me alot
however for the last formula i think you could have used "=TAKE(FILTER(Table2[Training],Table2[Code]=D2),-1)" to get the last record irrespective of the sorting. Well it might not be good with large data set as you mentioned.
very neat, great tip
or single cell formula, regardless of sorting 😉
=XLOOKUP(E3:E7,Training[Code],Training[Training],,,-1)
(-1 argument => search last to first)
Dear Sir...still vlookup can't stand alone...right...but the trick you have shown to find duplicate is amazing
Shout out to you Goodly! I learn new things everyday. Thank you for making such awesome videos. Will be taking your course too in August.
Wow, great examples! Thank you!
Thankyou so much 🎉🎉🎉🎉
Would you please tell which screen capture application are you using? I like how some words appeared in front of your body and the others behind. The contents of your videos are very valuable. Thank you
Thank you
I always said you are simply amazing
Thanks
Awesome. Solve my many problem
Enjoy :)
Great tips! I would recommend adding some chapters/breaks on the video so it's easier to find the method that you are looking for on the video
What you did can easily be achieved through XLookup I believe.. No need to make life so complicated 😊
In may job, we use yet excel 2016...so no too simples
@@danielgoncalveslima9350choose columns is not there in Excel 2016 too...for that matter any of the array functions
@@danielgoncalveslima9350 even than this is not going to work in 2016.
@@danielgoncalveslima9350 The last two tricks do work in Excel 2016. You just have to enter the second to last one as an array formula with Ctrl+Shift+Enter. For the others, may just have to revert back to INDEX and MATCH
Yes, index match is amazing!
very good
If I have chosecol function why I apply v-lookup function I use X-lookup function
Using choose and filter function it will be easy
You deliver amazing content in very less time.😊
Glad you like them!
Xlookup is far more superior and easy to use than vlookup
Agree.
Agree, but not everyone has a version of Excel that includes Xlookup.
But it's an interesting way to explore
@@ExcelWithChriswill they have Choose formula?
@@MayurAgarwalmayuragrThey will have XLOOKUP if they have CHOOSECOLS.
Nice
Nice to see some excel, I think I've seen Bill S do the fast Lookup, but you explain things so well,
loved the last trick to force jumping to last record.
Cool tricks.
I really want to join your video courses on Power Query but its out of my budget. I guess you should give some discounts to students on bundled courses.
An Excel expert published few years ago a report/article that stated as follows: VLOOKUP is number 3 Excel most used function, after SUM and AVERAGE, especially is users select the data and check the results in the status bar.
I prefer xlookup and choosecol functions 😊
👍 Great, very useful examples
Didn't know most of them 😊
How do you do it?? Brilliant 😊
Hi Goodly, the stuff and content you provide is amazing ! I'm really excited to follow your chanel. I'm currently stuck in a simple problem,. I'm seeking for a value in a column and if found I need the values of the next column (5 times). Please let me know in which of your excellent videos I can find a solutiion for this use case, thank you very much in advance.. Regards from Switzerland ;-)
@Goodly
how to pass parameter to public URL ??
Please give me reply for solution
ਜੈ ਹੋ.ਚਨਦੀਪ ਸਿੰਘ ਗੁਰੂ ਜੀ ਜਿੰਦਾਬਾਦ
Thank you. I learned some things.
But for the last example with the "9999", wouldn't you get wrong results for E1 if you also had an E19 in your employee list for example?
I don't prefer approx match because your code breaks anytime which will be hard to find with a large volume of data. It's useful when the input is range and output is single value based on range
If our data in first column numbers then text join function works or not
Gracias pero porque no usar xlookup?
Index/match is more handy then vlookup.
Using VLOOKUP instead of XLOOKUP in 2023 is like using a typewriter to write a letter and send it through the mail instead of emailing someone. Yes it's more personal but much more complicated.
=TEXTJOIN(",",TRUE,IF(E3=Training[Code],Training[Training],"")) , This is working very fine(Working for all the rows). But =IF(E3=Training[Code],Training[Training],"") , this part is working for the first row only if I try to use it without textjoin. Why it is so ?
Actually I am little bit disappointed with this video. Your videos are always fantastic and teach me something new, in this video it is mainly vlookup with approx match. The problem with that is it requires sorted data, so better to use xlookup Plus combination of filter, sort, take
So right! If it cannot be ensured that the current data isn’t resorted and future changes/additions doesn’t “break” the sorting requirement, then the result cannot be trusted.
Xlookup