The Ultimate XLOOKUP Tutorial (The Best Excel Formula)
HTML-код
- Опубликовано: 19 июн 2024
- Xlookup formula tutorial in Excel using 5 examples.
👉 Take our Excel Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: careerprinciples.myflodesk.co...
Learn how to use the XLOOKUP function in Excel with 5 examples from easy to hard. First, you'll learn how to use a simple XLOOKUP and replace a VLOOKUP. Then, you'll learn how to use the XLOOKUP with a wildcard for approximate matches. Third, you'll learn how to use the xlookup with multiple conditions to replace the index match. Fourth, we'll look at approximate matches using the match mode for matches that are smaller than a certain number. Finally, we'll look at getting multiple answers with the spill feature in XLOOKUP. Once we understand the xlookup, we'll look at some of the limitations of the formula, especially with summing values.
LEARN:
🔥Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - XLOOKUP Example 1
1:57 - XLOOKUP with Wildcard Chart
3:39 - XLOOKUP with Multiple Conditions
6:08 - Match Mode XLOOKUP
8:13 - XLOOKUP with multiple answers
9:42 - XLOOKUP problems
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
👉 Take our Excel Course: www.careerprinciples.com/courses/excel-for-business-finance
I have always wanted to take an advanced excel/google sheets course. I have used spreadsheets since the old lotus 123 days. I use the count features a lot at work but don't know how yet to group and automate. however. I am learning pivot tables to help me with reports
You are the man Kenji. I've worked in accounting for 7 years and consider myself very proficient in excel, however you've taught me so much in the few short videos I've found. New subscriber!
I love how you clearly explain how to apply formulas .....sending love from Kenya
I would really watch your excel classes for the whole day!
You really make the formula so easy to understand and the places where it could be probably used.
Thank you so much!
Thanks for your clear, direct and informative instruction. I have subscribed.
4:55 Very informative! So the inner xlookup returns a column from a group of columns, while the outer xlookup returns a cell from a column (group of cells). 9:14 or return a row from a group of rows.
brilliant. thank you so much Kenji for the tutorial video! love from Malaysia
Very informative. Thanks.
Thanks for mentioning the shortcuts everytime you use them!
You are such an amazing tutor.Thankyou so much.I subscribed
Thanks for making such wonderful video.
Yes, I agree. One of the functions I use daily
Super video yet again!!!
We appreciate you, keep on good work
From Nigeria
💯/ 💯 brilliant..Bro Kenji💪Dubai
I am in a bootcamp for data analytics and I found this video so helpful
You are amazing man!
Thank you very much.
Thank you
really helpful to explain this by you kenji
glad it was helpful!
Hi Kenji,
This was an excellent refresher of the XLOOKUP function for me. Thanks for all your efforts. 👍
For the Limitation example can we also make use of the FILTER function as mentioned below:
=SUM(FILTER($D$3:$D$18,$B$3:$B$18=F3))
Please confirm.
Regards,
Deepak.
Excellent video,,
Kenji is the real GOAT 😊😊❤❤. Thank you for all your videos man,..
amazing always, very highly appreciated Kenji
Thanks for watching :)
You did great Job.. you got one more subc.
Great video!!
Glad you enjoyed it
Amazing! I was only familiar with the first formula😂❤
I am a beginner, still receiving class. I enjoy watch your videos, very self explanatory
Amazing :)
I used sumproduct for the last limitations part
Pieeeeerre Gaaaslyyyyyyy 😁
Your videos are simply lovely :)
hahahhaa Thank you!
Nice presentation, Maybe you needed to add that this function is available for Office 365, Excel 2019 and above.
thanks
Sumif or sum & filter .. both will work
Kenji, what if the columns were not named in months? On Vlookup we used column number of the table array.
Interesting.
Hi Kenji, what laptop do you use? thanks
I like this video
Wow Kanji you are he goat i will replicate this and tagged you .. take your 🌺🌺🌺🌺🌹🌹
dude, how is it better than the index match? how will you use an xlookup when the source data table and the results table have column headers in different orders?? in that case please tell me how xlookup can solve it.
Really helpful and informative, but I have a query : What if we have same first name but different last name, How to apply the formula then?
u made this shit fun, i didnt jnow u could xlookup xlookup
hhaah thank you!
Very insightful video and thank you for that. But you mentioned that Xlookup only allows one lookup value. I believe I can add up multiple lookup values by adding the & in the lookup_value section right? But I can also see that it wouldn't work efficiently in this specific example because we have more than one months, so a nested xlookup is better option of course.
How does this replace vlookup then?
@@zmm3492 Xlookup in general replaces Vlookup in many ways, like Vup can't look to the left or look horizontally or it can't give you back multiple answers as shown in the video.
I am wondering what would happen in the Example 2 if there are more people with the same surname?
hi i am having a issue i am selling some item for someone but i will like to see what from the sale is mine and what is his how do i work that out ?
Hello sir I need some help that by interning only ID Number of an employee so I can get all the information about that employee like Name,last Name,department that he/she work in if can you help me that will be very nice?
Sir can U plz make a video of basic excel to advance level. 🙏
Plzzzzz.... Plzzzz..pllzz sir make a video of basic to advance level excel.🙏
Wonderful 😍
One request please make video on Three statement model from scratch using assumptions...
coming next week! Stay tuned :)
@@KenjiExplains Thankyou Man 🥺❤️
Very helpful video! If I had more than one employees with the same name, could I use the wildcard method to find whichever employee I'd want? If not, which method is the most efficient?
also works XLOOKUP combined to SUM formula
SumIf for me in the last one too.
Can copilot 365 and other ai tools do all type of works in ms Excel
Or is it still important to learn ms Excel deeply all functions and advance formulas ??
Sir please ans me
Hello! For each formula I added I received an error #VALUE! Any ideas?
how it works for the last part for text
If you can make one video that will be so nice
Always work in tables, never in ranges.
Love the F1 names lol
Hahaha glad you noticed!!
How can I use the Xlookup formula to find information across multiple worksheets (not workbooks)?
That's easy, just select the area on the desired tab. Info doesn't all need to be on the same sheet.
Xlookup gang
Lets go!
i am been frustrated with with #NAME? error. i followed your steps but still ends up pop up the error. Xlookup( xlookup value,array, return value)
Last problem can be solved using sumif 10:15
👍
Can we use XLOOKUP in Excel tables?
where are your country ?
Thanks for this impactful teaching. However, I keep getting this error on the last one 5 (#VALUE!).
I am using 2019 Excel. don't know if that's the reason for the error. Kindly assist
i didn't get output
👍👍☝☝
Lewis Hamilton should earn the highest
formula 1 fan?
ahah glad you noticed
Number 5 was meh
Sir please speak in hindi
haha i would if i could!
Have you employed ex Formula...no pun intended...One drivers?
hahaha love it! glad you noticed & thanks for watching :)
Hi, thank you for all your informative videos... i know you're busy, but I do hope you'll get time to reply to my question..
How do i use Xlookup on specific dates and revenue
( weekly summary )
Daily , weekly , week no, and months, can i also use it for expenses