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
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.
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 ;-)
@@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
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.
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.
The “Left” VLOOKUP is very clever. I like how the CHOOSE function was used in this solution. Thanks!
Your all Tricks are Very helpful..
Awesome tricks. Will definitely use these going forward.
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.
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)
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
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.
Great, well explained. Thanks for your efforts... regard.
Amazing . You are at joss level . Thank you
Love your videos. Awesome teacher. deep knowledge. Envy your work
amazing hacks! they helped me a lot! thanks for sharing!
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
vlookup tutorial is real treat .thx alot sir keep up good work
Last one was new to me and awesome, thanks
Thank you 🙏🏼!!!!!
Great tips. I got to start using new formulas in Excel
Excellent new tricks 👌
Thanks sir..... Superb. Thanks a lot.
Great work, so simply explained.
Great Work Chandeep
Dear Sir...still vlookup can't stand alone...right...but the trick you have shown to find duplicate is amazing
Wow, great examples! Thank you!
Brilliant as usual. Greetings from South Africa.
Thanks a lot last tip was owesome
You are just Amazing
This video was awesome.
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.
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 ;-)
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!
Yes it is 👍next we expect a video for an id its record behind a colom
If I have chosecol function why I apply v-lookup function I use X-lookup function
I always said you are simply amazing
You deliver amazing content in very less time.😊
Glad you like them!
Thank you
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.
Thanks
Thankyou so much 🎉🎉🎉🎉
Awesome. Solve my many problem
Enjoy :)
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.
ਜੈ ਹੋ.ਚਨਦੀਪ ਸਿੰਘ ਗੁਰੂ ਜੀ ਜਿੰਦਾਬਾਦ
How do you do it?? Brilliant 😊
Using choose and filter function it will be easy
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.
very good
👍 Great, very useful examples
Nice
Gracias pero porque no usar xlookup?
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
@Goodly
how to pass parameter to public URL ??
Please give me reply for solution
Didn't know most of them 😊
If our data in first column numbers then text join function works or not
I prefer xlookup and choosecol functions 😊
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