XLOOKUP in Excel | Excel Tutorials for Beginners
ฝัง
- เผยแพร่เมื่อ 2 มิ.ย. 2024
- Take my Full Excel for Data Analytics Course! www.analystbuilder.com/course...
Excel is one of the most used skills in the data world. In this series we will be walking through all of the most important topics that Data Analysts need to know in order to be proficient in Excel.
Excel XLOOKUP Template: bit.ly/314BWFV
Microsoft XLOOKUP Documentation: bit.ly/3mF3dGx
Udemy Excel Courses:
Pivot Tables in Excel: bit.ly/3peELgG
Visualizations and Charts in Excel: bit.ly/30MU9HT
All Excel Essentials: bit.ly/3Fdk6iG
____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.io/5bBd62
Data Analysis with Python - coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
Tableau Data Visualization - coursera.pxf.io/MXYqaN
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
SUPPORT MY CHANNEL - PATREON/MERCH
Patreon Page - / alextheanalyst
Alex The Analyst Shop - teespring.com/stores/alex-the...
____________________________________________
Websites:
GitHub: github.com/AlexTheAnalyst
Instagram: @Alex_The_Analyst
____________________________________________
0:00 Intro
0:59 XLOOKUP Overview
4:14 XLOOKUP w Multiple Rows
6:16 XLOOKUP Exact Match
9:29 XLOOKUP Search Order
12:12 XLOOKUP Horizontal Search
12:55 XLOOKUP w SUM()
14:51 XLOOKUP vs VLOOKUP
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
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 :)
That’s so great! Congratulations on the new job! Keep up the good work :)
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!
@@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 :)
@@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 🙏🏾
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??.
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!
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!
Thank you so much Mr. Alex for providing extremely useful content.
Still going through these. Great tutorial again.
Alex, this video is a TREASURE! Thank you so much
Starting This playlist from today, hope I can complete it ASAP.
Thanks Alex for this.
The best teacher! the pacing is perfect even for beginners.
Thank you Alex, I am continuously making progress by watching your videos!🙏
Btw, will you be going over “Power Query” within this excel series?
Also, thanks for the vids, they help a lot 👌🏽🔥
Awesome . I had check out alot of data analysis courses on youtube but your teaching sytle is the best
I used to use vlookup a lot and this video about xlookup has helped me think better on my solutions. Thanks!
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...?
Thank you so much for your content Alex!
Thank you Alex for making these videos
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🙈
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!!
On my journey through the boot camp...
Learning XLOOKUP makes me think of issues that I could have resolved easier before with data. Cool!
Hey Alex,
Learning alot from your videos,
Your simplicity is truly inspiring.❤
Happy to hear that!
Thank you very much Alex!!
Tons of thanks sir!
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!
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.
Thanks a lot Alex!
Thank you man I did not understand vlookup from the google course ,but your explanation on point 👌🏽
well explained. thank you
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 :)
Great fan of yours, just 1 advice - Could you please start sharing data dump in description for our practice?
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!
Hey @harshilparmar4211.
Could you please share how you got into Microsoft as an intern?
Thank you.
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!
excel not having suggestions of xlookup does that formula removed by excel.
@@clovisstanford6515 Excel only provides for users with 2021 excel or those who use Microsoft 365. Sad.
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.
My best Excel function 2021 ...
Legendary content sir, thanks alot, also loved the office reference with the names lol
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?
Brilliant tutorial
Hi Alex! Can you use the Xlookup by looking up the data from another sheet? Thanks.
Thanks a ton Alex
Hi Alex thank you for your great videos. I would like to ask, is VBA useful for a data analyst?
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.
Thank you Alex, I am continuously making progress by watching your videos!. The best teacher! the pacing is perfect even for beginners..
THANK YOU
So is VLOOKUP obsolete now? Is there ever a reason to use it? Thanks for another helpful video!
Thank you
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.
I wish I could just Ctrl C Ctrl V your knowledge straight into my brain.
Me too
Thanks a lot Alex, it was useful , but do i have to attend udemy course or it is enough?
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!
could be done by pressing F4
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?
ohhh i love you thanks
@Alex I will want you to recommend me course I can buy to start my analyst career. Thanks
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!
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.
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
@@julierenee6668 Thankssssss❣️❣️❣️❣️
Was thinking the same. It's absolute vs relative cell referencing. Overly handy to know.
Alex is it possible to do all this using my android phone or a laptop is a must?
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 )
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
I’ve almost entirely switched from index(match) to X lookup in most instances.
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.
Same here! :(
@@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
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?
thanks
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.
Hi Alex. I'm having difficulty downloading the Excel template for practice
can you make the video about your online presence and networking with peoples online
Hey Alex, XLookup is showing errors. Please make a video for latest excel version.
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
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.
I definitely will do that 👍
Yes, I’d love you to cover SAS in one of your videos, too. Thanks!
hello i hope you could also make a vid for if and or not.
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 .
Hi guys! , When I choose multiple columns as the return array, I get "#Value!" Error. Can someone help me?
**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
DAY-5, yeeey
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
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?
IFs are still really useful, xlookup wont replace them
Hello, When I typed in the XLOOKUP formular I kept getting NAME? as a result. Please how do I solve this?
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
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
I am also having this problem
cant i just use sql and select their email and end date?
the xlooup didnt find multiple results and i couldnt find out why, please help!
SIR WE NEED SOME XLOOKUP VIDEOS ON LOOKING VALUE FROM 2 DIFF SHEETS,WORKBOOK ALSO
Hello,
I’m not getting the Xlookup formula in my Excel
my xlookup when i press enter @3:40 it returns #name? kindly help
hi, you did't not provide the your LinkedIn link.
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.
Same boat here...😂😂
Hi , try with excel online version .. it works fine there 🙌
Me too
What do you use to record your videos?
OBS open source software 👍
Hi Alex,
Your first video in this playlist is hidden. How to access that?
It's actually just queued up to be released next Tuesday - it'll become available then
Hi Alex
Please i need a more deep dive into different lookup functions. Would you be kind enough to send me some Udemy courses you’d recommend that’ll really be beneficial to me?
i can't found this function XLOOKUP over in my excel how can i add it ?
the links doesnt open sir
The file for this tutorial isn't downloading
Hi Alex, thanks for the wonderful content you putting out here, means alot.
However, the use of wildcard you did was on a smaller dataset, where it was easy to notice where the lookup value does not correspond with the value in the lookup array.
What if we are working with a larger data with thousands of rows? Is there like a logical way of setting xlookup to search for the return value? like in the case of Flender/Flenderson and Meredith/Meredith Palmer where the "*"& where placed before and after the lookup value referenced cell, Can a single formula be written to return the needed result if the condition is met? I have been trying to come up with something, I'm not getting it.
There is, but can get complicated. Look into something called RegEx in Excel.
Hi, can anyone explain why we are doing Xlookup with sum because we can directly sum the two cells without using Xlookup.
Just for demonstration purposes lol
For the section of XlookUp-Multiple Rows, anyone getting spill error for end date?? Please let me know..
I can't download the cookie document from github
Hi, I am getting this error #NAME?
getting #value! & #N/A error. how to solve this ??
Hello All, How to update the Excel 2016 version to the latest? I cannot see any updated menu in my Excel. The reason I am asking is because I can't use XLOOKUP ? Any body has a similar problem?
can you do vlookup
5:00 when i use this same exact formula, it doesnt work. it just says #N/A
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
when i do the xlookup for multiple rows or anthing with a date in it i get a '#value!' why?
how do I fix this?
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
sir i can't see any XLOOKUP option in my excel, so what should i use instead of this XlookUp.?
me too! there's no xlookup at my excel?
Same here. Can someone pls help
Yes same there's no XLOOKUP function in my excel
Same here. I can’t find xlookup function on my excel😢
the formula =XLOOKUP(A3,I2:I10,O2:P10) is not working for me, it's showing me an error message "#value!"
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
@@abdelrahmanmoslem1172 thank you so much
Why xlookup isn't working now today 2024
I am getting an #VALUE error in xlookup w multiple rows...can anyone pls help.
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