How to Use XLOOKUP in Microsoft Excel

Learn how to use the Xlookup function in Excel. Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.
If you want to run through the examples featured in this video, you can find the original worksheet that I used here: 1drv.ms/x/s!AmxrofZZlZ-whIdjz...
Microsoft support article on xlookup:
support.office.com/en-us/arti...
To learn more about using Excel, watch the following videos:
- Pivot Tables: • Pivot Table Excel Tuto...
- Vlookup & hlookup: • VLOOKUP & HLOOKUP in E...
- Mail Merge: • How to Mail Merge in W...
Watch more Excel tutorial videos: • ❎ How to use Excel
- Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
As full disclosure, I work at Microsoft as a full-time employee.

[@KevinStratvert]

🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com ⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: kzbin.info/aero/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY

[@taylorcrose8287]

Bruh. I was just SHOOK by this video. Like where is my data WHERE. I’m dying to login to work tomorrow. Forwards and back... auto results for my #N/As you gotta be SHITTING ME. You are my people. Microsoft is lucky to have you because you’re now my new superhero. God speed.

[@jamielindley5123]

Great tutorial, simple and clear. Initially watched in full, went away and created my own data set, then worked along side whilst watching the video again and it worked. Many thanks.

[@MarsorryIckuatuna]

Best Excel tutorial on this function. I've heard about it before - but never thought much about it. Great video Kevin 👍

[@ibm.hansen5714]

Great simple and focused explanations (so now after 17 minutes - I fully understand the nexted Xlookup ecample - thank you !

[@janebrodie5133]

Kevin you are amazing - I wish I’d found you earlier in my career, I’ve learnt so much from you during lockdown. Keep the videos coming. Love to see one which helps you to build a performance dashboard please 👍

[@IncludeAkademia]

Thanks for this video. Many people do not even realize what a huge analytical tool Excel is.

[@kathya6313]

the formula I need was in min 5:35, thank you for the update!!!!

[@ExcelTutorials1]

Not going to lie, I love the XLOOKUP function! Its amazing how your return results can be to the left of what you are looking up!

[@neilathome]

Thank you, Kevin. Very helpful and easy to follow along.

[@kenlagat833]

Amazing work Kevin, you have made my data analytical life so ease. Thank you!

[@JK-jm6kd]

I work with data all day thank you.

[@Snowblaze_]

Incredibly useful and easy to understand video! Thanks, mate!

[@annannb]

Thank you, Kevin! You are so generous with your information and knowledge of Excel and you explain instructions very well. Please keep it coming. Thanks again!

[@KevinStratvert]

Thanks for the note :)

[@jonhouston87]

@@KevinStratvert Hey Kevin, great video. Just wondering if you've ever heard of the BetterLookup add-in? It's like VLOOKUP, HLOOKUP, and XLOOKUP but allows for multiple search criteria and it returns multiple values. You should review it. I bet they would even give you a free version if you contacted them and did a video.

[@redxsling8883]

thanks Kevin this is another way I use on my PC.

[@shangds75]

I find this video is so much helpful and thank you for your very clear explanation on XLOOKUP.

[@jacqueszakhem3187]

LOVE THIS! A massive time-saving function!

[@ntashatheomnipresent4969]

Kevin u give invaluable pieces of information in A very comprehensible way .Amazing ! Thanks

[@patrickmcardle4511]

Hi Kevin. Thanks for putting these tutorials together...I have really learned a lot from them. I have heard that the Office products have the ability to save custom colors to the color picker palette but nothing I’ve found online makes sense. I’ve seen people talking about creating new themes but I thought there was a way to do it without a theme. It would be great if you could do a tutorial on that. The downside is that I am still using The 2010 version of Excel, Word, etc...

[@generationalwealthcreators74]

This is a great video. I love all the illustration and creativity that you performed with this Excel function. Please keep it up. Thank you so much for this brilliant presentation.

[@CS-bl2sz]

The nested x-look up is just so fancy!

[@That_AboElHamd]

Thank you so much. This was really comprehensive & Helpful.

[@roberth.9558]

Thanks, Kevin. Very clearly presented.

[@emisiko]

Highly highly hiiiiiiiiiiighly appreciated for this tutorial !!

[@FutureXT]

You're simply thee best Kevin! 🙏💯🙌🏼

[@philiplbrown]

Very well presented, concise and thorough. It would help if you could zoom in on your worksheets so we can see the entries being made.

[@rothensexploit1815]

Thank you Kevin. We use spreadsheets all the time at work. Our accounting system is an old database style, so the information is usually raw data dump and then we use Excel to define the data. My supervisor is the one that has to create the XLOOKUP tables because, until today, I didn't quite understand how they were done. You have just turned on the "Light Bulb" in my head. Simple but very effective! Thank You!!!!

[@shanea38]

Excellent tutorial - thank you.

[@majdialhurimy3451]

Thank you for this amazing lecturer. I Like your warm voice.

[@KevinStratvert]

Thanks for the note Majdi :)

[@cindyadriennelumbres6086]

Amazing! I use Index, match and match for the last function you did, it's pretty similar :)

[@garylillich]

Thank you for sharing. Easy to understand a fairly complex function. Now it replaces VLOOKUP and HLOOKUP. I have found VLOOKUP to be a very essential function for years. But I am curious, should a beginner learn VLOOKUP before XLOOKUP, or bypass it?

[@salimk4874]

wonderful videos, easy to follow and to learn from in such short time...kudoo to you

[@VarunKarthikSG]

What I'm gonna do is, I'm gonna like this video and subscribe to your channel because I found it very interesting and useful..!

[@saleet1000]

Very easy to grasp, great

[@leonardogranados2733]

Kevin, thanks for your videos, they are helping me a lot in my job. I have 3 different Excel books with similar info (HHRR) but some names could be in one list but not in the other; kindly which function works best to find those differences ?

[@budsayadejpun3821]

Thank you so much I would like to know how to use Xlook up from different sheet

[@muhammadshehzad1622]

Hi Kevin Stratver Thanks for all the tutorials and my question is can we use xlookup function to get the only minimum range and maximum range from an array or table?

[@rithmignanathilake7928]

Thank you so much. Very helpful ! :)

[@BilluTube]

It's just an amazing feature thanks Kevin.

[@jayantgupta3037]

Hey kevin ! I just shared your video to some users. Thank you for making this here! Biggest fan, This is Jayant

[@charlene5940]

Thank you! Do you have a check list where items can be tracked? I would like to be able to track my educational journey for each subject matter?

[@jkederyte]

Subscribed, thank you!

[@mohammadsulaiman8748]

Thank you, I am learning a lot from you. Can you do VLOOKup and Xlookup using larger data?

[@jaymaharjan997]

Thank you, Kevin !!

[@AmitSingh-ej4iz]

Hi, Kevin Thanks for sharing. I tried this personally and it's awesome however can you throw some light in case we have two exactly the same lookup values in data. thanks in advance

[@19761999]

Dude, you rock! Thanks

[@nissan9169]

Great stuff thanks

[@Rubenvaldo]

Really helpful. Thx!!

[@manhaandmahirahossain8683]

Thanks so much Kevin.

[@mcveemanalili2984]

very helpful on the data base that i want to develop for our school. thank you

[@paulsavastano8139]

Kevin. Just watched your video. Great stuff, nice cadence with clear speaking. One request, can you please make your data/screen bigger. A bit small to read you data. Thx again

[@doge8178]

loved it... so useful.. i am going show off at work now 😁

[@bridgetkoester8124]

Thank you Kevin. Can XLOOKUP be combined with an IF statement and then perform a calculation? What is the proper syntax? Thank you.

[@lexuannhi]

You're great. Have you done a section about macro or excel script. I would love to know.

[@ntm3051]

Hey Kevin the video is awesome. Can you make another one on how to add or enable xlookup on excel please ?? Thank you.

[@mrcaleb7728]

Cool vid!

[@plewisto]

Very good info.

[@stopdrinkingleftskoolaid6510]

Hi Kevin. Your videos are really great. This was a great example but it’s really hard to see because the view is small.

[@solinsaadi1886]

Thanks a million

[@tizme2311]

Hello Kevin, I think your videos are exceptional, have perused your Playlist link, what would be usefull is if they could be annotated with when the age of the posting (ie: 6 months) etc.

[@ibrahim82649]

@Kevin Stratvert Thanks this was very informative, when i think of it xlookup sounds and works better than vlookup.

[@ManCalledHorse]

Hi! Noobie excel user here. Other than copying and pasting a column/row from one spreadsheet onto another (within the same workbook), how can I import/transfer over that data into a spread sheet with blank fields? Basically, I want to be able to use a sheet as where I enter my raw data (copy and paste from an outside source) and then other sheets automatically organize and calculate the data. Thanks in advance!

[@xlwatcher8885]

that was just amazing. So informative about the intricacies of xlookup. Could you comment on becoming and Insider using Beta Channel or Current Channel (Preview)? What does each mean? Are there any risks? I am impatiently awaiting the new check box feature for cells on the insert tab. Thank you so much

[@fahimgodid4221]

Thank you mr ٠

[@siddhantgattani1749]

Kevin, you should teach courses on Udemy about Excel, ppt, windows power user bcoz you have the ability to teach diff things in a simple manner... And now after leaving Microsoft, you also have enough time to make a Udemy course.... Love your tutorials always!!!

[@Eternal_Tech]

I would much rather Kevin teach here on KZbin than a pay site like Udemy. Why would I want to pay Udemy when I can watch the tutorials here on KZbin for free?

[@rozerantasdelen7376]

just amazing

[@georgelau6859]

Hey Kevin, as much as I liked your video. Can you share which USB mic you use for recording...? result is pretty good.

[@youssefghazi4040]

a5ooya kevin stratvert shokran 3al mosa3dah

[@waros120]

I think in the "xlookup nesting" index match combination is also good

[@margaretteelainegonzales3388]

Hi Kevin, can Xlookup can be also combine to other function to perform some computation? Thank you and Kudos

[@joen5000]

I was asking myself whether it's possible to use Xlookup in order to find the location of certain values in within a field rage, but I think, I should think of a different method.

[@brandonchiu4422]

what happens if you have more than one depending cells to one input cell, eg. The same student might have more than one majors and on the excel data entry, it might have exact same name corresponds to two different majors. In this case, how do you use Xlookup when you search the student name, It brings up both of the rows and says both of the majors. I hope this all makes sense

[@shaileshtrivedi1218]

Hello Kevin, for some simple solutions you are the best . mostly it is helpful in my job also. One interested thing came in my work. I wants to highlylight only few font in a cell based on other cell. for example. if I have 2356 in one cell and at another cell, wants to look ig "2356" is in the cell , if yes, need only "2356" # in TED, not all text or number in the cell, can you make one video, please

[@Paladin101]

Thanks for this. Enjoy your videos but they could be better if you remove all the whitespace on the page in post production. It makes it easier to read on tablet or iPad.👍

[@sandeepshakya6747]

Replacement of Index and Match for left lookup values .

[@najeebzahid1915]

Hi Kevin , can u pls explain how to apply 10% loyalty discount in a very long list , to specific clients of only X company or X only X area

[@jayantgupta3037]

Super video! I applauded for ₹40.00 👏

[@KevinStratvert]

Thanks so much, Jayant! Much appreciated 🙏

[@garylillich]

What is the best way to make suggestions to MS for future enhancements or improvements?

[@dsulvadarius]

Very Neat.

[@anuragmittal18]

Hi Kevin, thank you for the video and I have a question. After applying Xlookup formula successfully to one row I want to replicate the same logic to other corresponding rows, how do I do that?

[@reidsexton2869]

Hey Kevin, I haven't used the xlookup function yet. It seems to be a streamlined version of using an "Index, Match" function. Was this the inspiration?

[@KevinStratvert]

Yeah, index match serves a similar purpose. The downside of index match is that it requires nesting and that's confusing for most users with all the brackets. It also requires a sorted list by default. Xlookup is really the best of vlookup, hlookup, and index match combined into a new function :)

[@sarojpatra8306]

Can it handle duplicate entries?

[@rodpablo9324]

Is there a manual with examples with multiple criteria, and spill

[@brendamartinezgomez2297]

amazing

[@adjoaaj7593]

game changer!

[@emarquezd]

excelent

[@lachlancarrigan1344]

Can xlookup return a collated value from multiple sheets? Months across the top, products down the left col, and values in the correct cells?

[@sarahlove4216]

Is it possible to use xlookup to look for the student's major and then return all of their information on a different tab in the same sheet? For example, I noticed in your example there were several people who had a computer science major. If I wanted to send a list of all the CS majors to a teacher with all of their other information included (not just one value), how would that be done?

[@estrellaneie1543]

WHATS 3 things Y’all LEARNED IN THIS VIDEO? :)

[@rcain4701]

Nothing

[@lindseyforeman2010]

Braeden Harthoorn same lmao😂

[@connerpewitt1801]

Estrella Neie Why we doing this😂

[@blkzumba175]

1. i need to slow down and listen. 2. this isn't has hard as I thought. 3. i will save so much time just by doing this formula.

[@garylillich]

XLOOKUP should save me a lot of time, since I transfer several columns of data. One challenge, how do we get the fill handle to work on the first formula? In my Office 365, cannot double click to fill the column. Ideas?

[@pavankumar-pf5iq]

If we not have a space between first name and last name, how we can make this work

[@ehsanali89]

Can you make a video about all Exel errors, because now with combined columns I have an Error reading #Value! and I'm stuck

[@jaredthomas9246]

Show how to use this on tables where one of the criteria changes column in table

[@estebanserrano6568]

I have a user table with first and last names, no badge numbers, and I have another list with first and last names with badge numbers. is there a way for me to look up First+Last names to find badge number?

[@alejandro12613]

Hi, i just want to ask, I don't have Xlook up function and =Sequence formula , how activate or show them up??

[@naeemullah3089]

Which version of Office includes this function?

[@tarigeltayeb413]

how to make xlookup sort results in column?

[@aslkha3788]

Hi sir vlookup formela in one id desplay with 3 & 4 down rows

[@carlchristopher2534]

How soon is soon I've been hearing all these wonderful features coming for 6 months now but still only available to the Insiders

[@KevinStratvert]

Hey Carl - it'll be coming to Office 365 subscribers. XLOOKUP is currently available to users enrolled in the Insiders program. There's no final release date yet. XLOOKUP will be used widely and it needs to meet a high-quality bar before being released broadly.

[@KevinStratvert]

quick note - it was just announced at the end of August techcommunity.microsoft.com/t5/excel-blog/announcing-xlookup/ba-p/811376 here's the official note on timing: "XLOOKUP and XMATCH are available for users signed up for the Office 365 Insiders Program starting today and will continue rolling out to Insiders over the next few weeks. Gradual roll outs allow us to gather feedback and ensure feature quality."

[@carlchristopher2534]

@@KevinStratvert Hey Kevin thank you for the update but the link didn't work at least on my phone haven't tried it on my laptop yet

[@carlchristopher2534]

@@KevinStratvert Hey Kevin once again thanks for the replies but still no firm release date I can't believe it take longer than 6 months cuz I've been hearing of the release for 6 months now but anyway I don't know if I can get signed up for the Excel insiders and get a copier lie maybe of a total release

[@KevinStratvert]

Hey Carl - sorry, included extra characters after the URL above. I've corrected this. You should be able to sign up for Insiders via File > Account and then sign up for Insiders. Here's also the Office Insider web site: insider.office.com/en-us/ Once you're an Insider, you'll be able to start taking advantage of xlookup immediately.

[@PritomAhad]

so the question is as such i have a coloum with names of sports so for instance “cricket” “soccer” “chess” “ice hockey” “car race” they want me to use a function so that it’ll show first four words of the single word sports and for the “ice hockey” and “car race” Itll show “ first three letters of first word and first letter of second word” but they want me to use “if” function for it. The coloum that has the names of the sports (both single word and two words” is labelled “sport” and i have to use “if” function, also they said if they add a new name be it “one word” or “two word” sport, the formula i use should produce the desired result? So for if function how to let the system know that the sports with single words should show first four letters while the ones with two words should show “first words first three letters and second words first letter”