Excel - Unthinkable Lookup Tricks
ฝัง
- เผยแพร่เมื่อ 31 พ.ค. 2023
- Learn how you can use XLOOKUP and VLOOKUP to return multiple results in a customized order. XLOOKUP is amazing, but VLOOKUP still has a few tricks up its sleeve.
00:32 VLOOKUP - Return 1 Value
01:55 VLOOKUP - Dynamic Columns
02:52 XLOOKUP - Return 1 Value
03:10 XLOOKUP - Return Multiple Values
03:32 XLOOKUP - Return Selected Values
05:13 VLOOKUP - Return Multiple Values
06:45 VLOOKUP - Multiple, Selected, Ordered Values
07:30 VLOOKUP - Vertical Results
08:37 XLOOKUP - Return Multiple, Selected, Ordered Values
10:20 Conclusions - VLOOKUP vs XLOOKUP - วิทยาศาสตร์และเทคโนโลยี
this lesson was a revelation. I was like OOOHH and AAAHs all the time, great tips, sir!
Thanks for watching and saying nice things.
This is the most awesome demo of XLOOKUP and VLOOKUP tricks - THANK YOU!
Thank you for saying so. We appreciate it. Cheers.
@@bcti-bcti I went to your company's website and I couldn't find any courses. I am interested in Excel courses (from zero to hero), Power Query, Power Pivot, and Power BI.
Mind blowing tips. Thanks for sharing 👍❤
Our pleasure. Thanks for watching.
I just discovered this channel, and to think that it has less than 2k subscribers, is a true crime in TH-cam world.
Thank you sir for all the informations 🙏🙏
That is so gratifying to hear. We're still in our infancy (less than 1 year). Make sure you check out tomorrow's video. It's really cool.
I couldn't agree more!
I definitely learned something from this video!
Glad it was helpful! Thanks for watching.
This was great - your instruction style is excellent, too!
Truly unthinkable! Glad, I could locate your channel, It is a treasure trove! Thanks a lot!
Thank you, and welcome to the family.
Thanks for sharing great content. I think I will be using some of your idea in my current project. Thanks again.
brilliant and revealing highly useful hidden treasures , thanks a lot to share your invaluable knowledge
Thank YOU for taking the time to watch. We really appreciate it.
Excellent information. Real timesaver
Thank you!!!!
Truly amazing!
Thanks! Those are pretty cool.
Perfect! please share data file for practice..
I'm sorry; I don't have the file for this any longer. We hadn't been set up for posting exercise files at the time this video was created. My apologies.
its very useful
I agree 100%
Can you explain why you had to FILTER, then CHOOSECOLS instead of directly using CHOOSECOLS in the XLOOKUP return parameter?
That was just to demonstrate how to perform "vertical" filtering using the FILTER function. But, yes, the CHOOSECOLS function could have just used "Data7" and a {7,5,2,6,4} list for the columns.
Brilliant
I would like to thank you for choosing some good examples, but also clear and precise description and speech.
Though there is one thing I don´t fully understand in the last example, where you use both Filter and Choosecols, together with Xlookup, why not just wrap Xlookup in Chooscols (as long as it is unik rows in dataset)
Can you give me an example of how you would solve this using CHOOSECOLS(XLOOKUP())? I'm interested to see your solution.
If I have a table with e.i 3 columns, and I only want to see column 1 and 3 in the result, my formula will be like this:
=CHOOSECOLS(XLOOKUP(Compagny;tbl_Data[Compagny];tbl_Data[[Compagny]:[City]];"Not found";0);1;3)
Or if it is more complex and I want to be able to shift between coloumns to show, I will make a list of the columns to show, like this where I set G15 to 1 and G16 to 2
=CHOOSECOLS(XLOOKUP(Compagny;tbl_Data[Compagny];tbl_Data[[Compagny]:[City]];"Not found";0);G15:G16)
Hope it make sence!
@@bcti-bcti
Thanks for sharing,is it possible to download this file?🤩
I'm sorry; I don't have the file for this any longer. We hadn't been set up for posting exercise files at the time this video was created. My apologies.
only real issue with vlookup is it breaks if columns are added or removed
Very true. Now on to XLOOKUP.
Sir please clear my doubt
Copilot 365 soon available in Excel & other ai tools are there
So
is it still important to learn ms Excel deeply all functions and advance formulas
What should I do mam??
That is a valid concern. But if you think about people who truly understand Excel, versus the average user who relies on things being done for them...
Reliance on automation will only get you so far. Knowing Excel at its core will put you far and away above everyone else in your world.
It's like someone saying, "I'm an excellent driver." But then you say,
"So am I, but I know how to build cars and fix them when they break."
So it is always better to learn deeply ms Excel
No matter ai tools available in market..
Learn everything you can. You'll come out the winner that way.@@goutamnayak5011