Excel XLOOKUP Function - Comprehensive Lessons - 27 Examples - Excel Magic Trick 1600
Вставка
- Опубліковано 5 вер 2019
- 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
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?
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 : )
@@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!
@@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)
Loved this comprehensive tutorial! Thank you Mike for an XL lecture on XLOOKUP 👍
You are welcome for the XL lecture, Teammate Leila!!!
@@excelisfun Love the tutorials by both of you
two legend s are here ... I am following bruh of U
Nobody gives better examples than excelisfun
Thank you, RRR! I try to have fun and tell a good story : )
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
Hi Mike! Iam using office 365
But xlookup is not available in my office version?
@@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.
27 examples in 25 minutes - wow, this is going to take some digesting! Great video, as always: so comprehensive and clear.
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 : )
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.
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 : )
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 UA-cam 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!
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 UA-cam 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 : )
Sooooooo glad you finally taught this. Been waiting to see your excellent tutorial. God bless you Mike
Glad I finally got the function. Thanks for waiting and watching and supporting, Simoiya : )
Brilliant Mike, so comprehensive as usual. Like you said “An Epic video”. Look forward to further examples 😃
You are welcome for the epic, Roberto! More to come : ) : )
Your brilliant master samples on application of the XLookup really Stands out! thanks for such master explaination Mike!
You are welcome for the master XLOOKUP fun, Saul!!! Thank you for your help supporting what I do.
Great video! I got my update with the new functions yesterday. So glad you got this out so quickly, thank you!
You are welcome, Billy! Thanks for your support : )
This is the best video on xlookup I've seen. Thanks and I will be referring back to this.
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.
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!!!!
This new function is amazing! Thanks Mike!
I agree - just so many things all in one. You are welcome, as always, Chris : )
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.
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!!!
@@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.
@@derekpage8924 Good and fun Excel Knowledge is good! See you in the comments on future videos and remember to support with those thumbs ups : )
Thanx for making this video on Xlookup it a mammoth. As I said learning is at its best with Mike n excelisfun❤
You are welcome for the "Mammoth" XLOOKUP Video!!!! Thanks for your cool support, Santosh : )
Thank you Mike for the amazing tutorial on XLookup! I'll certainly go back and cosume each sheet over and over...😊👍🙏❤
You are welcome!
*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! ♥♥♥
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 : ) : )
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
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 : )
The most comprehensive tutorial on XLook UP
Hats off to you Sir....
Thank you for the hats off, Raja S!!!! I am happy that this videos helps you : ) : )
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!!
Yes, study this video... Sounds like fun. I want to do it also!!!! Thanks for your amazing support, Wayne : )
Wayne Edmondson Yes Totally Agreed on Your Comment.
Hey! Excel finally woke up and gave it to you. Great.
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 : )
@@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...
@@pmsocho , Sleeping is a euphemism for "Idiots", "Monopoly Abuse" and more ; )
Amazing scenarios Mike. Thanks for the FUN!!!
You are welcome for the fun scenarios, John Borg!!!!!! XLOOKUP Fun : )
Great function with great explanation. You are amazing. Nobody can teach in this easy and useful way.
Thumbs up for you and Microsoft.
Thanks for the kind words. And thank you for your support with your comments, thumbs ups and of course that Sub : )
Learnt a lot watching this. Thank you!
I am quite happy 😊 to see that you have Xlookup now. Thanks for the awesome examples. 👍
You are welcome for the awesome examples, Salim!!!!!!
XLOOKUP, Dynamic Arrays and everything! WOW!
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 : )
Thumbs up as always! Great examples!
Thank you very much, Teammate pmsocho!!!!
Great Video, Hard work behind, Thank you Mike!
You are welcome for the video and the hard work behind it, Muhammad!!!!
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.
You are welcome, so much, N Sanch01!!!!
Thank you so much! I learned a LOT from your videos and examples.
You are welcome so much!
Amazing, Mike! Thanks. I'm bookmarking this!
Yes, sir!!! Book mark it, and come back as many times as you want. That is the beauty of UA-cam : )
this is probably the best video on xlookup.
Glad you like it, robert!!!
An excellent comprehensive tutorial. Worth waiting for
Glad you liked it and it was worth the wait, John Durran!!!!
Finally!!! The good thing is that i got it aswell. So here we go :) :) thanks for the video mike.
Here we go!!!!! I love that : ) You are welcome as always, John Borg : )
Thanks Mike for this tutorial, this is a time saver and a great formula. You are the best.
You are welcome for the time saving XLOOKUP video, Ali!!!!
Thanks so much Mike. Thanks again for all your labor.
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 :-)
Thanks for the UA-cam recommendation of 5 stars and thumbs ups : ) : ) BTW, there are lots more cool videos to come on this amazing XLOOKUP function!!!
Amazing video, so many good examples, thank you Mike!
You are welcome for the so many good examples, rf05mjy!!!!
Thanks Mike for this Great Video. You are such a great teacher!!
You are welcome, Arthur!!! Thank you very much for your support with your comment, thumbs up and of course your Sub : )
XLOOKUP rocks!! As always, great video tutorial.
Glad you like it, Frederick!!!
Super Video , always amazed by your Tutorials , Thank You :)
Magnificent video! Thanks!!!
You are magnificently welcome, Yulin Liu!!! Thanks for your support : )
Wow.... Thank you Mike for another awesome excellent vdo.
You are welcome, Hassan! Thanks for your support : )
Comprehensive yet concise 👌
Glad you like it!!!!!!
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.
You are welcome, Jan! MS Marketing is not too good... But Excel is so amazing, I guess we have to live with it...
Great. Thank you very much, Mike!
You are very welcome, Shiping Li!!! Thanks for your support : )
I have been waiting for this Amzing comprehensive lesson, Thanks Mr. Mike
Me too. We have been waiting together!!! But here it is now : ) Thanks for your support, Ogwal The Statistician!!!!
Always welcome, Thanks a lot.
@@ogwalfrancis : ) : )
Absolutely Amazing. Thank you Mike...👍👍👍
You are absolutely welcome, Luciano : ) : )
What a valuable lesson ..... thanks Mike
You are welcome, Hussein : ) Value is good!!!!!
Amazing work as usual. So, so good at what you do!
Thank you, Trevor. I try to have fun and make the story easy to follow with all the details necessary to really understand.
@@excelisfun It's a fantastic teaching philosophy. It sure works for me!
@@trevorbbeairsto , Go Team!!!!
Great video which I eagerly waiting since week
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 : )
Thanks Mike for comprehensive guide to the new function :-))
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...
I finally able to use this formula. thanks for the detailed explanation plus the excel file :)
You are welcome, Julis : )
Thanks Mike. God bless you.
You are welcome, Abdulrahman!!!! Thanks for your support : )
OMG! What are U doing? Excellent! Bravo.
Glad you like it, Elena!!!!
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!
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 ; )
@@excelisfun lol thanks will do
@@mmmail1969 Re-watch and new-comment 2 : ) lol
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!
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.
Thanks Mike. Another awsome one ! Today will be a rainy day, just know what to do now ! ;-)
That is sooooo awesome: rainy day video for you all about XLOOKUP : ) : ) : ) Glad you like it and thanks for your support, Thierry!!!!
Wonderful Mike, thank you so much for sharing your videos.
The time line table is just amazing
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?
@@excelisfun I meant the table of content of the video, indicating where each of the 27 parts starts
@@nicor1501 Yes, Sir!!!!! I have been including those Time Hyperlinked Tables of Contents in every large video I have posted since the year 2012!!!!
Ultimate function with your presentation
Thank you, Anil! I hope it will change our Excel lives for the better : ) : )
many thanks , really you are such a good hand Mr. mark
Many You Are Welcomes, ahmed!!! Thanks for the good hand comment, but my name is Mike ; )
You finally go it :-P Great examples!
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 : )
Amazing, thank you!
Awesome explaination sir.. Thank you
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!!!
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 : )
In Florida, Jose Hernandez, what do you use Excel for most of the time?
Thanks mike for amazing tutorial❤️
You are welcome!!!
Can't wit to have this... hopefully this will be available soooooooOOON! Thanks Mike :)
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 : ) : )
Thanks Mike for the info... very much appreciated!
@@edge5817 : )
I have noticed you have increased your speed of explaining...
U are simply awsm when it comes to mechanics of excel..
I just hope I can help you with the videos .
@@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... 🙏
great sir, xlookup best explained only in your video.thanks
You are welcome, Deepak!! I am glad that the videos I post help you!
Yeyyyyy!! Mike got the XLOOKUP at last, and man... What an amazing video. EXCELlent.
Yeyyyyy! Thanks, Syed : ) XLOOKUP will be lots of fun : ) Do you have this function, yet ?
@@excelisfun naahhh. I don't have Office 365 Insider.
@@SyedMuzammilMahasanShahi Oooo.. That's right... I am sorry : (
@@excelisfun I'll try to get one thou :P
@@SyedMuzammilMahasanShahi : ) : ) : )
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
Glad you like it! I can't wait for the weekend either : )
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 : )
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!!
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...
@@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!!
@@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 : )
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.
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!!!!
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. :)
@@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 : )
Big Respect, Thank you so much!
You are welcome so much, Leung!!!!
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.
You are welcome, Mohamed!!!
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
Great explanations!
Glad it helps : )
that whole unique, sort thing was sick.
I agree : )
Thank you. Will be good fun.
You are welcome, Mark!!!! I agree: it will be good (or more) fun ; )
Woah... That's amazing video sir... U always killed it!!!
You are welcome, Maseeh!!!! Glad it was amazing and killed it : )
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. :-(
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 ; )
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... :-)
@@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 : )
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. :-)
@@GeertDelmulle : ) : ) : ) for releasing the new engine!!!!! Soon... I hope : )
always great Mr.Girvin ;)
: )
27 Examples in a Single video..... Super Amazing video....& About Xlook up Function..... it Just like Fire & Forget Missile.It will Find it's Target Anyways.
Glad you like it, Phone Excel : )
Very good. Thank you very much.
You are welcome!
Great Video Mate !!. SUPER LIKE !!
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 !?!?!
@@excelisfun SUPER LIKE is only for my special Mate :)
Such great trick
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!
How fast you do sir, wow super duper master in excel... well done.
Glad you like it, Nayak!!!!
Awesome trick
EXCELlent video, EXCELlent function!
Thanks, Teammate : )
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
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.
thank god that index match thing is now a thing of the past
Great video Mike. I havent got the new calculation engine yet and an missing out on the fun. Cant wait to pay for 365
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!!!
Wonderful video Mike; Lookup and Vlookup have their grandson XLOOKUP
Love your words, Sachin!!!!!! LOOKUP and VLOOKUP have a grandson : ) : ) Here he is: XLOOKUP!!!
@@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
@@sachinrv1 That one is an easy one... Here it is: =SUM(RIGHT(Numbers)+0)
@@excelisfun got that right on.. again our friend Array... Plz continue with your EE, "Excel Exploration", Cheers from INDIA :)
@@sachinrv1 Go Team!!!!!!!
epic & awesome!
I love your 📹 😊 do you have a playlist with just office 365 excel new tricks and features?
Thanks Mike!
You are welcome, Gwapomg!!!
Good Stuff. Worth noting also that XLOOKUP can do a 3-way lookup through multiple worksheets, working in conjunction with INDIRECT to define, from within the range of worksheets, the target worksheet, and always assuming that the 2-way table on each worksheet is located in the same cell-range as it is in all the other worksheets. Incidentally, INDEX and MATCH can also do a 3-way lookup if the user does not yet have Dynamic Arrays, again working in conjunction with INDIRECT to define the target worksheet.
Just super awesome
Glad it is super awesome for you, Sam!!!
Amezing function
I agree, Bindhyesh : ) It is amazing and useful!!