XLOOKUP in Excel | Excel Tutorials for Beginners

[@dinosaur5009]

Hi Alex! Been following your channel for nearly a year now and your videos helped me land a Junior Marketing Analyst role! It was from your videos where i learnt what skills i needed and how to showcase these skills through a portfolio. Just wanted to say a massive thank you :)

[@AlexTheAnalyst]

That’s so great! Congratulations on the new job! Keep up the good work :)

[@ermano5586]

Hey mate! How is it going? It was a year from your comment. Can you tell us anything that you met during this road, whatever this, problems, feedback or other stuff, please? It would be great for somenone who is non-stop watching this videos!

[@dinosaur5009]

​@@ermano5586 Hello, sure. 1 Year later I'm still working at the same place but I'm out of a Junior role now and working as a Marketing Analyst. When you first start it's initially a steep learning curve. These are the things I've learnt *Domain Knowledge is not required for an entry level role but it definitely helpful when conducting analysis. I found the more senior analysts at my company are able to look at the same data I was looking at but be able to get more insights out of it. I feel however this is mainly due to their experience as looking back a year from now I feel a lot more confident on what to look for compared to when I started. It's also best to ask look at and ask a lot of questions on other peoples analysis. This is the best way to increase your domain knowledge. *Don't feel the need to try and learn all the required technical skills at the same time. Before I entered this role I made it my goal to have exposure to all the possible technical skills I may need for a job. So i went out my way to learn the basics of Python, R, SQL,Excel,Powerbi and Tableau. Looking back that was a massive waste of time as my company only uses SQL & Excel mainly and were branching out to using Power BI. When I started, my role was just to do data pulls for the senior analysts which was an opportunity to get up to scratch on SQL. I invested alot my time into learning that skill and within a couple of months I became proficient enough to be able to do a variety of SQL related tasks. With this skill under my belt I was given additional opportunities to develop. I got moved to a team which did a lot of analysis which gave me the opportunity to focus a lot more of my time on Excel. With Excel and SQL under my belt now I'm now moving onto Python which Is something I always wanted to learn in depth. Point is in your only human so don't feel the need to get up to scratch in everything within a couple of months. Build up your technically offerings bit by bit. It's better to be an expert in a couple of technical skills than to have a basic understanding of all. *Be Resourceful when you are stuck. I've seen a couple of analysts join our company and were told they weren't a good fit after a couple of months. What I noticed was the people who were told to leave did not have good problem solving skills and didn't use their initiative in the slightest. When they were stuck they would just solely rely on other analysts for support. When you are stuck try to find ways to figure it our yourself first. For example when I was stuck why my SQL code was not working I would read the error messages and use it to figure out what the issue was. I would backtrack in the code to identify where the issues were stemming from. I would use resources on google when I couldn't figure it out and see if other people have had similar issues and how they resolved them. Point is showing initiative is a skill your manager wants to see and in general a good skill for an analyst. That doesn't mean never ask for help as asking questions is a great way to learn. It means have a good attempt yourself first. Hope this helps :)

[@ebubeawachie]

@@dinosaur5009I want to appreciate you so much for sharing this. So many times people are asked to update on a post they made and to share their progress and they don’t. You came here and left a treasure trove of very specific advise and anecdotes from the field. Thanks 🙏🏾

[@kalisa.]

Hey man, I'm a new student of data analyst, and wanted to know how your journey has been, have you seen growth in the field?, What's your salary, and what tips would you give a new person entering the field??.

[@georgeashton5424]

I’m soon to be starting my career as a data analyst for a marketing company in the new year and I wholeheartedly want to thank you for making these videos Alex. The information I’ve learnt from watching your channel has been invaluable during the interview process. Looking forward to the rest of the series and keep up the good work!

[@sj1795]

THANK YOU Alex! I've decided to write a gratitude comment in every one of your videos as an appreciation for everything you've done for all of us as well as offer feedback/thoughts on each video. I liked how you explained how the VLOOKUP function is used in Excel and the benefits of XLOOKUP/comparison to VLOOKUP. I can't believe all the time and energy you've put into this Bootcamp series. You are AWESOME!

[@clovisstanford6515]

excel not having suggestions of xlookup does that formula removed by excel.

[@JackKuoChang]

@@clovisstanford6515 Excel only provides for users with 2021 excel or those who use Microsoft 365. Sad.

[@frutonica.studio]

This is my SUM XLOOKUP using string manipulation to get substrings from "February - March". Hope this helps some others with same question in the head. =SUM(XLOOKUP(LEFT(B6, FIND("-",B6)-1), H1:S1, H2:S2):XLOOKUP(RIGHT(B6,LEN(B6)-FIND("-",B6)),H1:S1,H2:S2)) If "N/A" result, remember to check "February" if it has a space at the end. With basic programming background, to me, learning Excel nowdays is totally different from 20 years ago, LOL. Btw, thank you for videos, Alex.

[@Lakshmepw]

ummmm substrings ? i didn't really understand... like there are no substrings , there are number values that are corresponding to lookup array right... so.. maybe u can explain with a little example...

[@frutonica.studio]

@@Lakshmepw uhm, this is long ago, I take it a while to remember and re-watch video. Think as this: - You need to extract separated month text from “February-March” using LEFT for “February” and RIGHT for “March”. - Use separated month text to search in header row (H1:S1) to find the number values in row (H2:S2) (or other row depending on your choice). - Sum all the number values in lookup table between first number and second number. Honestly, after I seeing your comment, I’m a bit lost with my own comment as well. It’s because the “code” is too long and complex. In real life programming, we actually need to assign parts of code to variables and make the code overall readable and easy for maintenance or cooperation. For example: left_string = LEFT(…) right_string = … left_lookup = XLOOKUP(left_string,…) right_lookup = XLOOKUP(right_string,…) total = SUM(left_lookup:right_lookup)

[@harshilparmar4211]

Hey Alex, Hello from a former Microsoft intern! I really appreciate what you're doing and it would mean a lot to have a further deeper dive concepts in Excel for Data Analyst. Cheers!

[@acycliccoder7455]

Hey @harshilparmar4211. Could you please share how you got into Microsoft as an intern? Thank you.

[@rociob702]

GREAT video, you have a way of teaching a subject that might be difficult to some and put it in the simplest ways for others and myself to learn. Thank you!

[@monicabn3067]

Hey Alex, Learning alot from your videos, Your simplicity is truly inspiring.❤

[@AlexTheAnalyst]

Happy to hear that!

[@the_joerne_supremacy]

Not sure if someone else commented on this, but when you copy/paste formulas, you have to be sure the various cells AND ranges are still the ones you want to use. Like when you had to change the A3 to an A4, when you were wanting to lookup Pam Beasley in the first example after you pasted the formula from the Toby Flenderson XLOOKUP. If you look at the lookup_array and return_array in the Pam Beasley formula, those ranges shifted down, and I suspect IRL you want to continue letting the lookup and return start from Row 2. This can be solved by using absolute signs ($) in the cell references, but that might be a separate tutorial. Great videos!

[@adamsoluwafemi8774]

could be done by pressing F4

[@yqhanliving]

I used to use vlookup a lot and this video about xlookup has helped me think better on my solutions. Thanks!

[@SigmaCollege-ov4oi]

00:01 XLOOKUP is a new feature in Excel that replaces VLOOKUP 01:45 Using XLOOKUP in Excel for lookup and return values 03:39 XLOOKUP allows for returning multiple outputs in Excel. 05:47 XLOOKUP in Excel allows for exact match searching 07:52 Using wild card characters in XLOOKUP for flexible searches 09:45 XLOOKUP allows search modes like exact match and next larger value. 11:48 XLOOKUP function in Excel can be used to search for specific values. 13:52 Using XLOOKUP in Excel for searching specific values and adding them 16:01 Using XLOOKUP to search for a value and return specific data 17:53 XLOOKUP in Excel auto updates data from original array selection.

[@YolandaEzeagwu]

Alex, this video is a TREASURE! Thank you so much

[@lawaltaofeekolumide9527]

Awesome . I had check out alot of data analysis courses on youtube but your teaching sytle is the best

[@andreyblanket9298]

Hi Alex! There is a small issue in this video - when you drag the formula, the arrays go wrong. Though it doesn't affect the results in this particular video it might cause some problems in other situation. I really appreciate what you are doing! Wish you all the best!

[@julierenee6668]

You can stop it dragging the ranges down by entering $ in front of the numbers ie E2:E10 is written as E$2:E$10. The $ locks the position.

[@julierenee6668]

And if you're dragging across columns, the $ goes in front of the letter instead ie E2:P2 becomes $E2:$P2 & you can do both for good measure ie $E$2 etc

[@MissMayRandomposts]

​@@julierenee6668 Thankssssss❣️❣️❣️❣️

[@JR-xr4fc]

Was thinking the same. It's absolute vs relative cell referencing. Overly handy to know.

[@gpguri7645]

Thank you Alex, I am continuously making progress by watching your videos!🙏

[@rokibchi]

Starting This playlist from today, hope I can complete it ASAP. Thanks Alex for this.

[@farazahmed1668]

Thank you so much Mr. Alex for providing extremely useful content.

[@justinnacu]

The best teacher! the pacing is perfect even for beginners.

[@AkporHari]

Still going through these. Great tutorial again.

[@ChikoriTak]

Thanks Alex for an amazing course! I have a little comment regarding the formula. I think "$" should be added for a fix range. For example, in tab "XLookUp", the range H2:H10 and P2:P10 should be fixed. Therefore, the formula should be =XLOOKUP(A3;$H$2:$H$10;$P$2:$P$10), and then it can be applied to other below cells.

[@MousumiDeka-ip2lt]

In VLOOKUP did you try selecting column F to column O instead of column H to column O? I am getting an error

[@AbigailWaturi]

It's not working

[@1689JeffChavez]

On my journey through the boot camp... Learning XLOOKUP makes me think of issues that I could have resolved easier before with data. Cool!

[@aghthu7553]

I think we should use F4 or add dollar sign ($) to lock the range before double click to automate applying the function for below rows

[@db4313]

Crystal clear teachings as always Alex! One question pops up: In case of using xlookup with multiple return values: is it possible to choose in which cells the values will return? By default it returns the values directly next to eachother. But let's say you want an empty cell between each return value...?

[@nuriddintursunpulat327]

Now I get it after watching why there's extra address column on a sample I've downloaded where there isn't at the beginning of video :) Excellent tutorials, thank you shifu Alex!

[@whynot2288]

I wish I could just Ctrl C Ctrl V your knowledge straight into my brain.

[@davidniiayiquayefio]

Me too

[@Zaqhry]

Btw, will you be going over “Power Query” within this excel series? Also, thanks for the vids, they help a lot 👌🏽🔥

[@carlosvasco4103]

Hey Alex, thanks for the content. Just a quick notice regarding the XLOOKUP formula in 13:11. I believe you should have locked the month's array to be precise and correct. Fortunately, it turned out right anyway :)

[@tobechukwuonyekwere]

Thank you Alex for making these videos

[@AljIsHere128]

Ok I appreciate the breakdown understanding between XLookup and VLookup and how a pain in the azz with the VLookup doing updates can be. Let me go also do another video of the differences to so I can better understand. Thank U for this helpful video!!

[@liav4102]

I’ve almost entirely switched from index(match) to X lookup in most instances.

[@abdullahimaalim8345]

Hi Alex, Thank you so much for your excel tutorials. I was practicing on this template and I noticed something. The lookup array and the returned array keeps on changing as we move down looking up for more value. We did not run into error(#na) in the video because the items are arranged well. At first I ran into trouble but then I remembered the F4 lock. Locked the lookup array and the returned array such that it does not run into an error by searching empty array. Just wanted to share how exciting the experience was! Thank you.

[@MohammedAlabbani]

Legendary content sir, thanks alot, also loved the office reference with the names lol

[@jayashukla4799]

Hey Alex! How are you? Thanks a TON for these videos. I am trying to switch to data analyst and you are super helpful. Because of you, it seems doable🙂 I have just this one doubt though, to become a data analyst do I need deep knowledge of the programming language as I am not very good at coding🙈

[@echodelta7680]

I am doing exactly what you're doing at 04:57 in video, yet my Excel is giving me an error "A value used in the formula is of the wrong data type" when I hit ENTER after typing the formula. Please help.

[@helliotvillarroel2307]

Same here! :(

[@echodelta7680]

@@helliotvillarroel2307 ahh that's sad. Well i found Excel 2019 (& earlier) doesn't have XLOOKUP. So I switched to practicing in G-Sheets, then moved to Google Studio and did one small project of Studio+BigQuery

[@AlisaHof]

So is VLOOKUP obsolete now? Is there ever a reason to use it? Thanks for another helpful video!

[@S.KAKHTAR]

I think so

[@polokomonama6526]

Thank you so much for your content Alex!

[@ammarbushara8198]

Thank you man I did not understand vlookup from the google course ,but your explanation on point 👌🏽

[@mohammed-hananothman5558]

Great video Alex. I want to know what is the workaround if you want to return 2 or more values which match the search?

[@Bilkisu_]

Hello, When I typed in the XLOOKUP formular I kept getting NAME? as a result. Please how do I solve this?

[@PacklineSystems]

I would also like to know

[@estheroyelere9900]

@@PacklineSystems I have the same challenge also, please have you been able to overcome it? kindly put me through. thank you.

[@dashrathpurohit5872]

Great fan of yours, just 1 advice - Could you please start sharing data dump in description for our practice?

[@faiza-e-mahmud4551]

Hey Alex! Hope you're doing great. I was hoping, could you please reupload the "Microsoft XLOOKUP Documentation" file link. The current one shows there's some technical glitch.

[@hisokaamorou4211]

Tons of thanks sir!

[@enisusilowati8549]

Thanks for the video, but I have a question for the VLOOKUP part, why do I get #N/A result if I write the formula like this: =VLOOKUP(A5;E1:P10;12;FALSE). what's wrong with the table array range?

[@chrissarong9070]

Thank you very much Alex!!

[@yurobert3007]

Brilliant tutorial

[@sanikamhatre]

Hey Alex, XLookup is showing errors. Please make a video for latest excel version.

[@ganajoshuadanlami]

Thank you alex ❤

[@danirladaniel7352]

My best Excel function 2021 ...

[@lolao4642]

Hi Alex! Can you use the Xlookup by looking up the data from another sheet? Thanks.

[@helliotvillarroel2307]

Hi guys! , When I choose multiple columns as the return array, I get "#Value!" Error. Can someone help me?

[@cephtheobserver]

This might be a little late but i think the columns in his documents are not the same as the document we downloaded. E.g. The fullnames in his sheet are in column H while in the one i downloaded are in column I.

[@nasbidestroyerx]

XLOOKUP is not working in Office Pro Plus 2019. I searched and Microsoft says it is available only in 365. I am unable to figure out what is the alternative for XLOOKUP in older versions of Excel. Thank you

[@HBombdiggity]

I am also having this problem

[@prasannalondhe2415]

hello alex, when im using xlookup for multiple rows its showing value error. how to resolve it any 1 or alex please tell me. Thank you

[@donatusvictor3500]

Thank you Mr Alex for the tutorial, please my XLookUp for Multiple Rows is showing #VALUE! If i take my cursor to the cell it will display "A value used in the formula is kf the wrong data type". Please i need help. Thank you.

[@charbelberkachi9963]

Hi Alex Hope you are well In a lot of videos, you talk about SAS. Can you please make a video explaining what is really SAS, and what is the right course for a data analyst.

[@AlexTheAnalyst]

I definitely will do that 👍

[@veronicab2096]

Yes, I’d love you to cover SAS in one of your videos, too. Thanks!

[@jaafarsadik2588]

My Excel don't have XLOOKUP it's just LOOKUP, when I try multiple rows and Exact match it doesn't work. What souled I do ????????

[@shokoofehheidari5345]

Thanks a lot Alex!

[@metofz-kr6yl]

well explained. thank you

[@NixonBranch]

Thank you Alex, I am continuously making progress by watching your videos!. The best teacher! the pacing is perfect even for beginners..

[@banamailat_BM]

Hi Alex, Thank you for helpful videos. I learnt lots of things from your videos and still learning. Just ask a question: Cause I don't know if it's in front or behind , can i use " "*"&A5&"*" " statement for exact match?

[@HimanshuPakhale-n3i]

its easy to understand..

[@kayodeolanrewaju5459]

i tried the xlookup with multiple rows and it gave me an error. that a value used in the formula is of a wrong datatype

[@ednaukaegbu5978]

Hi Alex. I'm having difficulty downloading the Excel template for practice

[@gaoussoudoucoure4424]

For those having error on the XLookUp Multiple Rows, someone here commented "add ( & ) sign between the two outputs you want like this (=XLOOKUP(A3,I2:I10,O2:O10&P2:P10) and press Ctrl+Shift+Enter at the end". That returned the date and email for me, but it put them both in the same cell instead of separate ones like in the video

[@cookingdivastyle927]

I understand, I’m just unsure how is this feature used in the field?

[@beaut11]

Hi Alex, I'm having a challenge with "XLOOKUP Multiple Rows" . I've followed you step by step but I keep getting "#VALUE!" as my output. Please what do you think the issue might be? Thanks.

[@vivekfrancisxavier7000]

Hi , try with excel online version .. it works fine there 🙌

[@nomatusi7514]

Me too

[@BijoyPantu]

Hello Sir, In every single time when you used drag for the next names to auto fill-up there is a single mistake. As you drags down the range array is also changing by 1 row. so after any random search at the second time if we search for the first person we will get error or the message "Nor found" as the range is now changed. And for that we need to fix the range and return array values so that it searches within the same exact column and do not misses any upward values. Am i Right? I'm a big fan of yours as what you did is awesome. Please reply if possible.

[@yusuf8803]

the formula =XLOOKUP(A3,I2:I10,O2:P10) is not working for me, it's showing me an error message "#value!"

[@abdelrahmanmoslem1172]

add ( & ) sign between the two outputs you want like this (=XLOOKUP(A3,I2:I10,O2:O10&P2:P10) and press Ctrl+Shift+Enter at the end

[@yusuf8803]

@@abdelrahmanmoslem1172 thank you so much

[@gaoussoudoucoure4424]

@@abdelrahmanmoslem1172 Thank you, this was starting to stress me out, I will repost the comments for people to see it more. But it put the date and email in the same cell instead of separate cell. Do you know why?

[@shankars8211]

Hi Alex.. your videos are really helpful. But unfortunately, i am unable to find the xlookup, filter and query functions in my excel which i guess is a 2019 version. could you please help me how to get those functions for practicing?

[@chhayachouhan7249]

At "10:22 second", for Match mode=1, why the output is 1/5/2000 when the next larger value to 1/1/2000 is 7/4/2000? (considering that the date is in dd/mm/yyyy format)

[@Deekshi-ej8wc]

Thanks a ton Alex

[@SMIT_xx]

Hey Alex, I am using Excel 2019, and I am not able use XLOOKUP function , help out how can I do, operations related to XLOOKUP

[@dataScineceEnthusiast]

**Help** I got #value! Error when using xlookup multiple rows when the result range is o2:p10 Also it doesn't work with "not found" with exact match

[@theologosikonomou72]

Hi Alex thank you for your great videos. I would like to ask, is VBA useful for a data analyst?

[@jush4299]

my xlookup when i press enter @3:40 it returns #name? kindly help

[@jaberabedin5487]

Awesome stuff as usual Alex! One quick question: At 12:22, shouldn't the function be =XLOOKUP(B1,H1:S1,H2:S2) instead of =XLOOKUP(I1,H1:S1,H2:S2)? (I'm referring to the very first argument in the function - the lookup_value, being the February in cell B1 instead of cell I1 )

[@Kavi-learn]

the first argument represents the value we search by (here it is 'February') . Since February is in I1, =XLOOKUP(I1,H1:S1,H2:S2) it correct

[@HanifAyatullah]

The xlookup formular isn't working on my excel, is there like a particular version of excel to use for it..........vlookup is working tho

[@devgothwal]

can you make the video about your online presence and networking with peoples online

[@extrov1]

Thanks a lot Alex, it was useful , but do i have to attend udemy course or it is enough?

[@kathkeyyyk]

hello i hope you could also make a vid for if and or not.

[@krishnathanki999]

THANK YOU.

[@olaniyidadasamuel2882]

@Alex I will want you to recommend me course I can buy to start my analyst career. Thanks

[@Sattya1412]

SIR WE NEED SOME XLOOKUP VIDEOS ON LOOKING VALUE FROM 2 DIFF SHEETS,WORKBOOK ALSO

[@Storytimehouse22]

Alex is it possible to do all this using my android phone or a laptop is a must?

[@penumalasreekanth-yl1dd]

hai mr alex i have a doubt i am not able to use xlookup in my pc can you please tell me hoe to use it

[@dewxdrop23]

I can't find an XLOOKUP formula in Excel, I can only find LOOKUP. And it doesn't have the same description when entering tap. Any idea why?

[@emekamonyei2158]

Xlookup multiple rows is giving error message when I try it. What could be the problem

[@sohailomeromer4554]

sir i can't see any XLOOKUP option in my excel, so what should i use instead of this XlookUp.?

[@rahimayma9940]

me too! there's no xlookup at my excel?

[@kingjougar4203]

Same here. Can someone pls help

[@riddhitandel7471]

Yes same there's no XLOOKUP function in my excel

[@gbodogberachel2391]

Same here. I can’t find xlookup function on my excel😢

[@imadethiscuziwsbored]

Darn ill have to wait till i can get a newer version of excel cuz i guess 2019 doesnt have this function

[@Neet-ft3qp]

HII Alex do we not need VLOOKUP for Data Analysis?

[@mitoali5151]

I really can't understand that xlookup exact match for beasely and Meredith. Could someone explain it to me in a simple way please.

[@mohammedafnan7231]

Hello, I’m not getting the Xlookup formula in my Excel

[@adwaamohamed2674]

THANK YOU

[@yaelcohen118]

the xlooup didnt find multiple results and i couldnt find out why, please help!

[@uchechukwumazi6512]

Please this is urgent. I am on a train5 path and transition into data analytics. I hav learnt sql, pivot tables, power query, power pivot, power Bi, numpy, pandas in python for Dataanalytics. I want to dive into advanced excel but materials are just too much. The whole mcode in powerquery etc. please I need help! Can you advice me on the only things I need to cover and that is important in advance excel. The only topics in advance excel that I will be needing as a data analyst .

[@viktoriaballa4622]

DAY-5, yeeey

[@sametmutlu837]

is it meaning than we dont need if anymore? felt like xlookup will make we will not need to use if maybe even ifs. What do you think?

[@AlexTheAnalyst]

IFs are still really useful, xlookup wont replace them

[@onomeebe]

Thank you

[@gomojo1024]

ohhh i love you thanks

[@_avisekh]

Hi Alex, Your first video in this playlist is hidden. How to access that?

[@AlexTheAnalyst]

It's actually just queued up to be released next Tuesday - it'll become available then

[@mouhamedfaye4685]

i can't found this function XLOOKUP over in my excel how can i add it ?

[@Neutrino-xw6jl]

What do you use to record your videos?

[@AlexTheAnalyst]

OBS open source software 👍