How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)

Рет қаралды 3,551,149

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover the powerful capabilities of Excel's XLOOKUP function in this detailed tutorial. Ideal for Excel users looking to enhance their data lookup skills. XLOOKUP offers a more versatile and efficient approach compared to traditional functions like VLOOKUP and INDEX MATCH.
⬇️ Download the file I used in the video from here: pages.xelplus.com/xlookup-file
If you'd like to learn about the other brand new Excel functions like FILTER & UNIQUE check out the complete course 👉 www.xelplus.com/course/excel-...
In This Tutorial, You'll Learn:
▪️ Handling Missing Items: Understand how to effectively manage errors and missing items in data using XLOOKUP.
▪️ Using Wildcards for Partial Matches: Explore partial match lookups in XLOOKUP with wildcard characters.
▪️ Simplifying Horizontal Lookups: Replace HLOOKUP with the more efficient XLOOKUP for horizontal data searches.
▪️ Mastering Two-Way Lookups: Learn to conduct searches across both rows and columns using XLOOKUP.
▪️ Performing Approximate Matches: Gain insights on how to execute approximate matches for nuanced data analysis with XLOOKUP.
00:00 What is Excel's New XLOOKUP Function
01:15 Lookup Value Not Found with XLOOKUP
04:06 Horizontal Lookup with XLOOKUP in Excel
05:44 Look For Partial Match with XLOOKUP
07:51 Two-Way Lookup with XLOOKUP
10:29 Approximate Match with XLOOKUP
** AVAILABILITY ** The XLOOKUP function is available to Office 365 and 2021 users.
LINKS to related videos
First XLOOKUP video: • Discover What XLOOKUP ...
Excel left lookup with XLOOKUP: • How to do a Left Looku...
➡️ Join this channel to get access to perks: / @leilagharani
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

Пікірлер: 1 100

@LeilaGharani 4 жыл бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/xlookup-file XLOOKUP is available to Excel for Office 365 and 2021 users. If you'd like to learn about the other brand new Excel functions like FILTER & UNIQUE check out the complete course: www.xelplus.com/course/excel-dynamic-array-365-formulas/

@dicello 4 жыл бұрын

Hi Leila, I am using Excel in Office 365 but XLOOKUP function is not available on my pc. Do you have any idea why it isn't available. I will also write to Microsoft Support

@naderazzam5632 4 жыл бұрын

@@dicelloHi Koray, you have to be an office insider user to use it.

@dicello 4 жыл бұрын

Nader Azzam Thank you, I will have to wait for the public release than

@thomasst.martin2522 4 жыл бұрын

Koray Aydın I thought this was a public release as of last month. Just make sure your software is fully updated.

@dicello 4 жыл бұрын

Thomas St. Martin I checked if there are any updates but it says it’s up to date. Maybe they are distributing region by region. I am located in Turkey.

@kwamsahortor 2 жыл бұрын

I've been following and watching your videos for about two years. I'm a hardcore data analyst, I mean python, SQL, R guy. Had a job interview today and it was mostly excel based. Took a refresher with some of your videos and it saved me a lot. Thanks Leila.

@todayspecial9705 8 ай бұрын

and you learnt SQL & R from? how do we practice that? em actually starter in R

@kmlumd44 4 ай бұрын

Dude i dont think leila is a real person. Ur crying ur heart out to a bot right now.

@amandeepsinghid 2 жыл бұрын

Leila, there is no comparison to how simple you make things for your audience to understand when it comes to anything in excel. Hats off!

@kmlumd44 4 ай бұрын

Leila is a bot, man. We'll have her program u in a response on her next iteration

@TestTestGo 2 жыл бұрын

Always used to use index match match for these sorts of problems. Nice to see that Microsoft have included the concept as a single straight forward function rather than the clever trick that it was before.

@QcFrogMan Жыл бұрын

Simply amazing and so well explained. I’m a self taught excel user, been using it for over 25 years. Always used Vlookup, for so many things, but turned to xlookup maybe a year ago and have been loving it since, but using only its most basic form. These tips will come in handy, thanks!!

@hayleyross1207 2 жыл бұрын

Why am I watching this at 9:30 at night and getting super excited at how much easier this will make my life!

@BMags1 2 жыл бұрын

I’m watching it at 3:45am and having the same reaction. ☺️😞

@PokySan 2 жыл бұрын

Same here 01:06 and I have small kid's too I should really try to sleep!

@newoxnewox Ай бұрын

11:15pm here

@HoratiuDPop 3 жыл бұрын

Thank you, your videos are are always well structured, professional, high value and very useful. It takes knowledge, effort and passion to achieve this level of "perfection".

@dfsupport Жыл бұрын

Excellent video Leila! I just watched a couple of other videos on this topic done by other KZfaqrs and I was left in a cloud of confusion! I am not an excel guru but I need to get better at it. What makes your videos stand out is that you use real-world examples. I like the fact that most of your videos are under 20 minutes. If I forget how to do something I just watch the video again. I already have a few ideas on how to use XLOOKUP. Keep doing what you are doing, Leila! Thanks so much!

@bcd773 2 жыл бұрын

I will be rewatching this a few times. Game-changing lessons in lookup function, thank you!

@DarrenStarr Жыл бұрын

Leila I've bought your courses, because you explain things more clearly than anyone and the editing must take more time, but makes things so clear. You are my favourite Excel teacher by far. Thanks for all the value you provide. Really helps with my job.

@jessmaya8025 Жыл бұрын

I agree. Her clear explainations/teaching save me heaps of work!

@aldarei2013 11 ай бұрын

thank you so much for the well explanations you provided

@AliGaming-jm7fx 3 ай бұрын

Who else is learning Xlookup at 3:00 AM 😂😂😂😂

@husnainasghar7795 2 ай бұрын

Literally me 😂

@shrawins 2 ай бұрын

Fr.

@hassanadewumi8025 2 ай бұрын

Me 😂😂😭

@jonathanho8813 Ай бұрын

At 1.30am maybe

@sowrinvora1 Ай бұрын

4.43 AM 😂🎉

@lesherj Жыл бұрын

@Leila your videos are consistently the most helpful and easiest to follow. Every now and again, I click on someone else's video for help but I almost always come back and end up finding what I need from yours. Thank you for all of your posts, they are truly helpful! --Joe

@priman0cte 2 жыл бұрын

I know this video is a few years old now, but the explanation for the 2-way lookup saved me today. I'm putting together a table, that calculates freight prices, based on the amount of pallets/ load carriers and the consignee's postal code. Thank you!

@darrengray4797 2 жыл бұрын

Really comprehensive overview of a function that I’d not even heard of. Thanks for sharing 👍🏻

@LeilaGharani 2 жыл бұрын

Glad it was helpful!

@excelisfun 4 жыл бұрын

Thanks for the XLOOKUP magic, Teammate Leila : )

@jedher1990 2 жыл бұрын

Love both of your channels

@0401412740 2 жыл бұрын

She's married bro

@reddingmclemore7515 2 жыл бұрын

THANK YOU! I love your presentation style - just right for my knowledge of excel! I just learned about XLookup and my goal this morning was to learn how to use it. You started my day with a win!

@Skystang 2 жыл бұрын

Excellent. You explain this in an easy to understand way. I liked vlookup but I love xlookup. Can’t wait to put it to good use. Thumbs up from me!

@rkcivilengineer2088 2 жыл бұрын

Hello Leila, Thank you for all your amazing courses on Udemy. I'm upgrading my skill through your courses. Thank you.

@yurizhang178 3 жыл бұрын

I'm taking your vba courses on udemy. I've taken so many data analytics related courses taught by industry experts and you are the best teacher. Your style is always so clear and concise. Thank you!

@Zinzy 3 жыл бұрын

Getting all this knowledge from your videos... Thank you!

@charlesedwinbooks 2 жыл бұрын

I am so JAZZED for xlookup! Thank you for this amazing tutorial! I cannot wait to start using this!!

@nigellaguda4956 4 жыл бұрын

Hello Leila, Thank you for all your amazing courses. I have definitely become better using them. Thank you.

@LeilaGharani 4 жыл бұрын

Glad to hear that Nigel!

@imranuddin2153 3 жыл бұрын

Thanks Leila. I have always used index-match combinations for dynamics lookups. When I purchase an office 365 subscription, I was excited about xlookup, but wasn't quite sure how to make it dynamic as we could do with index-match. The 4th example where you used nested xlookup addresses the issue.

@Rebahna 9 ай бұрын

Thank you so much! This is is the video that I didn't know I needed to see today! I had spent the better part of my day trying to create a nested formula that exceeded the 64 entries allowed. I was trying to figure out how to do what I needed using VLOOKUP when I stumbled across your video and followed your instructions and got my formula to work and return the data that I needed. Your instructions and explanation were so easy to follow. Again, thank you! You saved me a lot of time!

@grantthornley7321 Жыл бұрын

Leila, I subscribe and follow very few people however the amount of information and practical knowledge you provide is incredible. You are my immediate go to whenever I am looking to simplify my excel programs. Thank you.

@LeilaGharani Жыл бұрын

Wow, thank you!

@shantikhot1345 2 жыл бұрын

Your teaching is amazing...I have started your excel dashboard course on udemy....And I am so excited to learn it...😃👏👏👌👌

@LeilaGharani 2 жыл бұрын

Wonderful!

@m.saadsiddiqui389 Жыл бұрын

For a chemical engineer who imports massive tabulated data into Excel and calculates properties based on MULTIPLE PARAMETERS, XLOOKUP is gold and time-saving! Thank you for sharing your knowledge with us. You've earned a subscriber :)

@AndyHydes Жыл бұрын

Excellent tutorial, Leila, nice and clear as always. Already used the functions and it's made life a lot easier :)

@mando388 2 жыл бұрын

Amazing, Indeed I benefited a lot from your lessons and raised my level of competence a lot, and therefore I got an increase in my salary

@kajet666 2 жыл бұрын

Looks like they’ve fixed vlookup’s fatal flaws, especially the fixed column number reference which made the function unusable and had everyone replace it with index(match). Well done.

@LeilaGharani 2 жыл бұрын

👍

@gareth6326 2 жыл бұрын

You can use a match for the column number so it is just as dynamic.

@IftikharAhmad-ol3kc 2 жыл бұрын

Clearing concept in simplest way possible.... loved it

@MidoCounters Жыл бұрын

Leila is the hero we never knew we needed. Amazing courses!

@kevinj5989 4 жыл бұрын

Really nice! ZERO ums, ahs, pauses, or slips. Perfect! Thank you! I bought a pile of your Udemy courses too.

@LeilaGharani 4 жыл бұрын

That's great. I hope you will enjoy the courses.

@kevinj5989 4 жыл бұрын

@@LeilaGharani It's VERY RARE that I hear anyone on KZfaq as good as you are. You never make a verbal mistake; never pause to figure out what to say; never say ah, um, or utter any filler words. Every phrase you speak moves the narrative forward. I think you're the best that I've ever seen (heard). I stand in awe. :-)

@rusty10785 2 жыл бұрын

I love how simplistic excel examples always are when in reality you are using these functions to look through 10 different workbooks lol

@iau Жыл бұрын

If you need to lookup accross 10 different workbooks, you're already way past the point of needing a database and using SQL for that.

@Alikhan78611 2 жыл бұрын

This is game changer! No longer have to arrange data for lookup! Thank you.

@erickoluoch9987 2 жыл бұрын

Xlookup is that powerful! I love your simple explanation

@1221270 3 жыл бұрын

You know you're depressed af when you get excited over a new Excel formula.

@LeilaGharani 3 жыл бұрын

Haha, then I guess I'm mega-depressed 😁

@DylanStarScream 11 ай бұрын

@@LeilaGharaniI am (still) excited too about this😂

@clevercitychick 4 жыл бұрын

I can't wait until this is included on all versions of Excel

@LeilaGharani 4 жыл бұрын

It’s now available for all 365 versions 😊

@sandugvr 3 жыл бұрын

@@LeilaGharani not working in 2007 version.

@StopEnglishia 3 жыл бұрын

@@sandugvr because microsoft ended office 2007 and you have to pay for microsoft 365

@originalkundukulangara9281 3 жыл бұрын

It is not available in 2016

@NoraRainey 3 жыл бұрын

It is not on Office Professional Plus 2019. Bummer!

@bernadettehill4710 2 жыл бұрын

Life changing! How did I not know this?? Have already shared with other colleagues and they had not heard of Xlookup either. Super excited about using this new formula and the video was excellent, thank you so much Leila!!

@LeilaGharani 2 жыл бұрын

Happy to help!

@GordonSimmons 2 жыл бұрын

I do some professional development every Friday and have subscribed to your channel as an integral part of that. Your format is great. Concise, easy to follow, and very informative.

@LeilaGharani 2 жыл бұрын

Awesome, thank you!

@roxiserban 4 жыл бұрын

When can we have this.....really want it. How long we need to wait...

@richarddu3835 4 жыл бұрын

Can't wait, so I just participated the insider program and updated my Excel...

@jasonwoods5326 4 жыл бұрын

Now, as of 10 Feb 2020.

@johnnycake020 4 жыл бұрын

I think it’s already out

@forestsunrise26 3 жыл бұрын

"James Willard got a maximum bonus of 0 - that looks good!" - but James is sad :P Just kidding! Thank you so much for this amazing tutorial!

@LeilaGharani 3 жыл бұрын

Poor James :)

@aloofmeatloaf1467 2 жыл бұрын

Came here for this comment

@juls9710 2 жыл бұрын

I have been using sumproduct for the 2 way lookup but xlookup seems easier and simpler. Thank you!

@venkatiyer7459 Жыл бұрын

Your Explanation is awesome as usual. I was not understanding two ways lookup but after watching your video I can use this at ease. Thanks a lot.

@brendanlochead2084 2 жыл бұрын

You shouldn’t use Xlookup if you’re planning on sharing the spreadsheet with colleagues or clients. It’s functionality only works on updated versions of excel and won’t work on old versions of excel.

@yaman3566 2 жыл бұрын

Paste as values.

@ChrisTian-uw9tq 2 жыл бұрын

Similar as to what Yman states - but first save a copy of your file under different file name so you can keep your formula version for yourself, then Copy -> Paste Values... a good habit to do in general because the chances of the person you are sending the file on to, knowing how to manage formulae let alone filtering and who knows what type of manipulations, better rule of thumb to keep a working file for yourself for later and provide a simple output to anyone downstream, unless the aim is to have them adjust values incorporated in the formula.

@nicok8203 2 жыл бұрын

doesnt every business have o365 nowadays

@The757packerfan 2 жыл бұрын

@@nicok8203 no :( We are still on MS Office 2016

@Real28 8 ай бұрын

Nah, upgrade or GTFO.

@bobfish7699 4 жыл бұрын

At Last - Been waiting for the 'IF not Found' parameter for years. All I've got to wait for now is for all of my client PC to get this version of excel....

@keithdutch5295 2 жыл бұрын

Thanks for the simple approach Leila to the multiple variable XLOOKUP function!

@user-fw8nq2yq6n 10 ай бұрын

love the way you speak and teach. Wish you keep teaching always and i keep listening.....thanks for being so nice while teaching. have a great day.

@abelmanalo3239 2 жыл бұрын

Wow, thank you. Before I am using Vlookup then shift to Index-Match then first time to know XLookup. Excited to apply this in my spreadsheet seems to be more powerful. Thank you very much.

@cooltimes3597 2 жыл бұрын

I like to listen to your tutor always because the way you explain is extraordinary, Thank you so much.

@user-kh2rd3od8x 11 ай бұрын

Hello Leila, Thank you for all your amazing courses

@PokySan 2 жыл бұрын

I was going to change a excel i use at fork, to be more automated and just found this vid, it will make it a lot easier for me. Funny thing is that actually I can use all of the examples in different areas of the excel! Can't wait to update the sheet now;). Thanks

@ahmedabbas569 2 жыл бұрын

Thanks for these valuable info, simple, neat and to the point... keep forward

@CarmenGonzalez-zi5je 2 жыл бұрын

Thank you so much for your videos! I was very lucky to found your channel! My native language is spanish, and I can understand almost every word you say. Thank you!

@LeilaGharani 2 жыл бұрын

That's great to hear, Carmen!

@badrm7620 2 жыл бұрын

♥️ i love you and your sound and your excel lessons. I liked xlookup too. Greetings from Arabia. ♥️

@user-qs3ro4zh6c 2 жыл бұрын

구글 알고리즘에 이끌려 새로운 재미난것을 배웠네요. 고마워요. 꼭 써먹어볼께요.

@primopen 2 жыл бұрын

Amazing post, thanks Leila!

@raffeuddin3522 Жыл бұрын

Hello Leila Gharani, thank you for sharing this information, its very simple & sweet explanation. Smile.

@roderickmose4691 4 жыл бұрын

Woke up this morning to find my Xlookup available. Mannnn am I rejoicing!!! Let the fun begin. whatttttt and an XMATCH! Boomshakalaka!!!

@LeilaGharani 4 жыл бұрын

Yay! Congrats. So many things to explore now :)

@blake1001x 2 жыл бұрын

Wow super thanks for this. Enjoyed this video indeed. Learned 5 techniques in less than 10 mins ;) thank you!

@wbtittle 2 жыл бұрын

Awesome. I have been using vlookup for a long time... This will greatly help the NA problem. Typeing if(NOT(ISERROR(VLOOKUP(...))),vlookup(...), default_value) has been my solution in the past... XLOOKUPs answer is so much better.

@user-yb2hr6ue1y Жыл бұрын

Clear explanation. very useful! Cheer.

@seanppeterson3874 2 жыл бұрын

Thank you, I have been using LOOKUP with IF statements and have issues when I sort. My work has finally updated from EXCELL 2007 and I thought I should see what's new.. These are the best tutorials I have found. Thanks for teaching this old dog new tricks.

@BradleyJTrychta 2 ай бұрын

Leila thankyou so much for all your videos. You are my goto.. Thank you for making me smarter.

@davesobani9565 2 жыл бұрын

Just wat I needed a quick look at the new syntax , thanks Ms Gharani

@ezequielbosch1321 2 жыл бұрын

Many thanks. As always, Crystal clear. Will be shifting to xlookup.

@ifcasiaparsiran673 Жыл бұрын

That was amazing and very much useful. Thanks Leila

@PhyllisSnyder-gu3eu Жыл бұрын

I never thought I would use this, but now I can't wait! Thank you!!!!!!!

@abbaskhalid5047 2 жыл бұрын

amazingly explained. thank you so much

@sanjayb212 Жыл бұрын

Thanks for the video. Really helpful

@ramitkhurana513 2 жыл бұрын

Xlookup is great! Thank you so much for this video.

@Kashyapdoshi 2 жыл бұрын

Thanks, two way xlookup was new to learn. It will be very helpful.

@joeym.3555 Жыл бұрын

Very well explained plus you give practice sheet, this is awesome work. keep it up :)

@masakalifghaltithunza27 2 жыл бұрын

Thank you for such a lovely and informative video..

@vijayanv5435 2 жыл бұрын

I love its XLOOKUP way of teaching the level ....!

@WindCutter93 Жыл бұрын

Yay, no longer need to write lengthy formula using Index Match. Thanks very much, Leila!

@82MMaarrkkuuSS82 Жыл бұрын

As always Laila.... awesome!!! You're my first option as soon as I have an excel doubt 😍😍

@gchathu11 2 жыл бұрын

Thank you! This was very useful session 😊

@darkmusic8212 Жыл бұрын

You are doing great job, thanks a million from Egypt.

@asirisam 2 жыл бұрын

Thank you very much for the great explanation.!

@vijayasrivatsa6869 2 жыл бұрын

Very informative sessions. I will definitely use this

@Gonefshn100 2 жыл бұрын

Thanks so much Lelia!! This really helped!!!

@jawamich Жыл бұрын

Excellent presentation very useful. Thanks a lot

@seernuruu Жыл бұрын

Thank you for very clear explanation

@kabengiolga3355 Жыл бұрын

Thank you so much Leila you are the best. ❤️

@kaushalchawla 6 ай бұрын

Hello,. Thank you. You make it look so simple!!

@m.irwannurhasim3708 2 жыл бұрын

I love this a lot, thank you Leila

@lucaspinho4714 Жыл бұрын

The best lesson ever, thanks from Brazil.

@junintosecario295 2 жыл бұрын

Thanks for sharing your knowledge. Really helpfull

@suryapothula3992 4 жыл бұрын

Earlier I use to stick to some set of formulas and do all my analysis based on those results.. lucky me randomly saw your video once (I don’t remember what was the first video I saw), impressed with the way you explain details about each argument made me to view more of your videos.. then made me to subscribe your channel.. I honestly thank you for sharing all these on KZfaq for free and educating many.. I strongly believe your videos helps lot of professionals to solve many of their issues with an ease.. you are unique of your kind.. wish you all the best.. my heart full thanks to you..🤝💐

@LeilaGharani 4 жыл бұрын

Thank you very much for the kind feedback. I'm really happy you find the content useful.

@Hejazi1365 2 жыл бұрын

Thank you Leyla, it was fabulous

@Nikles56 Жыл бұрын

I am definitely saving this video and will be using this!

@tanveerbangme 2 жыл бұрын

Very informative, thanks for the video

@lawrencehanson8552 2 жыл бұрын

Thank you Madam, your class is very useful

@jainpranay 2 жыл бұрын

Awesome Video. This is really helpful

@sahilmanocha Жыл бұрын

Thanks Leila, for this video. I did not understand two-way xlookup tough I found it useful, please make another video on it.

@waqaschaudhry975 2 жыл бұрын

Seems a very interesting formula. Going to try tomorrow and I hope I'm gonna enjoy it

@rishabhmittal123 4 ай бұрын

Thanks! Really helpful

@himanshuswami7048 Жыл бұрын

It was helpful. Thank you.

@nairobi203 11 ай бұрын

Loved it. Ok. the video is three years old and I am watching it now. In repeatng your steps, I included an offset to get the names of the employees sorted.