The Ultimate XLOOKUP Tutorial (The Best Excel Formula)

Рет қаралды 133,910

Күн бұрын

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.

Пікірлер: 92

@KenjiExplains 8 ай бұрын

👉 Take our Excel Course: www.careerprinciples.com/courses/excel-for-business-finance

@johncipolla8335 5 ай бұрын

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

@AzeezulGani 2 ай бұрын

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!

@premierleague9477 7 ай бұрын

I love how you clearly explain how to apply formulas .....sending love from Kenya

@wattosworkshop 5 ай бұрын

Thanks for your clear, direct and informative instruction. I have subscribed.

@RogerKlauser 25 күн бұрын

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!

@imanasri9950 Ай бұрын

brilliant. thank you so much Kenji for the tutorial video! love from Malaysia

@agtechtitan 2 ай бұрын

You are such an amazing tutor.Thankyou so much.I subscribed

@GmanMilli 5 ай бұрын

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.

@High_Goblin_King 7 ай бұрын

Thanks for mentioning the shortcuts everytime you use them!

@Indrajeet-roy 4 ай бұрын

Thanks for making such wonderful video.

@charlieee9040 8 ай бұрын

Kenji is the real GOAT 😊😊❤❤. Thank you for all your videos man,..

@hanko82 4 ай бұрын

I am in a bootcamp for data analytics and I found this video so helpful

@XLLearner_Courses 8 ай бұрын

Yes, I agree. One of the functions I use daily

@rodeld.rengel4905 3 күн бұрын

Very informative. Thanks.

@amusaazeez4410 Ай бұрын

We appreciate you, keep on good work From Nigeria

@pawan.space.... 8 ай бұрын

really helpful to explain this by you kenji

@KenjiExplains 8 ай бұрын

glad it was helpful!

@b.r.srihari4099 8 ай бұрын

Super video yet again!!!

@antonynganga7868 8 ай бұрын

💯/ 💯 brilliant..Bro Kenji💪Dubai

@aspectoflife 4 ай бұрын

amazing always, very highly appreciated Kenji

@KenjiExplains 4 ай бұрын

Thanks for watching :)

@equiwave80 6 ай бұрын

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.

@user-kk2ve9nb1m 20 күн бұрын

I am a beginner, still receiving class. I enjoy watch your videos, very self explanatory

@tomr.5217 2 ай бұрын

You are amazing man!

@izdadit 8 ай бұрын

Amazing! I was only familiar with the first formula😂❤

@janushabopage6790 5 ай бұрын

Thank you very much.

@corporate_guyfitness 8 ай бұрын

I used sumproduct for the last limitations part

@Jobheha 6 ай бұрын

Excellent video,,

@Dmytro848 7 ай бұрын

Pieeeeerre Gaaaslyyyyyyy 😁 Your videos are simply lovely :)

@KenjiExplains 7 ай бұрын

hahahhaa Thank you!

@stalker6887 2 ай бұрын

You did great Job.. you got one more subc.

@user-jk5gg8mv6n 8 ай бұрын

Thank you

@camlex6310 8 ай бұрын

Great video!!

@KenjiExplains 8 ай бұрын

Glad you enjoyed it

@patrickkinbonso1809 8 ай бұрын

Sumif or sum & filter .. both will work

@MatoYT 8 ай бұрын

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.

@zmm3492 8 ай бұрын

How does this replace vlookup then?

@MatoYT 8 ай бұрын

@@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.

@lonerpirate 7 ай бұрын

Amazing :)

@edwardgarcia752 6 ай бұрын

Hi Kenji, what laptop do you use? thanks

@stephenmakua6428 7 ай бұрын

Kenji, what if the columns were not named in months? On Vlookup we used column number of the table array.

@susansinha6509 Ай бұрын

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?

@RohitThakur-en7qw 8 ай бұрын

Wonderful 😍 One request please make video on Three statement model from scratch using assumptions...

@KenjiExplains 8 ай бұрын

coming next week! Stay tuned :)

@RohitThakur-en7qw 8 ай бұрын

@@KenjiExplains Thankyou Man 🥺❤️

@nickandreoglou2871 8 ай бұрын

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?

@soyoyeoluwafunso4586 6 ай бұрын

Wow Kanji you are he goat i will replicate this and tagged you .. take your 🌺🌺🌺🌺🌹🌹

@sofebiruk9996 5 ай бұрын

thanks

@arunmm4777 8 ай бұрын

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.

@kvelez 16 күн бұрын

Interesting.

@maxidago 4 ай бұрын

also works XLOOKUP combined to SUM formula

@phandongexcel 4 ай бұрын

I like this video

@sarabendranathgharai3898 8 ай бұрын

Plzzzzz.... Plzzzz..pllzz sir make a video of basic to advance level excel.🙏

@sarabendranathgharai3898 8 ай бұрын

Sir can U plz make a video of basic excel to advance level. 🙏

@martinayari3529 7 ай бұрын

I am wondering what would happen in the Example 2 if there are more people with the same surname?

@berniebeast 8 ай бұрын

u made this shit fun, i didnt jnow u could xlookup xlookup

@KenjiExplains 8 ай бұрын

hhaah thank you!

@rtshort 8 ай бұрын

SumIf for me in the last one too.

@goutamnayak5011 8 ай бұрын

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 ??

@goutamnayak5011 7 ай бұрын

Sir please ans me

@ferozfaqeeri 6 ай бұрын

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?

@agespin 5 ай бұрын

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 ?

@ferozfaqeeri 6 ай бұрын

If you can make one video that will be so nice

@barumarius2430 5 ай бұрын

Hello! For each formula I added I received an error #VALUE! Any ideas?

@mohamedeassa 8 ай бұрын

Xlookup gang

@KenjiExplains 8 ай бұрын

Lets go!

@VineethaChoyyan Ай бұрын

how it works for the last part for text

@davelemberg5602 5 ай бұрын

How can I use the Xlookup formula to find information across multiple worksheets (not workbooks)?

@SingleSuccessfulMomMBA1422 Ай бұрын

That's easy, just select the area on the desired tab. Info doesn't all need to be on the same sheet.

@EffigyOfAdam 4 күн бұрын

Love the F1 names lol

@KenjiExplains 4 күн бұрын

Hahaha glad you noticed!!

@yaminis8991 7 ай бұрын

Last problem can be solved using sumif 10:15

@SingleSuccessfulMomMBA1422 Ай бұрын

Always work in tables, never in ranges.

@goingvenus5603 11 күн бұрын

Can we use XLOOKUP in Excel tables?

@AndybextTV Ай бұрын

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)

@iampeteragada 6 ай бұрын

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

@marrapukiran8944 2 ай бұрын

i didn't get output

@user-ly3ox7rc1d Ай бұрын

where are your country ?

@graceminiano8325 2 ай бұрын

👍

@willzinner8813 8 ай бұрын

formula 1 fan?

@KenjiExplains 8 ай бұрын

ahah glad you noticed

@brianwakhungu8635 5 ай бұрын

Lewis Hamilton should earn the highest

@dennisverduz6289 5 ай бұрын

👍👍☝☝

@Nolgath 8 ай бұрын

Number 5 was meh

@GauravKumar-ti4oc 8 ай бұрын

Sir please speak in hindi

@KenjiExplains 8 ай бұрын

haha i would if i could!

@Dexter101x 8 ай бұрын

Have you employed ex Formula...no pun intended...One drivers?

@KenjiExplains 8 ай бұрын

hahaha love it! glad you noticed & thanks for watching :)

@0826254960 2 ай бұрын

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