Excel XLOOKUP Function - Comprehensive Lessons - 27 Examples - Excel Magic Trick 1600
Рет қаралды 124,691
4 жыл бұрынDownload Excel Start File (to follow along with video): excelisfun.net/files/EMT1600....
Download Excel File with All Examples Completed AFTER video is don: excelisfun.net/files/EMT1600F...
Learn about the new Office 365 XLOOKUP Function. This functions replaces much of what we did with historically earlier functions: VLOOKUP, HLOOKUP, LOOKUP, INDEX and MATCH.
Topics in video:
(00:12) Introduction
1. (02:00) Exact Match is XLOOKUP Default
2. (02:52) 3 arguments for Exact Match, one fewer than VLOOKUP
3. (03:37) Can insert Columns and it still works!!!!!
4. (04:38) Approximate Match using Exact Match , previous smallest, match_mode argument set to -1. See example of LOOKUP function (old function) that may be more efficient than newer XLOOKUP.
5. (07:29) Approximate Match & You Don't Have To Sort!!!!!
6. (07:56) Approximate Match using Exact Match , previous biggest, match_mode argument set to 1
7. (09:03) Find Last Values, Amongst Duplicates. (10:16) Also see XLOOKUP as Spilled Array. Also see SORT & UNIQUE Functions.
8. (11:15) Find First Values, Amongst Duplicates
9. (11:45) Lookup Left. (11:31) Important Lesson about Dimensions of lookup_array and return_array.
10. (12:52) Horizonal or Vertical Lookup, or Both. More important lessons about dimension sizes of lookup_array and return_array.
11. (14:02) Two Way Lookup. First look at “lookup a range”. More important lessons about dimension sizes of lookup_array and return_array. (15:58) See example of VLOOKUP & MATCH functions (old functions) that may be more efficient than newer XLOOKUP.
12. (16:37) Lookup Row
13. (17:24) Lookup Column
14. (17:38) Return Multiple Items.
15. (17:38) Can Retrieve whole record.
16. (18:37) But these methods require a static order and not dependent on Field Names
17. (18:44) Return Multiple Items with Dynamic Columns. Including Spilled Arrays.
18. (18:44) These methods are Dynamic! These methods lookup items based on Field Names.
19. (18:44) Old Methods for returning Records with columns specified in certain Order.
20. (19:16) New Methods for returning Records with columns specified in certain Order.
21. (20:16) INDEX & XMATCH. And (20:57) VLOOKUP and XMATCH. Old and New Together for returning Records with columns specified in certain Order.
22. (21:50) Rearrange Columns
23. (22:16) Lookup Cell References
24. (22:47) Lookup Picture
25. (23:00) Lookup Table. XLOOKUP is not a good method : (
26. (23:40) Wildcards
27. (24:14) Array Formulas
(24:35) Summary
Other Video Topics:
XLOOKUP in Excel is VLOOKUP Slayer, VLOOKUP... Gone But Not Forgotten
XLOOKUP or INDEX-MATCH-MATCH Head-to-Head
Microsoft introduces XLOOKUP in Excel - and it's a big deal
The New XLOOKUP: Compared to VLOOKUP & INDEX,
The New XLOOKUP Function for Excel
VLookup Fired...XLookup Hired - The New Giant Is Here
@cindyc6703 4 жыл бұрын
Recently, I download Xlookup. When I went through your video and copy what you have typed on the screen. I had a problem starting Tab 4-5- Xlookup(lookup,lookup_array, return_array,[if_not_found],[match_mode], [search_mode]) Do you why after the return_array, it show [if_not_found]?? Do you think this is setup issue or something else?
@excelisfun 4 жыл бұрын
No, as Microsoft does so often now, They add that new argument [if_not_found]. Although we have to make a few adjustments for what is in this video, all the amazing new examples in this video still work perfectly : )
@haleymeyer4482 4 жыл бұрын
@@excelisfun Hi I am having the same issue, on 4-5 tab when I enter in =XLOOKUP(E12,B3:B8,E3:E8,-1) it gives me -1 because it thinks I am entering "-1" for the ""[if_not_found]" - what adjustments are you making to have it work perfectly? is there a newer video I am missing? Thank you so much!
@excelisfun 4 жыл бұрын
@@haleymeyer4482 You can skip that argument like: =XLOOKUP(E12,B3:B8,E3:E8,,-1) or you could put something in the argumnet like; =XLOOKUP(E12,B3:B8,E3:E8,'Did Not Find",-1)
@LeilaGharani 4 жыл бұрын
Loved this comprehensive tutorial! Thank you Mike for an XL lecture on XLOOKUP 👍
@excelisfun 4 жыл бұрын
You are welcome for the XL lecture, Teammate Leila!!!
@UM9000 3 жыл бұрын
@@excelisfun Love the tutorials by both of you
@rafiqsheikh4742 2 жыл бұрын
two legend s are here ... I am following bruh of U
@clairejsquibb 4 жыл бұрын
27 examples in 25 minutes - wow, this is going to take some digesting! Great video, as always: so comprehensive and clear.
@excelisfun 4 жыл бұрын
Yes, there is a lot there, a lot of transformative formula stuff! Digesting should be efficient and fun, though. I am glad that it is clear for you, Claire : )
@rrrprogram8667 4 жыл бұрын
Nobody gives better examples than excelisfun
@excelisfun 4 жыл бұрын
Thank you, RRR! I try to have fun and tell a good story : )
@SaniGarba 4 жыл бұрын
Glad to see that you finally got the function released to your insider channel. On a lighter note, Mike, what are these giant techs afraid of. First it was KZfaq giving you some tough time with your playlist videos, them Microsoft delayed releasing these awesome functions to you. The consolation is, the more these incidences occur, the more they raise your profile to the global community of excel where you have the largest following of students and admirers. Continue doing what only you do best, MAKING EXCEL FUN!
@excelisfun 4 жыл бұрын
Thank you for your comment here and at Linkedin : ) Here is my comment from Linkedin: Yes, Sir! I will continue regardless of whether the corporate monopolists bungle and botch things to everyone's detriment. If KZfaq blocks me from making Learning Playlists or Microsoft can't get there marketing correct,, I will keep making and posting fun Excel resources for the world!!! Excel and Data Analysis are just too much fun!!! Thank you for your support, Sani Garba : )
@TwoLargePizzas 4 жыл бұрын
You know, I subscribed to this channel several years ago because I thought I was looking at magic. It is magic. I just never got around to the learning part. but holy crap, my boss would love me if I can do these.
@excelisfun 4 жыл бұрын
Yes, if you want to learn, Two Large Pizzas, excelisfun can help!!! Let me know if you need a good playlist, a particular video or a particular topic. Thanks for your support with your comments and thumbs ups : )
@wayneedmondson1065 4 жыл бұрын
Hi Mike.. as usual.. of all the new XLOOKUP videos I've watched since release, yours gives the most comprehensive and creative lessons for getting going with XLOOKUP, XMATCH and also reminding of the continued relevance of legacy methods that are still useful and in some cases may even be preferred. As always, many thanks for sharing your super-hero skills and talents with all of us mortals out here :)). Got my work cut out for the weekend to study this vid in detail.. haha!! Thumbs up!!
@excelisfun 4 жыл бұрын
Yes, study this video... Sounds like fun. I want to do it also!!!! Thanks for your amazing support, Wayne : )
@simfinso858 4 жыл бұрын
Wayne Edmondson Yes Totally Agreed on Your Comment.
@simoiyahector-morales3781 4 жыл бұрын
Sooooooo glad you finally taught this. Been waiting to see your excellent tutorial. God bless you Mike
@excelisfun 4 жыл бұрын
Glad I finally got the function. Thanks for waiting and watching and supporting, Simoiya : )
@robertovelicaz7719 4 жыл бұрын
Brilliant Mike, so comprehensive as usual. Like you said “An Epic video”. Look forward to further examples 😃
@excelisfun 4 жыл бұрын
You are welcome for the epic, Roberto! More to come : ) : )
@excelisfun 4 жыл бұрын
Topics in video: (00:12) Introduction 1. (02:00) Exact Match is XLOOKUP Default 2. (02:52) 3 arguments for Exact Match, one fewer than VLOOKUP 3. (03:37) Can insert Columns and it still works!!!!! 4. (04:38) Approximate Match using Exact Match , previous smallest, match_mode argument set to -1. See example of LOOKUP function (old function) that may be more efficient than newer XLOOKUP. 5. (07:29) Approximate Match & You Don't Have To Sort!!!!! 6. (07:56) Approximate Match using Exact Match , previous biggest, match_mode argument set to 1 7. (09:03) Find Last Values, Amongst Duplicates. (10:16) Also see XLOOKUP as Spilled Array. Also see SORT & UNIQUE Functions. 8. (11:15) Find First Values, Amongst Duplicates 9. (11:45) Lookup Left. (11:31) Important Lesson about Dimensions of lookup_array and return_array. 10. (12:52) Horizonal or Vertical Lookup, or Both. More important lessons about dimension sizes of lookup_array and return_array. 11. (14:02) Two Way Lookup. First look at “lookup a range”. More important lessons about dimension sizes of lookup_array and return_array. (15:58) See example of VLOOKUP & MATCH functions (old functions) that may be more efficient than newer XLOOKUP. 12. (16:37) Lookup Row 13. (17:24) Lookup Column 14. (17:38) Return Multiple Items. 15. (17:38) Can Retrieve whole record. 16. (18:37) But these methods require a static order and not dependent on Field Names 17. (18:44) Return Multiple Items with Dynamic Columns. Including Spilled Arrays. 18. (18:44) These methods are Dynamic! These methods lookup items based on Field Names. 19. (18:44) Old Methods for returning Records with columns specified in certain Order. 20. (19:16) New Methods for returning Records with columns specified in certain Order. 21. (20:16) INDEX & XMATCH. And (20:57) VLOOKUP and XMATCH. Old and New Together for returning Records with columns specified in certain Order. 22. (21:50) Rearrange Columns 23. (22:16) Lookup Cell References 24. (22:47) Lookup Picture 25. (23:00) Lookup Table. XLOOKUP is not a good method : ( 26. (23:40) Wildcards 27. (24:14) Array Formulas (24:35) Summary
@sebghatulbarykhabaey6680 4 жыл бұрын
Hi Mike! Iam using office 365 But xlookup is not available in my office version?
@excelisfun 4 жыл бұрын
@@sebghatulbarykhabaey6680 Currently it is only in the Insider Edition of Office 365. In Excel you can go to File, then Account and sign up, right inside Excel.
@derekpage8924 4 жыл бұрын
What a brilliant site Mike. I also really appreciate that you allow us to participate on each individual spreadsheet and the added bonus of the completed final version too. Amazing and keep them coming. Thank you.
@excelisfun 4 жыл бұрын
Yes, Derek, it is what I have been doing for 11 years and over 3000 videos. So I am glad you are now here, hanging out and learning Excel the efficient and fun way!!! Thank you for your support, Derek!!!
@derekpage8924 4 жыл бұрын
@@excelisfun Just wish that i would have discovered your site earlier Mike and as I said with the added bonus of all the spreadsheet data, fantastic. Congratulations on the 11 years too. I am truly converted, regards, from the UK, Del.
@excelisfun 4 жыл бұрын
@@derekpage8924 Good and fun Excel Knowledge is good! See you in the comments on future videos and remember to support with those thumbs ups : )
@byDsign 4 жыл бұрын
*I swear fo' GOD you are doing the Lord's work, Mike!* You break this info down like I'm a baby 'chile.... and I love it! No confusion when you're done! I got it! ♥♥♥
@excelisfun 4 жыл бұрын
Yes, that is what I do: I break it down with all the concepts there for us to learn, with the story told with few missing pieces, anticipating questions that viewers may have and then putting them into the story. Why do I do this? Because I am actually not very smart, and if I did not break it down into baby pieces and tell an easy story, I could not understand it. In this video, I think the most important part, that seemed to be missing in other people's videos, was the fact that the lookup_array and return_array have the have the same number of elements as specified by the direction (rows or columns) of the lookup elements in the lookup_array as they are laid out in the spreadsheet. I know that I was confused when I tried to learn XLOOKUP (and the Microsoft Help was significantly insufficient) about when we could spill and not spill values. I was confused because in the old LOOKUP function, the two arrays did NOT have to be the same direction, meaning: the lookup_vector and result_vector could be both horizontal, both vertical or one could be vertical and the other could be horizontal. Anyway, D. Hall, I am glad the video works for you and that you made an important comment about why it works for you : ) I appreciate your support : ) : )
@kjvstats9003 4 жыл бұрын
Mike, apart from describing your EMT1600 video as "fully comprehensive" on XLOOKUP, I prefer to use a much grander word...stupendous! You are still the Master. Great respect for your generous contribution to the world of Excel users. MVP for 2020 must be guaranteed. PS, Greetings from N.Ireland.
@excelisfun 4 жыл бұрын
Thank you for the stupendous comment, Authorized "From N Ireland" KJV Stats!!! I am glad that you liked the masterful XLOOKUP video : ) P.S. NOTHING is guaranteed from Microsoft. Even though I have been promoting MS by posting an average of 300 videos per year for the past 11 years, the MVP is a roll of the dice each year. You never know... But no matter, I will keep posting videos at excelisfun for our Awesome Online Team!!!!
@Soulenergy31 4 жыл бұрын
Your brilliant master samples on application of the XLookup really Stands out! thanks for such master explaination Mike!
@excelisfun 4 жыл бұрын
You are welcome for the master XLOOKUP fun, Saul!!! Thank you for your help supporting what I do.
@billywiswall6233 4 жыл бұрын
Great video! I got my update with the new functions yesterday. So glad you got this out so quickly, thank you!
@excelisfun 4 жыл бұрын
You are welcome, Billy! Thanks for your support : )
@msantosh1220 4 жыл бұрын
Thanx for making this video on Xlookup it a mammoth. As I said learning is at its best with Mike n excelisfun❤
@excelisfun 4 жыл бұрын
You are welcome for the "Mammoth" XLOOKUP Video!!!! Thanks for your cool support, Santosh : )
@mr.brownstone5716 4 жыл бұрын
Hey! Excel finally woke up and gave it to you. Great.
@excelisfun 4 жыл бұрын
I am glad they did, but I am sure that they are still sleeping at the wheel when it comes to marketing. I hope you enjoy the video, Mr. Brownstone : )
@pmsocho 4 жыл бұрын
@@excelisfun They are sleeping. Those new functions are so cool but I believe that the adoption of some of those will be very very slow. They should have been given us 10 years ago...
@excelisfun 4 жыл бұрын
@@pmsocho , Sleeping is a euphemism for "Idiots", "Monopoly Abuse" and more ; )
@mohamedchakroun4973 4 жыл бұрын
Briliant mike this is the only video up to now giving a full uses for xlookup with examples. I recommand to youtube to add an optinal evaluation to videos with star you will get 5 star for each video besides the thumbs up :-)
@excelisfun 4 жыл бұрын
Thanks for the KZfaq recommendation of 5 stars and thumbs ups : ) : ) BTW, there are lots more cool videos to come on this amazing XLOOKUP function!!!
@davidmanhire5532 4 жыл бұрын
Thanks Mike! Can't wait to get this function into action. I'll be watching this fast paced learning session more than once, awesome reference material as always from the indomitable spirit that is Mike Girvin! :D
@excelisfun 4 жыл бұрын
Glad you like this, David!!! Microsoft changed the order of the arguments and added a "Value If NA". Everything in this video is still good, you just need to be aware of the jumbled arguments. I will make a new video when they finally re;ease the XLOOKUP to all of Office 365 : )
@nsanch0181 4 жыл бұрын
Wow Mike! That was a great video. I have been waiting a while to go over this and I am so glad I did. You helped me refresh on a lot of the old lookups; and now I have a good understanding on this new Xlookup. That was a lot. Thank you so much Mike.
@excelisfun 4 жыл бұрын
You are welcome, so much, N Sanch01!!!!
@OzduSoleilDATA 4 жыл бұрын
XLOOKUP, Dynamic Arrays and everything! WOW!
@excelisfun 4 жыл бұрын
I like the "and everything" - because it is so TRUE: Microsoft has given us nearly everything with New Excel Calculation Engine with Array Spill, XLOOKUP, Power Query, Data Model. Relationships!!!! We are lucky to be bloggers about such cool stuff : )
@Prosperity-hk8ub Жыл бұрын
Thank you Mike for the amazing tutorial on XLookup! I'll certainly go back and cosume each sheet over and over...😊👍🙏❤
@excelisfun Жыл бұрын
You are welcome!
@chanman201 3 жыл бұрын
This is the best video on xlookup I've seen. Thanks and I will be referring back to this.
@johnborg6005 4 жыл бұрын
Finally!!! The good thing is that i got it aswell. So here we go :) :) thanks for the video mike.
@excelisfun 4 жыл бұрын
Here we go!!!!! I love that : ) You are welcome as always, John Borg : )
@chrism9037 4 жыл бұрын
This new function is amazing! Thanks Mike!
@excelisfun 4 жыл бұрын
I agree - just so many things all in one. You are welcome, as always, Chris : )
@muhammadelsayed2693 4 жыл бұрын
Great Video, Hard work behind, Thank you Mike!
@excelisfun 4 жыл бұрын
You are welcome for the video and the hard work behind it, Muhammad!!!!
@sasavienne 4 жыл бұрын
I am quite happy 😊 to see that you have Xlookup now. Thanks for the awesome examples. 👍
@excelisfun 4 жыл бұрын
You are welcome for the awesome examples, Salim!!!!!!
@Softwaretrain 4 жыл бұрын
Great function with great explanation. You are amazing. Nobody can teach in this easy and useful way. Thumbs up for you and Microsoft.
@excelisfun 4 жыл бұрын
Thanks for the kind words. And thank you for your support with your comments, thumbs ups and of course that Sub : )
@JoseHernandez-gp9pd 4 жыл бұрын
Good morning Mike. My name is Jose Hernandes=z from Miami, Florida. I was waiting for your tutorial about this new function. You are the best!!!
@excelisfun 4 жыл бұрын
Thank you very much, Sir! Welcome to the comments at excelisfun videos - we always have a lot of fun here : ) Thanks for waiting, Jose Hernandez, and thanks for watching and having fun : )
@excelisfun 4 жыл бұрын
In Florida, Jose Hernandez, what do you use Excel for most of the time?
@ogwalfrancis 4 жыл бұрын
I have been waiting for this Amzing comprehensive lesson, Thanks Mr. Mike
@excelisfun 4 жыл бұрын
Me too. We have been waiting together!!! But here it is now : ) Thanks for your support, Ogwal The Statistician!!!!
@ogwalfrancis 4 жыл бұрын
Always welcome, Thanks a lot.
@excelisfun 4 жыл бұрын
@@ogwalfrancis : ) : )
@johnborg5419 4 жыл бұрын
Amazing scenarios Mike. Thanks for the FUN!!!
@excelisfun 4 жыл бұрын
You are welcome for the fun scenarios, John Borg!!!!!! XLOOKUP Fun : )
@rajas4433 4 жыл бұрын
The most comprehensive tutorial on XLook UP Hats off to you Sir....
@excelisfun 4 жыл бұрын
Thank you for the hats off, Raja S!!!! I am happy that this videos helps you : ) : )
@mmmail1969 4 жыл бұрын
Thanks for this - a superb presentation, going to watch and re-watch this video, over a few days, really take the time to get a handle on this....great stuff, thanks again!
@excelisfun 4 жыл бұрын
You are welcome for the great stuff, mmmail1969!!!! I hope you will enjoy the re-watches : ) Just for fun, leave a comment each time you re-watch with a number like: re-watch 1, re-watch 2 and so on ; )
@mmmail1969 4 жыл бұрын
@@excelisfun lol thanks will do
@excelisfun 4 жыл бұрын
@@mmmail1969 Re-watch and new-comment 2 : ) lol
@alexkim7270 4 жыл бұрын
Amazing, Mike! Thanks. I'm bookmarking this!
@excelisfun 4 жыл бұрын
Yes, sir!!! Book mark it, and come back as many times as you want. That is the beauty of KZfaq : )
@Al-Ahdal 4 жыл бұрын
Wow.... Thank you Mike for another awesome excellent vdo.
@excelisfun 4 жыл бұрын
You are welcome, Hassan! Thanks for your support : )
@trevorbbeairsto 4 жыл бұрын
Amazing work as usual. So, so good at what you do!
@excelisfun 4 жыл бұрын
Thank you, Trevor. I try to have fun and make the story easy to follow with all the details necessary to really understand.
@trevorbbeairsto 4 жыл бұрын
@@excelisfun It's a fantastic teaching philosophy. It sure works for me!
@excelisfun 4 жыл бұрын
@@trevorbbeairsto , Go Team!!!!
@johndurran614 4 жыл бұрын
An excellent comprehensive tutorial. Worth waiting for
@excelisfun 4 жыл бұрын
Glad you liked it and it was worth the wait, John Durran!!!!
@pmsocho 4 жыл бұрын
Thumbs up as always! Great examples!
@excelisfun 4 жыл бұрын
Thank you very much, Teammate pmsocho!!!!
@caroleo5020 Жыл бұрын
Thank you so much! I learned a LOT from your videos and examples.
@excelisfun Жыл бұрын
You are welcome so much!
@alialbayati9368 4 жыл бұрын
Thanks Mike for this tutorial, this is a time saver and a great formula. You are the best.
@excelisfun 4 жыл бұрын
You are welcome for the time saving XLOOKUP video, Ali!!!!
@nicor1501 4 жыл бұрын
Wonderful Mike, thank you so much for sharing your videos. The time line table is just amazing
@excelisfun 4 жыл бұрын
You are welcome so much for the shares, Nico R!!!! P.S. What do you mean "time line table"? Was that something in the video? What is the minute mark?
@nicor1501 4 жыл бұрын
@@excelisfun I meant the table of content of the video, indicating where each of the 27 parts starts
@excelisfun 4 жыл бұрын
@@nicor1501 Yes, Sir!!!!! I have been including those Time Hyperlinked Tables of Contents in every large video I have posted since the year 2012!!!!
@shipingli7590 4 жыл бұрын
Great. Thank you very much, Mike!
@excelisfun 4 жыл бұрын
You are very welcome, Shiping Li!!! Thanks for your support : )
@masterof 4 жыл бұрын
Amazing video, so many good examples, thank you Mike!
@excelisfun 4 жыл бұрын
You are welcome for the so many good examples, rf05mjy!!!!
@timmytesla9655 2 жыл бұрын
Learnt a lot watching this. Thank you!
@ruroshinzynaruto 3 жыл бұрын
Super Video , always amazed by your Tutorials , Thank You :)
@richardhay645 4 жыл бұрын
Great video. Very complete. I've been working with these functions for a couple of days. There are a few situations where VL and IM are competitive. However., my actual uses for them will probably be few and far between! So maybe sometimes, maybe never!!
@excelisfun 4 жыл бұрын
I agree, there are not too many uses where XLOOKUP will not be the best. I will still use LOOKUP for simple Approximate Match for my tax tables and I will never use XLOOKUP for looking up tables, and might use XLOOKUP for some two way lookups... But we will see as we use it for months and months...
@richardhay645 4 жыл бұрын
@@excelisfun Not yet sure about looking up multiple tables. VL with, say, CHOOSE is quite good IF the tables are identical (except for the specific data). However, XL worked very well when the tables varied in number of columns (as in price tables from different manufacturers or products). I tried one scenario where one product price look up required join of ID and color and an in-stock/out-of-stock indicator column and where the ID and color columns were left of price and the stocking indicator was on the right. The other tables did not have this situation. XL (with CHOOSE and nested XL) did the job. And at least for me VL was left scratching its head. (You might have better "luck" creating a work around.) Also, in other situations, there seems to be an "exponential" power to nesting XL's when necessary. I found CHOOSE and IFS work nicely with XL. All in all, I have far more yet to learn about the power of XL, especially when nested and/or when combined with other functions, than what I know about it now!!
@excelisfun 4 жыл бұрын
@@richardhay645 Wow, really cool tests. Yes, I think, Richard Hay, since Excel is nearly infinite, we will never run out of things to learn. That maybe the REAL reason Excel is so much fun : )
@arthurronnie7311 4 жыл бұрын
Thanks Mike for this Great Video. You are such a great teacher!!
@excelisfun 4 жыл бұрын
You are welcome, Arthur!!! Thank you very much for your support with your comment, thumbs up and of course your Sub : )
@thierrybernot9369 4 жыл бұрын
Thanks Mike. Another awsome one ! Today will be a rainy day, just know what to do now ! ;-)
@excelisfun 4 жыл бұрын
That is sooooo awesome: rainy day video for you all about XLOOKUP : ) : ) : ) Glad you like it and thanks for your support, Thierry!!!!
@FredMadera 4 жыл бұрын
XLOOKUP rocks!! As always, great video tutorial.
@excelisfun 4 жыл бұрын
Glad you like it, Frederick!!!
@DougHExcel 4 жыл бұрын
You finally go it :-P Great examples!
@excelisfun 4 жыл бұрын
Thanks, Doug H!!!! It is really, really, terrible of Microsoft not not give it to you and Bill Szysz and me in the first wave!!! They do not care that some of us are on the front lines going to bat for them... What are they thinking? Anyway, we have it now : )
@SyedMuzammilMahasanShahi 4 жыл бұрын
Yeyyyyy!! Mike got the XLOOKUP at last, and man... What an amazing video. EXCELlent.
@excelisfun 4 жыл бұрын
Yeyyyyy! Thanks, Syed : ) XLOOKUP will be lots of fun : ) Do you have this function, yet ?
@SyedMuzammilMahasanShahi 4 жыл бұрын
@@excelisfun naahhh. I don't have Office 365 Insider.
@excelisfun 4 жыл бұрын
@@SyedMuzammilMahasanShahi Oooo.. That's right... I am sorry : (
@SyedMuzammilMahasanShahi 4 жыл бұрын
@@excelisfun I'll try to get one thou :P
@excelisfun 4 жыл бұрын
@@SyedMuzammilMahasanShahi : ) : ) : )
@amit12000 4 жыл бұрын
Great video which I eagerly waiting since week
@excelisfun 4 жыл бұрын
Me too! I had to wait a week to get the function. But here it is in this video: lots of fun and lots of examples! Thanks for your support, Amit : )
@edge5817 4 жыл бұрын
Can't wit to have this... hopefully this will be available soooooooOOON! Thanks Mike :)
@excelisfun 4 жыл бұрын
I hope that you will get it soon, also, Edmundo!!!! In the written article that Microsoft posted, they say everyone should have this in a few months. But we have heard that before... But we can hope : ) : )
@edge5817 4 жыл бұрын
Thanks Mike for the info... very much appreciated!
@excelisfun 4 жыл бұрын
@@edge5817 : )
@robertrobo7611 4 жыл бұрын
this is probably the best video on xlookup.
@excelisfun 4 жыл бұрын
Glad you like it, robert!!!
@judasiscariot7049 4 жыл бұрын
Thank you for the tutorial MIke. Can't wait for the weekend to arrive so I could download the workbook and get at it! :D
@excelisfun 4 жыл бұрын
Glad you like it! I can't wait for the weekend either : )
@excelisfun 4 жыл бұрын
Thanks for your support, Judas, on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
@JanBolhuis 4 жыл бұрын
Thank you so much for this amazing video. I don't understand why it isn't (and other new functions) available for everyone, The good part is that I'm prepared when it is.
@excelisfun 4 жыл бұрын
You are welcome, Jan! MS Marketing is not too good... But Excel is so amazing, I guess we have to live with it...
@HusseinKorish 4 жыл бұрын
What a valuable lesson ..... thanks Mike
@excelisfun 4 жыл бұрын
You are welcome, Hussein : ) Value is good!!!!!
@chiguy_ 4 жыл бұрын
this is awesome.. I really could have used this in my last position. definitely want to play around with this as soon as it becomes standard for everyone.
@excelisfun 4 жыл бұрын
Microsoft posted that it should be a few months until all Office 365 has it, but they have said that before... I hope we will all have it soon, Chi Guy!!!!
@chiguy_ 4 жыл бұрын
awesome.. btw.. I tend to go through your content to get a lot of tips and tricks loved how you always say and show what you are doing makes it SUPER easy to watch and understand. keep it up cheers. :)
@excelisfun 4 жыл бұрын
@@chiguy_ You are welcome for the carefully made stories about fun with Excel!!! Thanks for your support on each video that you have a super easy time with : )
@GeertDelmulle 4 жыл бұрын
This is the ultimate xlookup intro and comparison video! Super Great Job!... I’ll come back in a year or so when I finally get it in my semi-annual update cycle O365 installation. :-(
@excelisfun 4 жыл бұрын
I am so sorry, Geert : ( : ( : ( .... XLOOKUP and Dynamic Arrays are very useful... But, I guess, on the bright side, in a year or so when all of us Excel users on the planet get Office 365, we will all be singing in unison. But in the meantime, thank you for stopping by and leaving a comment... wait... in the background I can here all your co-workers and bosses saying such great things about your Excel BI skills ; )
@GeertDelmulle 4 жыл бұрын
It’s not O365 that’s the issue (we all have it) it’s the huge difference between the insider edition and the semi-annual update cycle-version. I recently tried to make a bet with Mr. Excel who said we’ll all have dynamic arrays by November this year. My bet was that we wouldn’t: clearly specifying the semi-annual update cycle. He responded that ‘he already had lost that bet’ because semi-annual doesn’t get an update before 20.03 (or is it feb.?) BTW: thanks for the compliments. The business side of the organization agrees with you, IT -who should support the business- sees it as competition and is not amused. When I had the ear of the board some time ago, I expressed my vision on Big Data: “from insurmountable obstacle to business as usual where anybody can work with it with ease thanks to the Power Tools. At NO extra cost! You can’t beat that.” That one sheet with your references was being projected as I made that statement. The only thing they need is a learning curve. And right here we have the perfect place to find that learning... some have promised to take or are taking right now their first steps on that learning curve. I went before them... :-)
@excelisfun 4 жыл бұрын
@@GeertDelmulle Not Amused!?!?!?! IT works for the entity, the entity benefits from Awesome Greet Dashboards, therefore, IT MUST love what you do. Period : ) At least the Board knows of your awesomeness : )
@GeertDelmulle 4 жыл бұрын
Thanks for your moral support, Mike. Don’t worry, I’ll keep spreading the good word on the Power Tools and Excel in general. (And yes, MS: releasing the new calc engine would help a lot with that. :-)
@excelisfun 4 жыл бұрын
@@GeertDelmulle : ) : ) : ) for releasing the new engine!!!!! Soon... I hope : )
@yulinliu850 4 жыл бұрын
Magnificent video! Thanks!!!
@excelisfun 4 жыл бұрын
You are magnificently welcome, Yulin Liu!!! Thanks for your support : )
@jbthepea 4 жыл бұрын
Thanks so much Mike. Thanks again for all your labor.
@pilotgundul 4 жыл бұрын
I finally able to use this formula. thanks for the detailed explanation plus the excel file :)
@excelisfun 4 жыл бұрын
You are welcome, Julis : )
@BillSzysz1 4 жыл бұрын
Thanks Mike for comprehensive guide to the new function :-))
@excelisfun 4 жыл бұрын
You are welcome, Teammate Bill "PQ Poet" Szysz!!!! As you know, there are many, many other ways we can use this and how it will change what we do : ) More videos coming soon...
@ennykraft 3 жыл бұрын
I'll teach a class on Wednesday about XLOOKUP and was going through this video in order to see if I had forgotten any topics. It's an awesome video that's covering everything from the basics to advanced. Thank you very much! I don't know if anyone has suggested this yet but my solution for problem #25 would be converting the 3 lookup ranges to tables, giving each the name of the product. And than the rate can be found with =XLOOKUP(B8;INDIRECT(A8&"[Units Sold]");INDIRECT(A8&"[Commission Rate]");;-1) XLOOKUP is such a versatile function!
@excelisfun 3 жыл бұрын
Yes, thanks. Crazy Microsoft released beta version, I made this video and then they added the [if_not_found] argument in the middle!?!? I have more recent videos that deal with this. But this video is more epic than them.
@jeanmatadi-cdle145 2 жыл бұрын
Comprehensive yet concise 👌
@excelisfun 2 жыл бұрын
Glad you like it!!!!!!
@MichaelVB226 3 жыл бұрын
I don't know if you'll see this, but I had to review the XLOOKUP function again and I love all your videos. On Worksheet 11 - two way lookup I believe the -1 is the condition if not found. We can leave that blank and just close the formula, correct? Thank you for all your wonderful videos. I love them all!! You make all the complex problems seem easy. I hope I can get to that level someday!
@Luciano_mp 4 жыл бұрын
Absolutely Amazing. Thank you Mike...👍👍👍
@excelisfun 4 жыл бұрын
You are absolutely welcome, Luciano : ) : )
@lyndlnavarrete3869 10 ай бұрын
Amazing, thank you!
@sachinrv1 4 жыл бұрын
Wonderful video Mike; Lookup and Vlookup have their grandson XLOOKUP
@excelisfun 4 жыл бұрын
Love your words, Sachin!!!!!! LOOKUP and VLOOKUP have a grandson : ) : ) Here he is: XLOOKUP!!!
@sachinrv1 4 жыл бұрын
@@excelisfun i hv one query i hv say 11,12,13 written in cells and want to have sum of only last digit; can my array friend help me out in this. for eg in this case the result has to be 6
@excelisfun 4 жыл бұрын
@@sachinrv1 That one is an easy one... Here it is: =SUM(RIGHT(Numbers)+0)
@sachinrv1 4 жыл бұрын
@@excelisfun got that right on.. again our friend Array... Plz continue with your EE, "Excel Exploration", Cheers from INDIA :)
@excelisfun 4 жыл бұрын
@@sachinrv1 Go Team!!!!!!!
@anil11996 4 жыл бұрын
Ultimate function with your presentation
@excelisfun 4 жыл бұрын
Thank you, Anil! I hope it will change our Excel lives for the better : ) : )
@douglaszulu6281 4 жыл бұрын
Great video Mike. I havent got the new calculation engine yet and an missing out on the fun. Cant wait to pay for 365
@excelisfun 4 жыл бұрын
I am sorry that you do not have office 365 yet. I really think that all serious Excel users will have to get Office 365 because the added money we give Microsoft for a subscription based Excel, is soooooooooooo worth it. The added benefits significantly and hugely outweigh the additional costs. I can't wait for you to get it, Douglas!!!
@twright198 3 жыл бұрын
Michael, this video is excellent, as all of your videos are. Number 22, summing cells (sales) dynamically between two dates, is incredible. Is there a way to add conditional formatting so that it highlights the cells - only - the cells between the two dates that are being calculated? This is not an entire row calculation, just the cell range between the two dates within the row. Thanks.
@wmfexcel 4 жыл бұрын
wow. Thanks for such a comprehensive video for this exciting new functions. I didn't expect it works for Picture lookup. Really have to try that. :) With no doubt, XLOOKUP is super. However, I believe VLOOKUP will be here for majority of Excel users for quite a while (maybe years), simply because not many organisations are using Excel 365. MS needs to be harder not only for building awesome tools, but also promoting to corporations! :P
@excelisfun 4 жыл бұрын
You are right, VLOOKUP will be around for a long time. It takes a while for peiople to change their ways. But just like the PivotTable (which took about 10 years) and Power Query and Power Pivot (still slowly being adopted), eventually, the XLOOKUP will be accepted and used often. I think the bigger block will be on Dynamic Arrays because they are so fundamentally different than how we have made formulas for past 40 years!!!! But luckily, I will have an Office 365 Excel Class posted in the coming year that will show all the new methods of doing the things we have been doing for decades... But I can't start that until Microsoft rel;eases the new Excel Calculations Engine to all of Office 365.
@helent3217 4 жыл бұрын
OMG! What are U doing? Excellent! Bravo.
@excelisfun 4 жыл бұрын
Glad you like it, Elena!!!!
@maseehahmad947 4 жыл бұрын
Woah... That's amazing video sir... U always killed it!!!
@excelisfun 4 жыл бұрын
You are welcome, Maseeh!!!! Glad it was amazing and killed it : )
@jayeshsaroj3944 3 жыл бұрын
I have noticed you have increased your speed of explaining... U are simply awsm when it comes to mechanics of excel..
@excelisfun 3 жыл бұрын
I just hope I can help you with the videos .
@jayeshsaroj3944 3 жыл бұрын
@@excelisfun absolutely u did already.... In my Office people started to take me seriously when it comes to excel... its all coz of u sir. Its a video so i can watch many times if i don’t understand any thing... 🙏
@ExcelExciting 4 жыл бұрын
Great Video Mate !!. SUPER LIKE !!
@excelisfun 4 жыл бұрын
Thank you very much for the SUPER LIKE!!!!!! P.S. Where is that SUPER LIKE button/, where is it below the video/, I can't find it !?!?!
@ExcelExciting 4 жыл бұрын
@@excelisfun SUPER LIKE is only for my special Mate :)
@ahmedali-oi9wv 4 жыл бұрын
many thanks , really you are such a good hand Mr. mark
@excelisfun 4 жыл бұрын
Many You Are Welcomes, ahmed!!! Thanks for the good hand comment, but my name is Mike ; )
@excelmaster1328 4 ай бұрын
Thanks mike for amazing tutorial❤️
@excelisfun 4 ай бұрын
You are welcome!!!
@hazemali382 3 жыл бұрын
always great Mr.Girvin ;)
@excelisfun 3 жыл бұрын
: )
@abdulrahmanjosephsesay8507 4 жыл бұрын
Thanks Mike. God bless you.
@excelisfun 4 жыл бұрын
You are welcome, Abdulrahman!!!! Thanks for your support : )
@mohamedsaid7514 4 жыл бұрын
Sincerely greet you. You are truly the owner of my favor. I learned a lot from you. Sincerely, respect and thanks a lot for you.
@excelisfun 4 жыл бұрын
You are welcome, Mohamed!!!
@mohamedsaid7514 4 жыл бұрын
I have a question, please I have a worksheet with a set of data, and when some of that data is filtered by a filter formula, the number of that data is 44 rows, so is there a way to make a filter formula for the first 22 rows and in the adjacent column the filter formula starts from 23 to 44
@RajeevKumar_SQH 4 жыл бұрын
Awesome explaination sir.. Thank you
@Jennifer98001 4 жыл бұрын
I love your 📹 😊 do you have a playlist with just office 365 excel new tricks and features?
@markpodesta4605 4 жыл бұрын
Thank you. Will be good fun.
@excelisfun 4 жыл бұрын
You are welcome, Mark!!!! I agree: it will be good (or more) fun ; )
@moesadr3342 Жыл бұрын
Thanks for all amazing training videos, short, sweet and right to the point but very through! Would you kindly let me know how I can use look up to search for the number of rows with a field includes certain value for This and/ or Last Week, This and/ or Last Month, This and/ or Last Quarter and This and/ or Last Year? How would the formula could look? Thank you!
@jvlm3091 3 жыл бұрын
Great explanations!
@excelisfun 3 жыл бұрын
Glad it helps : )
@deepakmirchandani1348 4 жыл бұрын
great sir, xlookup best explained only in your video.thanks
@excelisfun 4 жыл бұрын
You are welcome, Deepak!! I am glad that the videos I post help you!
@gunalan4949 Жыл бұрын
Very good. Thank you very much.
@excelisfun Жыл бұрын
You are welcome!
@aorsh123 4 жыл бұрын
Big Respect, Thank you so much!
@excelisfun 4 жыл бұрын
You are welcome so much, Leung!!!!
@MalinaC 4 жыл бұрын
EXCELlent video, EXCELlent function!
@excelisfun 4 жыл бұрын
Thanks, Teammate : )
@kss2066 2 жыл бұрын
epic & awesome!
@nayak-theleaderking5694 2 жыл бұрын
How fast you do sir, wow super duper master in excel... well done.
@excelisfun 2 жыл бұрын
Glad you like it, Nayak!!!!
@mohan8887 3 жыл бұрын
Such great trick
@dougmphilly 4 жыл бұрын
that whole unique, sort thing was sick.
@excelisfun 4 жыл бұрын
I agree : )
@mohan8887 3 жыл бұрын
Awesome trick
@man-nongjong3497 3 жыл бұрын
Thanks Mike!
@excelisfun 3 жыл бұрын
You are welcome, Gwapomg!!!
@mohankyatham Жыл бұрын
excellent video.
@excelisfun Жыл бұрын
Glad this helps : )
@zikaekenta7352 4 жыл бұрын
Haha. I knew the grandmaster would have a video soon enough. When will it be available for the general public?
@excelisfun 4 жыл бұрын
You are welcome for the grandmaster production, zika ekenta!!! Do you mean grandmaster like the famous grandmaster from the 1970s funk music scene (kzfaq.info/get/bejne/htWSpbahst2vm2g.html)? If so, that is quite a huge compliment : ) Thanks. In the written article that Microsoft posted, they say everyone should have this in a few months. But we have heard that before... But we can hope : ) : )
@zikaekenta7352 4 жыл бұрын
@@excelisfun Indeed that song is a timeless classic, much like your excel videos! Thanks for your unceasing efforts on youtube.
@excelisfun 4 жыл бұрын
@@zikaekenta7352 Unceasing - that is a perfect word for what I have been doing for over 11 years ; ) ... Unceasing is what we must do as humans to try and make the world a better place!
@excelisfun 4 жыл бұрын
@@zikaekenta7352 I can hear the timeless classic now... : ) It makes me wonder, how I keep from going under... without XLOOKUP!!!!!
@romanguz 4 жыл бұрын
Amazing video especially that XLOOKUP is now officially available to all Office 365 users :))
@dougmphilly 4 жыл бұрын
timing couldn't be better.
excelisfun
Рет қаралды 6 М.
All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX - 28 Examples! - 365 MECS 08
excelisfun
Рет қаралды 40 М.
MyOnlineTrainingHub
Рет қаралды 296 М.
excelisfun
Рет қаралды 83 М.
excelisfun
Рет қаралды 112 М.
excelisfun
Рет қаралды 117 М.