Excel XLOOKUP Function Definitive Guide - Replaces VLOOKUP, INDEX&MATCH & HLOOKUP

The new XLOOKUP function is better, easier and faster than VLOOKUP, HLOOKUP, INDEX & MATCH and OFFSET...oh, and you also don't need IFERROR to hide errors because it's built in.
🔻 DOWNLOAD EXCEL FILE INCL. STEP BY STEP WRITTEN INSTRUCTIONS: www.myonlinetraininghub.com/e...
🎓 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
💬 Please leave me a COMMENT. I read them all!
🧟‍♀️ CONNECT with Mynda on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
0:00 How to use the XLOOKUP Function
1:01 XLOOKUP syntax
1:37 Simple XLOOKUP
2:53 XLOOKUP does HLOOKUP
4:17 XLOOKUP does INDEX & MATCH
7:14 XLOOKUP Returns Multiple Columns
11:18 XLOOKUP Dynamic Range
13:37 XLOOKUP Error Handling
14:45 XLOOKUP Last Value
16:13 XLOOKUP Left
17:18 XLOOKUP Wildcards
18:25 XLOOKUP Approximate Match

[@ToshibaBillMonroe]

The XLookup content is great, but the biggest surprise for me was learning you can move the function guide out of the way at 19:10! Super helpful!

[@MyOnlineTrainingHub]

😁 sometimes it’s the small things.

[@ediedi6751]

Why all tutorials have to be so great? Thanks so much

[@MyOnlineTrainingHub]

Glad you like them!

[@wayneedmondson1065]

Hi Mynda.. thanks for such a great and comprehensive summary of XLOOKUP.. and for providing a workbook to follow along with the examples. Although I have it on an Insider laptop, looking forward to general release of this and XMATCH soon, so I can begin to incorporating in live work product vs. just testing. Thanks for all the great tips and resources you provide. Looking forward to more fun with EXCEL in 2020. Thumbs up!

[@darrylmorgan]

Really like XLOOKUP But INDEX and XMATCH has blown me away!Thank You for the Awesome Tutorial Mynda :):):)

[@maxgolaz1]

Mynda, your tutorials are brilliant - really clear, step by step, downloadable examples, and best user cases for each function. My go to training for excel. many thanks!

[@MyOnlineTrainingHub]

Wow, thank you!

[@erikl.1860]

Amazing how you always know how to explain everything so clearly, will well thought examples! thanks!

[@MyOnlineTrainingHub]

Glad you think so, Erik!

[@cristian.angyal]

Great examples! Thanks for sharing!

[@paulmfti]

Your tutorials are so pleasant and easy to follow. You seem to be a top shelf excel channel and we appreciate your help. It’s made a big difference in my work efficiency and enjoyment. Lots of value added. Thank you very much ;)

[@MyOnlineTrainingHub]

Wow, thank you, Paul!

[@yengyangnaotou9590]

Thank you very much for your wonderful tutorial Mynda ! you are the best !

[@josefhadz343]

Beautifully produced! Well scripted and flawlessly executed. I needed to watch it twice because I was trying to determine exactly how you created the video. Yes, your content was great, informative and I learned some great techniques; but I was thrilled with your delivery. Take care,

[@MyOnlineTrainingHub]

Wow, thank you for your kind words, Josef!

[@juliamt7511]

Brilliant tutorial! All the examples allow us to build a deep understanding of the formulas and its application. 👏👏👏👏👏

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@DennisYeadon]

Thank you. This function has made life here much easier. Your tutorial as usual is clear and informative.

[@MyOnlineTrainingHub]

Great to hear!

[@HM-wj4je]

Today got xlookup in my 365. This is crazy. Best tutor

[@MyOnlineTrainingHub]

Yay! Have fun with it :-)

[@sreeharikca1]

OMG... Amazing.. Thanks for many examples.. Great of you...

[@asifarshed6775]

Great video. Comprehensively covered the xlookup function along with good examples. Many thanks!! 👍

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@aguerojg]

Thank you very much Mynda, your tutorial is fantastic and very easy to follow, you have the gift to explain the function so well. Fortunately Xlookup is already available in the Latinamerican version of Office 365. Have a wonderful New Year full of happiness and peace, and hope the natural situation in Australia improves soon

[@dravenbryson6053]

i guess Im asking randomly but does anybody know of a trick to get back into an instagram account..? I stupidly forgot the account password. I appreciate any help you can offer me.

[@daliladuf]

Your videos are absolute gems! Thank you so much!

[@MyOnlineTrainingHub]

Glad you think so!

[@WilsonC15]

I like the way you put things together and fully recommend what can we do of that function instead of the rest just show a few. Thank you! I love your video!

[@MyOnlineTrainingHub]

Thanks for your kind words!

[@toque15]

This video is awesome! It is very thorough and has great visuals and examples. Thanks!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@estycki]

I just wanted to say thank you so much, I was told we needed to add meal choices to a table seating spreadsheet - I offered to do it because I was sure I could do it with an excel formula. This turned a task that would take someone hours (searching, copying, pasting from another sheet) to something that took a few minutes from watching your video about XLOOKUP.

[@MyOnlineTrainingHub]

Awesome to hear!

[@wynhopkins4023]

Great Video Mynda. Thanks for the shout out 😊

[@blackwhite2361]

This function is so powerful. I only used it to pull information from a dimension table and fill-up a column in a fact table. I never knew it was so versatile. Thanks for making this video.

[@MyOnlineTrainingHub]

My pleasure 😊

[@nisthakhanal6280]

Loved all the examples. especially Example 4. Never thought xlookup could be so useful. Thankyou

[@MyOnlineTrainingHub]

Glad it was helpful!

[@rufe142]

I like the way you discuss, very clear and understandable. Thank you. Kudos.

[@MyOnlineTrainingHub]

I appreciate that!

[@Luciano_mp]

Very good, nice. Thanks Mynda!

[@Tarmogolf]

Just went live in the semi-annual channel. I've passed this tutorial on to my whole office. Thank you so much!

[@MyOnlineTrainingHub]

Woo hoo, so excited that you finally have XLOOKUP! Have fun with it, Stan and thanks for sharing my tutorial :-)

[@BakwasBlog]

Too excited to learn this, had I saw your video 2 years back I'd have done so many things easily, Thank you so much

[@MyOnlineTrainingHub]

Great to hear!

[@philiplbrown]

Very well presented. I am new to Lookups in general, but this has encouraged me to practice.

[@MyOnlineTrainingHub]

Great to hear 😊

[@ravimarella737]

WoW... This is really a game changer... I so excited to use this going forward.. Thank you so much for your tutorial Mynda...

[@MyOnlineTrainingHub]

My pleasure, Ravi!

[@prabhakaransankar476]

So useful and the worksheet is much illustrative, thank you so much for sharing this with us.

[@MyOnlineTrainingHub]

Great to hear it was useful :-)

[@patrickcorrea7710]

Hi Mynda, you are awesome person and an Excel Xpert. Thank you for making such informative videos and sharing your knowledge free of cost. May your efforts be rewarded 10fold & more.

[@MyOnlineTrainingHub]

My pleasure, Patrick! 🙏

[@aldrindacion3468]

Wow...for so many years I've been using the v&h lookup combining and creating some conditions which resulting to increasing the file size. Really its a game changing. Thanks alot.

[@MyOnlineTrainingHub]

Great to hear 😊

[@yengyangnaotou9590]

Thank you so much Mynda Treacy your tutorial are the best !

[@MyOnlineTrainingHub]

Thank you so much!

[@toshman696969]

This was pretty awesome. Loved filter and choose function incorporated into this.

[@MyOnlineTrainingHub]

Awesome, thank you!

[@yulinliu850]

Great tutorial! Thanks

[@nairobi203]

Before the video you think you know Excel... dunrg and after the video you realise you still have a loooooong way to go.. Thanks for sharing.

[@MyOnlineTrainingHub]

So pleased you learnt something new 😊

[@ranjithanand8680]

Absolute game-changer. Great work. Would request this kinda "multiple use case" style explanations and comparisons over other formulas for the upcoming videos. Cheers!

[@MyOnlineTrainingHub]

Thanks for the suggestion, Ranjith!

[@enkhchimegbatbold51]

Very useful, eye-opening. Thank you Mynda

[@MyOnlineTrainingHub]

You are most welcome!

[@abdisalammohamed8851]

I always enjoy, the way you present and your persuasive examples, Thanks for the efforts, watching this September 2022.

[@MyOnlineTrainingHub]

Glad you like them!

[@gujjenaut]

Thanks for teaching the killer Xlookup formula !!

[@MyOnlineTrainingHub]

My pleasure :-)

[@earlescourt66]

Another great video Mynda. I was struggling to get correct results using xlookup but your very useful flag that both ranges needed to have the same number of columns/rows gave me the reason why. 😊. As my second range needs to be larger. So I now have success using index match!

[@MyOnlineTrainingHub]

Awesome! Glad I could help!

[@arun7kurup]

Great video again! Appreciate your expertise and examples.

[@MyOnlineTrainingHub]

Glad you like them, Pharm!

[@rajneeshrai1781]

The best and proper tutorial for Xlookup 🔥 Thanks you

[@MyOnlineTrainingHub]

Thank you! So pleased you think so, Rajneesh :-)

[@CP-rm7rz]

Life saver on xlookup dynamic range sum

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@sivas9443]

Wonderful & Extremely Helpful ! thanks a ton!

[@MyOnlineTrainingHub]

Glad to hear that!

[@growth2509]

Excellent coverage of xlookup! Very helpful to me.

[@MyOnlineTrainingHub]

Thank you! Great to hear :-)

[@malejandrahorvath]

Thank you so much! Definitely a game changer formula, love it :)

[@MyOnlineTrainingHub]

Glad it was helpful, Alejandra!

[@Jojosmith342]

Excellent tutorial. Subscribed. thumbs up. thanks for teaching

[@MyOnlineTrainingHub]

Awesome, thank you!

[@mohammedfiezaan8652]

Your video is so amazing I learnt xlookup so quickly. Really helpful examples for real-life work scenarios

[@MyOnlineTrainingHub]

Great to hear!

[@mohammedfiezaan8652]

@@MyOnlineTrainingHub how to i look up a value if its in another tab so i want it to get the value from one worksheet to another.

[@phoebesguitar]

Quality Content! Liked, Shared and Subscribed!

[@MyOnlineTrainingHub]

Much appreciated, Ryan!

[@nhidinh679]

Very nice. Thanks a lot for the great detailed tutorial about Xlookup.

[@MyOnlineTrainingHub]

Glad it was helpful! Have fun with XLOOKUP :-)

[@Johan10683]

Best Excel channel! Thank you!

[@MyOnlineTrainingHub]

Glad you think so, Johan!

[@PolanskyMoreira]

Awesome explanation. Thank you very much. Gretting from Ecuador

[@MyOnlineTrainingHub]

Glad it was helpful!

[@AshwaniSharma-fc2nz]

Very much helpful Maam

[@thulasiramanjayaraman7832]

Very nicely and clearly explained. Thank u so much for the efforts.

[@MyOnlineTrainingHub]

You are most welcome :-)

[@TheeRKLOVE]

This is great video. Appreciate the examples and well explained uses.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@sawa85sa]

Brilliant! one more that I was actually expecting here for everyone that hates concatenating multiple lookup criteria and than doing the same with multiple lookup columns: {=XLOOKUP([Lookup_value_1]&[Lookup_value_2],[Lookup_Column_1]&[Lookup_Column_2],[Results_Array])} CTRL+SHIFT+Enter for Array formula

[@MyOnlineTrainingHub]

Thanks for sharing your formula, however, because this is a dynamic array, you no longer use CTRL+SHIFT+ENTER :-)

[@gyozakeynsianism]

Yes! I just figured that out yesterday, too. It is very powerful. And yes, don't need to tell Excel it's an array formula.

[@yuxiashan529]

Very helpful! Thank you, Mynda

[@MyOnlineTrainingHub]

Great to hear, Yuxia!

[@gyozakeynsianism]

This is extremely helpful, Mynda. Thank you.

[@jamespaul4618]

7:40 lookup array and 'return' array syntax. 12:55 viewing formula. Thanks for your insights

[@MyOnlineTrainingHub]

My pleasure, James!

[@vijayarjunwadkar]

Thank you Mynda for this comprehensive XLOOKUP tutorial! It's truly a game changer, and you have explained it so well! 😊👍

[@MyOnlineTrainingHub]

Glad it was helpful, Vijay!

[@frutituti]

Thank you so much for all your tutorials! Xlookup !!! Wooow finally excel goes to the left (like Beyoncé)! I would love to ask you so many questions about excel☺️

[@MyOnlineTrainingHub]

:-D "Excel goes left like Beyonce", love it!

[@danielsakin9694]

Very nice video, please keep them coming.

[@MyOnlineTrainingHub]

Thanks, will do, Daniel!

[@shoaibrehman9988]

Excellent demonstration just installed 365 ready to use xlookup, definitely this tutorial is very helpful. Thanks a lot for amazing efforts and sharing knowledge. Thanks Take Care

[@MyOnlineTrainingHub]

Great to hear, Shoaib!

[@User-xw4dt]

In this Video are so much Informations and all of them are really great. Working in a CallCenter and already used a few them.

[@MyOnlineTrainingHub]

Great to hear!

[@chonzadaniel]

Thanks Mynda, very handy stuff!👏

[@MyOnlineTrainingHub]

Glad you liked it, Emmanuel!

[@albertlaginikoro2631]

Great tutorial, understandable and absolute brilliant. Thank you very much..

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@SamPandiangan]

My covid-19 self quarantine becomes useful and more productive, thanks to your very useful and your excellent delivery. Why I only got to know your videos for the last one week? Where were I've been? LOL Thank you so much, Mynda.

[@MyOnlineTrainingHub]

:-) great to hear you're finding my tutorials helpful!

[@nvicki2710]

Absolutely brilliant! Thanks a lot!

[@MyOnlineTrainingHub]

Glad you liked it!

[@abdelouahedelmaazouzi6102]

wonderful formula and very well explained! Thank you very much 🙂

[@MyOnlineTrainingHub]

Glad it was helpful! 🙏😊

[@qadeerhussain5351]

That's Great Formula, Let me practice it, appreciated your Efforts

[@MyOnlineTrainingHub]

My pleasure, Qadeer!

[@gyozakeynsianism]

Actually Mynda this is probably the single most useful KZbin video I've ever come across. (There was that one video that explained Fourier transforms and the frequency domain back when I was learning that stuff, but I don't use that stuff at all anymore - whereas Excel is eternal :-). Also I'm teaching my wife some Excel too using your videos. Are you on Patreon? Your videos are definitely worth a subscription.

[@MyOnlineTrainingHub]

I'm so pleased to know you're finding the videos helpful. I'm not on Patreon, sorry, but I have courses on my own website here: www.myonlinetraininghub.com/

[@vishnusoman8532]

Thanks, this is exactly what I was looking for.

[@MyOnlineTrainingHub]

Great to hear, Vishnu!

[@statmanron]

This was great presentation. I'm a big fan!!!

[@MyOnlineTrainingHub]

Thanks so much 😊

[@signalmas5673]

One of the perks of index match I’m using a lot is the shortcut Ctrl+[ that sends you to the location of the first argument in the formula, in this case the lookup array. Using XLOOKUP (similarly to Vlookup and Hlookup) will send me to the lookup value. Not useful. Happy to see the powerful Index Xmatch to keep my needed perk and still benefit the array return power of excel new cal engine Thank you for opening eyes for other uses and additional arguments, I might start using Xlookup in some instances. We’ve been teased with this new excel calc power for soooo long... we want it, we need it....now.

[@shetkar911]

I have seen plenty of videos on xlookup and u r the best among them. thanks for this video

[@MyOnlineTrainingHub]

Thank you so much 😄

[@shetkar911]

Kindly help me with this... i need to arrange few items in such a way that if i choose any item i need few items to be populated in front of it Example: if i choose laptop a list of items need to populate like charger, speaker, mouse related accessories What function can do this ???? Please help

[@MyOnlineTrainingHub]

It really depends on how your source data is structured. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@shetkar911]

@@MyOnlineTrainingHub Posted my query with an attached file

[@moiz6330]

Great work... It really helps... Thanks

[@MyOnlineTrainingHub]

Glad to hear that 😊

[@kishoreyc]

You are my inspiration. You are my Guru.

[@MyOnlineTrainingHub]

Thanks for your kind words 🙏

[@timmytesla9655]

I learnt a lot. Thank you for this great video.

[@MyOnlineTrainingHub]

Glad to hear it!

[@user-ve7xq2xy3g]

Great Training.. Thank you.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@cesara7478]

Your are the master! Thks for sharing this video!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@francinagoh2541]

Thanks for the illustration.

[@MyOnlineTrainingHub]

You're welcome, Francina!

[@emilgeorgiev3700]

Thank you Mynda!

[@MyOnlineTrainingHub]

My pleasure, Emil!

[@sachinrv1]

Thanks for this video. XLOOKUP has all the good features of VLOOKUP, INDEX&MATCH,LOOKUP,HLOOKUP. Besides this, it can also do two way lookup, ignore errors, find approximate match , find wild card match etc. You cannot ask for more. Excellent function in Office 365.

[@MyOnlineTrainingHub]

Indeed 😊

[@nutanisnow982]

Great work mam, don't know why people dislike such videos

[@MyOnlineTrainingHub]

There's always going to be haters :-) that's their problem. I don't let it bother me as I'm more interested in the people I can help.

[@jimmyflinn8718]

Awesome video. Really well explained.

[@MyOnlineTrainingHub]

Glad you liked it, Jimmy!

[@DianHrozek]

Awesome as usual 🤩 Thank you

[@MyOnlineTrainingHub]

Thanks, Dian :-)

[@leedsunitedfan8494]

Excellent. Thank you

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@pierrecatpacbell]

Thank you, very helpful.

[@MyOnlineTrainingHub]

Glad to hear!

[@Ignacio_The_Muffio]

you are amazing! I realise the few things I master in excel

[@MyOnlineTrainingHub]

Glad you enjoyed my video :-)

[@Ignacio_The_Muffio]

@@MyOnlineTrainingHub for me it's impossible to learn so much! you are as well a lovely person, thanks very much for answering. I will check as well your web-site, see if there is any course I can follow.

[@laluyani6083]

Very helpful, as usual. Love excel more. Thanks..

[@MyOnlineTrainingHub]

Great to hear!

[@charlesqi4850]

This is suuuuuuuuuuper useful, thank you so much!

[@MyOnlineTrainingHub]

Great to hear you're excited about XLOOKUP, Charles :-)

[@RaffaeleLauricella]

Amazing!!! Thanks a lot!!!

[@MyOnlineTrainingHub]

My pleasure, Raffaele :-)

[@adedejiemmanuel1]

You are a genius. Thank you.

[@MyOnlineTrainingHub]

Great to know you enjoyed my video, Azza :-)

[@user-nm4ul6gr6o]

Thank you very much, dear teacher.

[@MyOnlineTrainingHub]

You're welcome!

[@floral2000]

Thank you for sharing.

[@MyOnlineTrainingHub]

My pleasure, Daisy!

[@debashishdas9186]

It’s very helpful,,,, Thanks a lot,,🥰

[@MyOnlineTrainingHub]

You're welcome 😊

[@Kursatural]

Thank you so much!

[@MyOnlineTrainingHub]

My pleasure 😊

[@jeffquayhagen1705]

So cool, and yes, this just made my day!

[@MyOnlineTrainingHub]

Wonderful to hear 😊

[@uyenle1825]

so great, thank you so much

[@MyOnlineTrainingHub]

Glad you liked it!