Excel XLOOKUP Function - Comprehensive Lessons - 27 Examples - Excel Magic Trick 1600
HTML-код
- Опубликовано: 3 окт 2024
- Download Excel Start File (to follow along with video): excelisfun.net...
Download Excel File with All Examples Completed AFTER video is don: excelisfun.net...
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)
@@excelisfun I was having the same issue but doing your fix helped! But my issue is I didn't get the help options where it shows what -1, 1, 2 or -2 means when I entered the comma after the return array.
Nobody gives better examples than excelisfun
Thank you, RRR! I try to have fun and tell a good story : )
Comprehensive yet concise 👌
Glad you like it!!!!!!
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
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 : )
Thank you so much! I learned a LOT from your videos and examples.
You are welcome so much!
this is probably the best video on xlookup.
Glad you like it, robert!!!
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 : ) : )
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 : )
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 : )
*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 : ) : )
Thank you Mike for the amazing tutorial on XLookup! I'll certainly go back and cosume each sheet over and over...😊👍🙏❤
You are welcome!
that whole unique, sort thing was sick.
I agree : )
Ultimate function with your presentation
Thank you, Anil! I hope it will change our Excel lives for the better : ) : )
What a valuable lesson ..... thanks Mike
You are welcome, Hussein : ) Value is good!!!!!
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 RUclips 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 RUclips 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 : )
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... 🙏
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?
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!!!!
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.
Learnt a lot watching this. Thank you!
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 : )
An excellent comprehensive tutorial. Worth waiting for
Glad you liked it and it was worth the wait, John Durran!!!!
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.
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 : )
Great. Thank you very much, Mike!
You are very welcome, Shiping Li!!! Thanks for your support : )
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 RUclips recommendation of 5 stars and thumbs ups : ) : ) BTW, there are lots more cool videos to come on this amazing XLOOKUP function!!!
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 : )
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 ; )
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 : )
XLOOKUP rocks!! As always, great video tutorial.
Glad you like it, Frederick!!!
thank god that index match thing is now a thing of the past
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.
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
This new function is amazing! Thanks Mike!
I agree - just so many things all in one. You are welcome, as always, Chris : )
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!!!!!!!
Thumbs up as always! Great examples!
Thank you very much, Teammate pmsocho!!!!
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 : ) : ) : )
I am quite happy 😊 to see that you have Xlookup now. Thanks for the awesome examples. 👍
You are welcome for the awesome examples, Salim!!!!!!
OMG! What are U doing? Excellent! Bravo.
Glad you like it, Elena!!!!
Thanks mike for amazing tutorial❤️
You are welcome!!!
How fast you do sir, wow super duper master in excel... well done.
Glad you like it, Nayak!!!!
great sir, xlookup best explained only in your video.thanks
You are welcome, Deepak!! I am glad that the videos I post help you!
Magnificent video! Thanks!!!
You are magnificently welcome, Yulin Liu!!! Thanks for your support : )
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 : ) : )
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.
Thanks Mike. God bless you.
You are welcome, Abdulrahman!!!! Thanks for your support : )
Super Video , always amazed by your Tutorials , Thank You :)
Wow.... Thank you Mike for another awesome excellent vdo.
You are welcome, Hassan! Thanks for your support : )
Great Video, Hard work behind, Thank you Mike!
You are welcome for the video and the hard work behind it, Muhammad!!!!
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 : ) : )
I finally able to use this formula. thanks for the detailed explanation plus the excel file :)
You are welcome, Julis : )
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 : )
Amazing scenarios Mike. Thanks for the FUN!!!
You are welcome for the fun scenarios, John Borg!!!!!! XLOOKUP Fun : )
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!!!!
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 RUclips : )
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...
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
Amazing, thank you!
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 : )
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 ; )
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 : )
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 : )
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 : )
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 :)
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...
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!!!!
Thanks so much Mike. Thanks again for all your labor.
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 : )
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!!!!
Absolutely Amazing. Thank you Mike...👍👍👍
You are absolutely welcome, Luciano : ) : )
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.
Awesome explaination sir.. Thank you
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!!!!
Very good. Thank you very much.
You are welcome!
Amazing video, so many good examples, thank you Mike!
You are welcome for the so many good examples, rf05mjy!!!!
Thank you. Will be good fun.
You are welcome, Mark!!!! I agree: it will be good (or more) fun ; )
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 : )
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 : )
EXCELlent video, EXCELlent function!
Thanks, Teammate : )
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 : )
always great Mr.Girvin ;)
: )
Amazing video especially that XLOOKUP is now officially available to all Office 365 users :))
timing couldn't be better.
Great explanations!
Glad it helps : )
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!!!!
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 : )
I love your 📹 😊 do you have a playlist with just office 365 excel new tricks and features?
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!
excellent video.
Glad this helps : )
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.
Woah... That's amazing video sir... U always killed it!!!
You are welcome, Maseeh!!!! Glad it was amazing and killed it : )
Thanks Mike
You are welcome, Dave Bowman!!!!!!
thanks
You are welcome, mohamed!!!!
Such great trick
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.
Awesome trick
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 : )
The Lookup function was provided for backward compatibility in earlier iterations of Excel so I am not sure how much of that function's algorithm was used in XLookup's algorithm. I always liked the concept of the Lookup Function, and its seems sombody at Microsoft liked the concept as well.
The algorithm of LOOKUP was not used, but the fact that it had two separate arguments for the lookup array and return array with no column index has been with us for almost 40 years : ) 1980 was when VisiCalc release LOOKUP. I am surprised they did not do this sooner...
@@excelisfun You may not remember but year's ago you set me straight on Excel's Aggregate function. I thought that function ran all the algorithms (AVERAGE
, COUNT, COUNTA etc) each time the the function was used, but you ran a timing test for me and set me straight--Aggregate was like an umbrella which had a set of algorithms but only the algorithm selected in the function argument would be run. That gave me a great insight into how MicroSoft wrote that algorithm.
Quattro Pro Values also has the @Lookup function, but not 1-2-3. The syntax is the same, value, LookupVector and ResultVector and both vectors must be in ascending order for @LOOKUP to return the correct value (upper- and lowercase text have the same values).
There could have been licensing or copyright reasons why the Lookup function's vector concept wasn't used previously. That's just a guess. I am sure that it's just a matter of time before custom user defined functions which accomplish much of the new functions Xlookup and Xmatch do.
@@michaelconnors7668 So cool: I remember Quattro Pro : ) : ) For VisiCalc, back in 1979, the story goes that Bricklin and Frankston wanted to be able to do they taxes, and so that is why they made sure to have the LOOKUP function in the first release of the spreadsheet... LOOKUP, Quattro Pro, Lotus 1-2-3, AGGREGATE, they all seem so old now that we have XLOOKUP : )
@@excelisfun 1st. Quattro Pro has the very cool function @VHLOOKUP--@VHLOOKUP - Vertical and Horizontal Lookup
Syntax
@VHLOOKUP (V_Val, H_Val, Block, )
V_Val The value of the index row.
H_Val The value of the index column.
Block The range of cells.
Type Lets you specify whether or not the match must be exact.
0 Does not need to be an exact match
1 Must be an exact match
@VHLOOKUP returns the value at the intersection of the row and column specified by V_Val and H_Val.
Example
In the following example, @VHLOOKUP searches across the index row ("FEBRUARY") and down the index column ("TWO") of the Block (A1..D4). @VHLOOKUP returns the value at the intersection of the index row and the index column.
A B C D
1 ONE TWO THREE
2 JANUARY 5 52 84
3 FEBRUARY 10 32 67
4 MARCH 15 42 18
@VHLOOKUP("FEBRUARY", "TWO", A1..D4)
Returns: 32
Quattro Pro Functions Help.
@@excelisfun 2nd. Quattro Pro also has another function @XINDEX - Return Value at Column and Row
Syntax
@XINDEX(Block,ColHead,RowHead,)
Block Cell name or reference.
ColHead Column to look in; must be the contents of a cell in the first row of the cells.
RowHead Row to look in; must be the contents of a cell in the first column of the cells.
PageName Name of notebook sheet (optional).
@XINDEX returns the contents of a cell located at the intersection of a specified column, row, and (optionally) notebook sheet.
ColHead, RowHead, and PageName can be numeric values or text.
@XINDEX looks first for the sheet name, if specified. Then it scans the leftmost column for the value RowHead and the top row for the value ColHead, returning the value in the cell at the intersection.
Examples
You record home sales prices and dates in a table called HOUSES, with a notebook sheet for each area. The sheet for Long Island looks like this:
A B C D E F
1 Cust# Offered Date sold Asked Sold for Months on mkt
2 286 05-May-95 27-Jul-95 $325,000 $315,000 2
3 183 02-Feb-93 7-Aug-95 $295,000 $250,000 30
4 173 04-Apr-95 11-Sep-95 $150,000 $135,000 5
5 218 25-May-94 12-Oct-95 $495,000 $425,000 16
6 104 15-Mar-94 17-Oct-95 $195,000 $150,000 19
The amount Long Island customer 183 paid is
@XINDEX(HOUSES,"Sold for", 183, "Long Island") = $250,000
The date Customer 218 closed is
@XINDEX(HOUSES,"Date sold", 218, "Long Island") = 12-Oct-95
At 5% commission, the agent who sold Customer 104 a house received
+0.05*@XINDEX(HOUSES, "Sold for", 104, "Long Island") = $7,500
Thanks Mike!
You are welcome, Gwapomg!!!