Excel Magic Trick 1481: Multiple Table Lookup: SWITCH or INDIRECT Function? VLOOKUP too
HTML-код
- Опубликовано: 24 мар 2018
- Download Files: Start Excel File: excelisfun.net/files/EMT1479-...
Entire page with all Excel Files for All Videos: people.highline.edu/mgirvin/ex... about how to
In this video learn how to how to perform lookup from multiple tables. Learn two methods: 1) VLOOKUP and SWITCH Function or 2) VLOOKUP and INDIRECT Function with Defined Names.
1) (00:01) Introduction
2) (01:30) VLOOKUP and SWITCH Function (New Excel 2016 Office 365 Function)
3) (07:20) VLOOKUP and INDIRECT Function with Defined Names.
4) (11:06) Summary
No you never stop your videos please your are just special.. And keep do videos please... Am sharing your videos to all my friends and relatives...
Just when I think, you couldn't surprise me anymore; you do it again. You're MAGIC MIKE
So happy to help. Funny thing is that when I was a laundry boy at age 15 (first real job), they gave me the name Magic Mike because I organized and color coded all the completed laundry lol, BUT: then at age 49, the coach on my bicycle racing team gave me the name Magic Mike because I was so cheerful and helpful to all the teammates. And now here is the third time I have been given this name becasue I make magic with Excel lol*100
@@excelisfun It may be easy to you, but what you do helps and changes life significantly. I'm grateful to you. ♥️🙏
@@777kiya I believe that my duty is to try and make the world a better place, and have fun doing it, laundry, bikes or Excel!
OMG you are the King of Excel thank you so mch Mr. Mike i really loved the video i'm so sorry i'm too late to see this video because i was in hospital.
Never too late... I hope you will be okay, Ismail - I hope you are okay after your hospital visit.
Yeah I'm fine now thanks for asking Mr. Mike waiting for new videos.
Thanks for posting the videos. Really appreciate seeing the two example (switch and indirect) to solve the problem.
Excellent. I personally like INDIRECT. It is just unbeatable..
Yes, it certainly is elegant. Thanks for your support : )
I keep learning your interesting video daily. It's so great and I love them. Thank you for all your works.
Thanks Mike for this EXCELlent video.
You are welcome for the fun with Excel!
What is wrong with people?!
These videos are amazing and are not just getting the hits and likes it deserves.. Hats off for making difficult things seem so simple..
I 100% agree. In the last year I have considered closing excelisfun channel because it is so discouraging. The only conclusion that I can come to is that there is not much demand for the videos I post - especially since I see other Excel RUclipsrs with more views and Thumbs Ups on some videos. To me I interpret it to mean that what I make, what I do in the video, and the presentation is not valued.
I appreciate your support with your comments, Thumbs Ups and Sub : ) Hopefully over the next year i will try and do better so views and support will increase here at the excelisfun channel.
100% agree. All your videos are awesome and extremely well thought out. Thanks you.
Ohhhh I really like this switch! It really is the swiss army knife of lookup :) Many thanks for the great video.
You are switchingly welcome, teammate!
As always nice enjoyable delivery of content, Makes the difficult functions seem easy.
I am glad to help : )
Absolutely awesome! I've never used SWITCH function before! Thumbs up of course and thanks for the video :)
You are welcome, teammate!!
:)
This is very helpful! Thank you!
Great video! Unfortunately, don't have switch formula on my work but I have discovered Indirect formula. Still, don't see the logic of it but can see the huge benefits for my work using it. Thank you!
Wow, Great clear explanation of the 2 methods Mike!!
Glad you like it! Thanks for your support : )
omg...this is an awesome function....thanks for the video!
You are welcome!
I love EMT very informative very fun ExcelIsFun is at the top :-) I love the description of switch&Lookup as a swiss army khnife :-)
Yes, SWITCH and CHOOSE are so amazing because you can look up anything : ) Swiss Army Knife : )
Like all your tutorials. Excel-lent
Thank you, China Bono, for all your likes!!! And, I am glad that you think the videos are EXCELlent : )
Great as usual...Thanks Mike
Glad you like it!!!! Thank you very much for your support!
Love your videos and your voice!! You sound like a cool to hang out with.
Love is good! Excel is fun! I am glad that you like the videos. Thanks for the support with your comment, Thumbs Up and Sub : )
Great tricks! Thanks!
You are welcome, Teammate!
Wow, amazing Mike!
Glad you like it, Chris! Thanks for your support : )
Thanks Mike. Great video.
You are welcome, WRH!!!!
very very helpful
thanks for sharing.... very informative
I am so glad that the videos help! That is why I post. Please help me and click that Thumbs Up on every video that you watch and leave a comment when you feel inspired : ) Sub too so you know when new vids come out!
ExcelIsfun, obrigado.
Glad you like it!!! Thank you for your support : )
Amazing Sunday Afternoon FUN. Thanks Mike. I need to practice on the Switch though. I use the IFS more often for multiple table. By the way, I saw you - OZ and Leila in a photo at a summit on FB. I would pay to be in a photo with the three of you guys :) :) :)
No need to pay - some day we will do it for free : ) Thanks John Borg !!!!
thanks for this excellent method sir,
further if we have many tables say 100 or more, then we have to name them so many times to use indirect. how to do that sir ?
I like the new logo and banner
Glad you ya like it, Level 0!!!
Hi, thanks for your great videos.
In this video you have a list of units and they are overlapping. Quad 1-5 units and then 5-10 units. What happens if you choose 5 units?
Brgds BHD
Liked without knowing what is video all about, because i already know it will be informative!
Me too! ♥
Thank you so much - and I will try not to let you down !!!!
You are welcome. D. Hall - Thank you so much : ) : )
Thanks!
You are welcome! Thanks for the support with your comments, Thumbs Ups and Sub : )
Switch is awesome!
Glad you like it! I certainly do : ) Thanks for your support!
that's great mike.i'm very very like youre cse book that leila gharani itroduce for me to read. thats Excellent.in this video i lean a new formula, Swich, thats very useful.maybe becouse the tables have a same header and same size too, By combining Offset and match function, and ceate dynamic range, is the another way to solve this.
I am glad that you like the video and the CSE book! Thank you for your support : )
Hi Mike
you awesome
Glad you like the videos!
How to run this tip with data in work book and collect them in a separate worksheet
Sir Using vlookup and offset formula multiply refrence and multiple table lookup please solution sir
Sir, I have been learning excel since 2013 by watching your videos on RUclips. I have done some demo projects based on the work experience at my previous companies. Whatever I'm today in excel, just because of you. You are my inspiration, my learning Guru. That's why I'm sharing these 3 projects with you on your Google+ and Twitter Account. Please, Sir, let me know your opinion on these 3 projects. I'm looking forward to your reply.
Unfortunately I am working 12+ hours a day right now and have no time. I am glad that the videos help you, though.
OK sir, but if you have any free time, check my 3 projects. I have sent these projects on your Google+ and Twitter account. Good bye, Sir.
Good and new one for me, I need to use this
Awesome! Glad it will be helpful! Thanks for your support : )
ExcelIsFun. really helpful
Like and then watch the video... Thats the confidence
Thanks for that support : )
Very good
Glad you like it, Monica : )
Hi sir yoda thank's for the video e,d for the shortest formula you'r a genius, all what i learn and i can do it's from you think's
As always - I am glad that the videos help you : ) They are fun to make and fun to have you watch! Thanks for your support!
If you create an Excel table (Ctr T) and mane them properly it indirect will also work...
Dear Mike, I know this comment isn't supposed to go here, but i have been working on a project that is a mixture of several of your worksheets. I am stuck on a final part of the project that I can not figure out. Is there someplace to upload my project to so so I can show what its not doing correctly?
outstanding
Glad you like it! Thank you for your support : )
Have Excel 2016, but not the 365 version so I can't try out the switch function. :(
Hi, whenever you have a chance. Please, please make some excel vba tutorials. Thank you en advance
Hi there, thanks for the great videos. Any plan to do a comprehensive tutorial on financial modelling? It will be really helpful. Thanks!
I already have a 110 video playlist for finance. Here is is: ruclips.net/p/PL90E1F26C7B85E78F
Thank you for your support with comments, Thumbs Ups and Sub.
Wow, thanks a lot! Keep up with the good work!
I can keep the work up with help from viewers like you!!! It helps a lot to click thumbs up and leave a comment sometimes : )
will do. Surely!!!
Hi Mike
I have a question please
How do I get a certificate MVP
It used to be much, much harder a few years back. But now you can nominate yourself. Search Google for Excel MVP and llok for site where you can nominate yourself and then list the reasons why you should be one : )
I think you have to be helping people out with Excel like making free contant on you tube and or forums.
??? ---can't download the associated files - what's up with that? Got : ForbiddenYou don't have permission to access /mgirvin/RUclipsExcelIsFun/EMT1479-1481.xlsx on this server.
Shew, Excel is a rabbit hole, wow. Thanks for all the great content.
Question for anyone who doesn't mind helping a rookie.
Best function for example, if Sunshine is buying 25 units @ $17 but I need to add on $X to the price for a special feature that is only applicable sometimes.
I did another table to the side with a data validation list but it looks sloppy I think...
For back and forth dialog to get Excel sutions try this great Excel Question Site: mrexcel.com/forum
Hi Mike, I searched your videos but I couldn't find any videos on Connections.(I am talking about the normal connections found in data tab in excel).If you already made some videos, can you post it or if not, can you make a video? This would be a great help. Please Please.............................
I do not have a video on this topic. I am still six months backed up with videos for my classes, it will be awhile before I can get to a video on this topic.
Thank you Mike, for the reply. No problem. Until then I will be following your other awesome videos :) :)
I can see that the Switch function allows you to return a table after searching the tables. But is there a way to return the full table from a group of tables and then copy a table “x” number of times, filling each table one after the other?
I have no idea how to do that!?!? For back and forth dialog to get Excel sutions try this great Excel Question Site: mrexcel.com/forum
ExcelIsFun thanks Mike. I’ve posted up my question giving an example of what I’m trying to do.
ExcelIsFun Hey Mike, so I was going over some older videos and you did a few things that were all parts of what I need to do. Checkout your Magic Trick 493, where you copied an item “x” times
ExcelIsFun here is a VBA code that copies a cell x number of times. This is what is like to do, but do it for a range instead of a single cell.
ruclips.net/video/xxmUenINcjs/видео.html
No one is better than you
I am glad that these videos help, RUclips Excel Easy in Hindi!!!!
Listen. Please translate your subject with hindi and post it with english subject. You will get more subs and likes .....
Informative... Will try to use switch function now.... However I used it in DAX haha..
Any plans for DAX n M lang mike??
Finally, Coming soon, in the next month I will finally start posting a new series about Power Query, M, DAX and Data Analysis and Business Analysis. You will finally get what you want : ) But I have to tell you that you have been annoying over that last six months in that you always ask, even though you new I had prior contractual agreements that precluded me from getting to this new videos series.
ExcelIsFun Oops... Extremely sorry and regret for any kind of annoying comments... May be I am jus super excited and was just re-confirming if the DAX series isn't delayed, as some other priorities seeds in... Anyways.. Will take care of my comments from here on..
how to create MIS report? give an example
I am sorry, I do not know how to create a MIS report.
ExcelIsFun can you please upload some latest tricks for pivot tables and vlookup?
I have 100s of PivotTable and VLOOKUP tricks.
Here is a video playlist with all the tricks for PivotTable: ruclips.net/p/PLDF4390866B480CD2 (first three videos have everything you need)
Here is a video playlist with all the tricks for VLOOKUP: ruclips.net/p/PLE9C01909A6A5A14B
Please help support my efforts with a Thumbs Up and comment on each video that you watch : )
ExcelIsFun thanks a lot
Like the video, but don't like making a table name same as name of product. When marketing changes name of "Quad" to "4Solution" there is hidden knowledge embedded in table name. Love switch!
Go it! Yes, SWITCH is great!!! Thank you very much fpr your support!!!
That is a nice formula, but it is a greedy policy Microsoft is applying here .... office 365 only!
The function came out after the perpetual version of Office 2016 did. It will be in Office 2019 but why buy that when you get new stuff all the time with an Office 365 subscription? Microsoft is definitely not being greedy with this function. They've made it available in Excel Online, Excel for iPhone, Excel for Android, etc. which are all free.
brianxyz on iPhone and Android? Thousands of records ...?
Yes, Excel 2019 coming out later this year will have it. Office 365 already had it.
Sir Using vlookup and offset formula multiply refrence and multiple table lookup please solution sir
Sir Using vlookup and offset formula multiply refrence and multiple table lookup please solution sir
Sir Using vlookup and offset formula multiply refrence and multiple table lookup please solution sir
Sir Using vlookup and offset formula multiply refrence and multiple table lookup please solution sir
Sir Using vlookup and offset formula multiply refrence and multiple table lookup please solution sir
Sir Using vlookup and offset formula multiply refrence and multiple table lookup please solution sir