Excel Magic Trick 1835 Part 02: Lookup ROW with CHOOSECOLS, INDEX, XLOOKUP or FILTER function?
Вставка
- Опубліковано 13 лип 2024
- Download Excel File: excelisfun.net/files/EMT1835-...
Learn about how to lookup a row using CHOOSECOLS, INDEX, FILTER or XLOOKUP functions.
Topics:
1. (00:00) Introduction
2. (00:19) Viewer Question
3. (00:38) CHOOSEROWS and XMATCH
4. (01:23) XLOOKUP
5. (02:06) INDEX and XMATCH
6. (03:00) FILTER
7. (03:37) See that all these methods work in SUM function to add sales for the month
8. (04:01) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula
i love love love your materials and videos. Thanks for helping us to improve our excel skills.
You are welcome!!!!!
These new formulas are amazing and super versatile
Microsoft is very, very good to us!!!
I just love how you always know what problems to address. Simple everyday problems in Excel that we have to solve on a daily basis. And you tell us how. In many different ways. (In addition to the more complicated and advanced Excel problems, of course, that we, regular ExcelIsFun followers, also become familiar with.) Thank you!
You are welcome, ZT : ) : ) Thanks for the kind words!
very good, thanks
Thanks Mike :)
You are welcome, Formula Guy!!!!
Perfect Video. Can't wait for the next video about the function that handles ranges only. U probably mean the SUMIFS function
You are so smart : ) : )
Greats examples, thanks Mike.
You are welcome, Luciano!!!!
Thanks Mike... I like how you show many solutions. Keep up the great content.
You are welcome for the many : ) : ) : )
Thanks Mike!!
You are welcome, Chris M!!!!
Thanks Mike for this EXCELlent video.
You are welcome for the EXCELlent fun, Fellow Teacher!!!
A quicker one could be to select the whole table, press CTRL+SHIFT+F3, select the first column as names, and then use the indirect function referencing cell B8.
Recent subscriber here. Your videos are high quality and I’d recommend them to everyone who wants to level up their Excel skills and build a solid understanding of their features of interest.
Thanks so much for creating these videos and the related workbooks.
You are welcome!!! If you are new and have M 365 Excel, the MECS class is the most complete class I have ever created. It has Excel, Power Query, Power Pivot, Power BI, Dynamic Arrays and much more. The class has videos, free pdf notes (free book) and the many files so you can follow along. Have you seen my home page and the intro video that shows how the channel works. Here is home page: www.youtube.com/@excelisfun
Here is MECS class: ua-cam.com/play/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW.html
But don't do anything until you watch intro video : )
Mike is best teacher To teach you,
all my "magic" in power query I currently do is learned 95% from this channel and 5% from searching for more complex issues
So if you want to be the best in excel I say you found the perfect place to learn.
@@ExcelInstructor Thank you for your kind words, My Rad Friend : ) : ) : ) : )
Thanks alot Mike .... your explanation cleared it out ... i was using the the formulas in a wrong combination ( Filter inside index ... then i get lost).
Glad to help : ) : ) : ) : )
How can i auto extract rates of different products based on their quantity, if i purchase same product but on diff rates and quantity. In gsneral sheet rate shoud auto generate when i type a products, note when first product purchased qty finished then same product's rate updates when it was purchased second time with different rate and quantity.
Can you help me?
😁 👍👌
: ) : ) : ) : ) : )
Gret stuff again.
You implied that index would work for this problem in older versions of Excel.
But does Index work as a spillable function in say Excel 2019? I thought Index was updated to do that only in modern versions and in 2019 only gives a single cell answer.
Not that anyone should be using 2019!!
INDEX works in any version, but spilled arrays are not in 2019.
The world awaits Mike's python video....
Correct
Not for over a year... I don't know python. I am writing two new books: M Code and Dynamic Array Formulas. And I am taking care of my elderly Mom. This is one of Microsoft's new releases that I just have to miss... I am very sad about this because I am especially excited about K Means, which I have done by hand and they are insane to calculate and Python does it in BAM! time. I can't wait, but it is a long way off. : ( : ( : (
@@simfinso858 Not for over a year... I don't know python. I am writing two new books: M Code and Dynamic Array Formulas. And I am taking care of my elderly Mom. This is one of Microsoft's new releases that I just have to miss... I am very sad about this because I am especially excited about K Means, which I have done by hand and they are insane to calculate and Python does it in BAM! time. I can't wait, but it is a long way off. : ( : ( : (
@@excelisfun quite right Mike. Pythonpriority.
@@ricos1497 The M Code book should be really good : ) At least I have having so much fun writing it, even though the pages come slowly...
BAAAM
That is when you know we are having fun!!!!! BAM!