Lookup values across multiple worksheets: VLOOKUP / INDEX MATCH in Excel
Вставка
- Опубліковано 6 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Learn how to use Excel for looking up values across multiple worksheets. Ideal for users of all levels, this video guides you through different methods, including VLOOKUP and INDEX & MATCH, to handle complex data sets efficiently.
⬇️ Download the workbook here: pages.xelplus.com/lookup-diff...
👩💻 What You'll Discover:
▪️ Dynamic Lookup Techniques: Learn how to retrieve data dynamically from multiple tabs using VLOOKUP and INDEX & MATCH.
▪️ Practical Scenarios: Understand the process through practical examples, like summarizing invoiced amounts from different divisions.
▪️ Table Conversion and Use: See how converting data into Excel tables can simplify your lookup processes.
▪️ Indirect Function for Flexibility: Explore the power of the INDIRECT function for creating dynamic references in your formulas.
In this Excel tutorial I show you how to lookup values from multiple Excel Sheets or tabs. In Excel it's relatively easy to lookup values in one specific tab. But how do you lookup values in multiple tabs using just one single formula?
00:00 Lookup Values in Multiple Worksheets in Excel
02:15 VLOOKUP Across Multiple Worksheets
07:43 INDEX & MATCH Across Multiple Worksheets
In the video, I show you different versions of getting this done. One version uses VLOOKUP and direct worksheet and cell references. The other version uses INDEX & MATCH and Excel table names and references. The key here is the INDIRECT function which acts as the messenger which returns the correct address to the different lookup formulas.
Of course, you can apply this (indirect) method inside other formulas.
Links to related videos:
VLOOKUP explained ► • Excel VLOOKUP: Basics ...
INDEX MATCH Basics ► • How to use Excel Index...
INDIRECT explained ► • Excel INDIRECT Functio...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/lookup-different-sheets
I feel like a kid watching Wayne Gretzky play ice hockey. This level of Excel knowledge borders on witchcraft. Really impressive, Leila!
You probably dont give a damn but does anybody know a method to log back into an instagram account??
I was dumb forgot my login password. I appreciate any tricks you can give me
@Arturo Steven instablaster :)
@London Sonny Thanks so much for your reply. I found the site thru google and I'm in the hacking process atm.
Looks like it's gonna take quite some time so I will get back to you later with my results.
@London Sonny it did the trick and I actually got access to my account again. Im so happy:D
Thank you so much you saved my ass!
@Arturo Steven you are welcome :D
Great video : ) Such a useful set of tricks!
Thank you Mike : )
I totally agree mike... this was a good one with vlookup and indirect.... very useful. Thanks @leila
You're welcome Mammen. Thank you for the support.
ExcelIsFun .Hi mam I jeet from india, mam your teaching method is very easy and helpful for us I watch your all videos
Saw this video a couple days ago and used it a today!
Absolutely outstanding Lelia, great succinct explanation to sometime i've been trying to resolve for a looooooooooong time. God bless you and your future Excel adventures.
Thank you, Leila!
That's the most useful explanation that I've ever found! You're doing a great job!
Thanks Anna! Glad you like it :)
So much clarity in your presentation. Thank you Leila.I have been looking for something like this for a long time.
You helped me solve an excel problem that I've been stuck on for 3 days. Thank youuu!! Subscribed.
FINALLY!!! Someone who can explain things in a clear way and make it look easy!! I've subscribed because of your entire series on INDEX MATCH & VLOOKUP...!!!! Thanks for these videos, even if it's 3 years old!!!
Awesome, thank you Stéphane!
Leila you are one of the best ever.im from Gabon very passionated with your excel classes.thanks a lot for what your help to humanity
Hi Leila, Awesome and your teaching and explaining is excellent and easy to understand.
I use Office 2019 and for some reason the VLOOKUP didn't work for me but the INDEX-MATCH did work by converting to Table.
Thank you very and it was enjoyable to follow and learn from your tutorials
Leila - the session is incredibly supportive. Thank you for that one! :) I appreciate it.
Thank you! This video helped me solve a problem that had been driving me nuts!
Thank you very much Leila...You will be remembered😌😌
Leila You are always inspiring me, thank you so much for your detailed explanation.
You've always sounded good, but this was your best! big fan of your sweet voice.😊
I've watched it several times and still confused. Guess I've got to back up and learn the basics of VLOOKUP and INDIRECT before trying to tackle this one. Thought I knew Excel until I found this series. She is the MASTER of Excel, thats for sure. I'm impressed with her knowledge.
Thanks. Indirect reference for sheet names was very helpful.
This video was exactly the solution I needed the first time I came across it a couple years ago! Now, I'm coming back to it for another project and it's just as useful now as it was then. :D Thank you so much!
You're very welcome Cassandra!
have re-winded the video couple of times to fully understand the concept..but every second worth it. thanks
Exactly what i need now, and I remember watching this here~
Thanks a lot, Leila!
Glad this came in handy :)
You have helped me countless times! Thank you so much for all your videos.
Geat video madam u just opened my eyes by showing how intelligently these formula can be applies and result brought out of them
really a very great effort by u
😇😇😇😇😇😇
Thank you so much for the explanation, finally got it working with your video and example file, the brackets and other tiny details are killing me.
Wow what a brilliant concept. I loved it.
Leila, you are amazing. Thank you!
I agree!
thanks for your video love the content . it is very understandable also for someone from Belgium
Thank you Leila! I really like the way you use functions most out of it...😃
You're welcome Santosh.
This saved me a ton of time in which I have to summarize a dozen sheets!
Thank you so much Prof!
You're very welcome!
Perfect! you are the best!
Thank you Leila !
You provide us a very useful way to extract data from multiple worksheets.
You're welcome Chaminda.
I don’t know which thing got me in love the most, you or you’re lessons...I would say both haha! Such a beautiful woman😍
Thank you! 😃
This is sooo helpful, thanks for these tips! The INDEX(INDIRECT really helped me!
This just ended a massive headache for me. Thank you! I'm sure I'll return to your channel in the future. Keep up the good work!
I hope you will Andrew. Glad the video was helpful.
I was working on a workbook and your tutorial came as a godsend, thank you so much.
However, your alternative "Lookup" formula in your file didn't work in my workbook. Anyway, i used "Vlookup" one. Thank you again.
OMG! Thank you for the resources!!!
Saved me a lot of time that otherwise would have wasted on making nested if formulas for 10 worksheets. Thanks a lot. Your channel is really the best one out there.
Wow, thank you very much!
great... its very easy to understand the way you are explaining complex things in simple way..
Thank you, have helped me consolidated data from multiple sheet. Gratitude.
Nice job Leila done by You, Good explanation skill, fully excellence
Great! Perfect Video. Thanks!
Leila you are really a natural talent kid. love from Bangladesh.
This tutorial was very much needed! Thanks Leila :-)
Glad to hear that Alex :)
your video is so helpful for me, thanks
Awesome explanations .....of each formula....I love the way you describe the formulas...👌
Thank you Manisha. I'm glad you find the explanation easy to follow.
I am blown away Excel has a lot to offer!
Brilliant.. THANKS.
Such a powerful Indirect feature and even greater your explanation
It was a difficult one to explain. I'm glad you find it easy to follow :)
One of the best tutorial I have ever seen. Your videos are loaded with learning
Wow, thanks!
excellent and crystal clear explanation, many thanks for sharing.
Everytime I hear this girl say "Today, I am gonna show you..." I get the shivers!
😂😂
My god what a great command on Excel function, first time I ever seen on UA-cam videos like your video. So good thanks a lot.
Thank you Anubhav! I'm very happy to hear you like the video and find the content useful.
Thanks Mam, again one more useful, beneficial and helpful formula for the Excel users/lovers/addicts. One more time great, great big salute to your brain and feet, for providing and publishing this type of great videos, and making us jealous about you in your Excel skills.
We are very very lucky to get the videos at timely intervals. Waiting eagerly to watch one more video. Praying the god to get an opportunity to see you in person and to learn more and more skills for the benefit and welfare of the community. A Royal Salute.
You're very welcome Gopala and thank you very much for the kind words. I am very glad you find the videos beneficial.
Great Video. Thank you so much.
Outstanding explanation. Thankyou very much it really solved my problems
This is amazing. I couldn't get the formulae right at first but the fact you then supply the example worksheet means that I could easily correct it. Thank you for covering such an essential topic!
Hi Leila, this excel trick is spectacular. I have never seen this trick on any channel it's worth watching. Please upload videos based on Excel, PPT, Docx & Access. If you are not planning to do it anytime soon than please tell me such that I can start looking for other alternatives.
thank you very much Leila for sharing your skills.
So much clarity the way you teach.....truly appreciate it.
Thank you Ankita! Glad you like the videos :)
Thank you Ankita! Glad you like the videos :)
Thanks Leila. Well done.
Your approach to build up functions is excellent, good job👍🏻👍🏻
Thanks! 😃
You are excellent and genius , Nice tricks
Had to watch this a few times :-)
I just would like to say thank you. I’ve learnt a lot from your channel.
Happy to hear that!
Hi Leila hope you are doing well, your tutorials are always good and very easy to understand. Appreciate if you make Excel tutorial for linking Excel with databases like Oracle.
Power Query , or are you looking an an ODBC link ??
Wow, Thanks for the video and EXCEL is very easy to learn from your videos, I would love to 😊 meet you in person, God bless you & be safe in this COVID_19 crisis.
this helped me a lot... thx so much! 😘
I've been trying to work out how to do this for my assessment for the past couple of hours. Watched 4 minutes of your video and I've nailed it.
Glad to hear it was helpful!
Hello. Thank you so much.:)
Thanks a lot Leila
Thank you for great video (as always!) :)
You're welcome Malina and thanks for stopping by :)
Great video! Just curious, Is there a way to reference separate tabs with cell content?
Thanks...a lot ...
It helps me much
THANK YOU LEILA!!!
Great Video, I was searching for a formula like this for many days. Thanks!
Glad it was helpful!
Absolutely perfect thank u so much Leila i really appreciate
You're very welcome Ismail.
THANK YOU!
Speechless... Incredible skills.
Thank you so much 😀
Very very thanks
thanks. very useful. again thanks a lot
this was a lifesaver. Thank you!
Glad it helped!
Love this!
Awesome explanations,
Question? if we have more than two columns in each work sheet and want to look all the values at the same time.
Realy enjoying your classes.
I'm glad you like the videos.
Great! thank you!
U Explained this kind of topic in very lucid way....!!! Amazing 😊
thankuu very very much... 😘
You're very welcome. Glad you like the video.
currently pulling my hair out trying to figure out a solution. But your videos make my life much easier. Thank you.
Your teaching method, tone, information, everything is perfect.
Please will you like to teach about correlation and regression and some other things of data analysis?
Thanks Fahad. Glad you like the videos. Anything specific in Excel you're looking for regarding correlations with scatter plot?
Thanks Master!
Thank you!
You're very welcome.
Hi Leila, excellent Video, you made my day 🙂
Great Video .. thanks
Excellent trick. Thank you very much Leila.
My pleasure. Glad you like it.
I really appreciate mam its helps me every time with your videos and I really improved my skills in excel
Thank you Syed for your support. Very happy to hear that :)
Thank you very much,Leila.
You're very welcome.
Very useful 👍 tkx
Hello Leila, nice video! g reat sharing... learned a lot from this
amazing Technic This tutorial was very much needed thank you madam
Thank you Praveen. Agree - a few people had asked this question and finally I got around to filming it....
Great video Leila , honestly you explained greattt!!! my very big problem is solvedddddddddd............... thanks a lotttttt to you.
That's great to hear, Saffi!
hello Leila, thank you so much for your videos I learn more with each one of them!
I have a question for anyone who comes across this and can help :), so the first part of the quetsion is: what if we have dependant drop down lists (Thank you Leila for those two videos btw) and want to extract information from the table linked to those two drop down lists?
the second part of the question is: how can we extract ifnormation from a table on the same sheet but from different arrays, considering the informations, that we want to retrieve, as the divisions used in the video above?
thank you
Great solution Leila. Does it also work when vlookup-ing between different sheets in different workbooks with different file directory address?
Hi Leila,
This was a really slick video. When you first started it I thought you were going the 3D route but this you solutions are really cool! Thank you for the video.
Michael
Thank you Michael :)
Thanks for the video for clear information on vlookup
You're very welcome.