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.

Комментарии • 93

  • @KenjiExplains
    @KenjiExplains  8 месяцев назад +4

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

    • @johncipolla8335
      @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

  • @RogerKlauser
    @RogerKlauser 28 дней назад

    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!

  • @premierleague9477
    @premierleague9477 7 месяцев назад +1

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

  • @AzeezulGani
    @AzeezulGani 3 месяца назад

    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!

  • @wattosworkshop
    @wattosworkshop 5 месяцев назад +2

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

  • @GmanMilli
    @GmanMilli 5 месяцев назад +1

    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.

  • @imanasri9950
    @imanasri9950 Месяц назад

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

  • @rodeld.rengel4905
    @rodeld.rengel4905 6 дней назад

    Very informative. Thanks.

  • @High_Goblin_King
    @High_Goblin_King 7 месяцев назад +1

    Thanks for mentioning the shortcuts everytime you use them!

  • @agtechtitan
    @agtechtitan 2 месяца назад

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

  • @Indrajeet-roy
    @Indrajeet-roy 4 месяца назад

    Thanks for making such wonderful video.

  • @XLLearner_Courses
    @XLLearner_Courses 8 месяцев назад +2

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

  • @b.r.srihari4099
    @b.r.srihari4099 8 месяцев назад

    Super video yet again!!!

  • @amusaazeez4410
    @amusaazeez4410 Месяц назад

    We appreciate you, keep on good work
    From Nigeria

  • @antonynganga7868
    @antonynganga7868 8 месяцев назад +1

    💯/ 💯 brilliant..Bro Kenji💪Dubai

  • @hanko82
    @hanko82 4 месяца назад

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

  • @tomr.5217
    @tomr.5217 2 месяца назад

    You are amazing man!

  • @janushabopage6790
    @janushabopage6790 6 месяцев назад

    Thank you very much.

  • @user-jk5gg8mv6n
    @user-jk5gg8mv6n 8 месяцев назад +1

    Thank you

  • @pawan.space....
    @pawan.space.... 8 месяцев назад +2

    really helpful to explain this by you kenji

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

  • @Jobheha
    @Jobheha 6 месяцев назад

    Excellent video,,

  • @charlieee9040
    @charlieee9040 8 месяцев назад +9

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

  • @aspectoflife
    @aspectoflife 4 месяца назад

    amazing always, very highly appreciated Kenji

  • @stalker6887
    @stalker6887 2 месяца назад

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

  • @camlex6310
    @camlex6310 8 месяцев назад +2

    Great video!!

  • @izdadit
    @izdadit 8 месяцев назад

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

  • @user-kk2ve9nb1m
    @user-kk2ve9nb1m 24 дня назад

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

  • @lonerpirate
    @lonerpirate 7 месяцев назад

    Amazing :)

  • @corporate_guyfitness
    @corporate_guyfitness 8 месяцев назад +1

    I used sumproduct for the last limitations part

  • @Dmytro848
    @Dmytro848 7 месяцев назад +1

    Pieeeeerre Gaaaslyyyyyyy 😁
    Your videos are simply lovely :)

  • @FrankEric
    @FrankEric День назад

    Nice presentation, Maybe you needed to add that this function is available for Office 365, Excel 2019 and above.

  • @sofebiruk9996
    @sofebiruk9996 5 месяцев назад

    thanks

  • @patrickkinbonso1809
    @patrickkinbonso1809 8 месяцев назад

    Sumif or sum & filter .. both will work

  • @stephenmakua6428
    @stephenmakua6428 7 месяцев назад +1

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

  • @kvelez
    @kvelez 20 дней назад

    Interesting.

  • @edwardgarcia752
    @edwardgarcia752 6 месяцев назад

    Hi Kenji, what laptop do you use? thanks

  • @phandongexcel
    @phandongexcel 4 месяца назад

    I like this video

  • @soyoyeoluwafunso4586
    @soyoyeoluwafunso4586 6 месяцев назад

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

  • @arunmm4777
    @arunmm4777 8 месяцев назад +1

    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.

  • @susansinha6509
    @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?

  • @berniebeast
    @berniebeast 8 месяцев назад +1

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

  • @MatoYT
    @MatoYT 8 месяцев назад +1

    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
      @zmm3492 8 месяцев назад

      How does this replace vlookup then?

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

  • @martinayari3529
    @martinayari3529 7 месяцев назад

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

  • @agespin
    @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
    @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?

  • @sarabendranathgharai3898
    @sarabendranathgharai3898 8 месяцев назад

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

  • @sarabendranathgharai3898
    @sarabendranathgharai3898 8 месяцев назад

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

  • @RohitThakur-en7qw
    @RohitThakur-en7qw 8 месяцев назад +1

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

    • @KenjiExplains
      @KenjiExplains  8 месяцев назад +2

      coming next week! Stay tuned :)

    • @RohitThakur-en7qw
      @RohitThakur-en7qw 8 месяцев назад

      @@KenjiExplains Thankyou Man 🥺❤️

  • @nickandreoglou2871
    @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?

  • @maxidago
    @maxidago 4 месяца назад

    also works XLOOKUP combined to SUM formula

  • @rtshort
    @rtshort 8 месяцев назад +1

    SumIf for me in the last one too.

  • @goutamnayak5011
    @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 ??

  • @barumarius2430
    @barumarius2430 5 месяцев назад

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

  • @VineethaChoyyan
    @VineethaChoyyan Месяц назад

    how it works for the last part for text

  • @ferozfaqeeri
    @ferozfaqeeri 6 месяцев назад

    If you can make one video that will be so nice

  • @SingleSuccessfulMomMBA1422
    @SingleSuccessfulMomMBA1422 Месяц назад +3

    Always work in tables, never in ranges.

  • @EffigyOfAdam
    @EffigyOfAdam 7 дней назад

    Love the F1 names lol

  • @davelemberg5602
    @davelemberg5602 5 месяцев назад

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

    • @SingleSuccessfulMomMBA1422
      @SingleSuccessfulMomMBA1422 Месяц назад

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

  • @mohamedeassa
    @mohamedeassa 8 месяцев назад +3

    Xlookup gang

  • @AndybextTV
    @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)

  • @yaminis8991
    @yaminis8991 7 месяцев назад

    Last problem can be solved using sumif 10:15

  • @graceminiano8325
    @graceminiano8325 2 месяца назад

    👍

  • @goingvenus5603
    @goingvenus5603 15 дней назад

    Can we use XLOOKUP in Excel tables?

  • @user-ly3ox7rc1d
    @user-ly3ox7rc1d Месяц назад

    where are your country ?

  • @iampeteragada
    @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
    @marrapukiran8944 2 месяца назад

    i didn't get output

  • @dennisverduz6289
    @dennisverduz6289 5 месяцев назад

    👍👍☝☝

  • @brianwakhungu8635
    @brianwakhungu8635 5 месяцев назад +1

    Lewis Hamilton should earn the highest

  • @willzinner8813
    @willzinner8813 8 месяцев назад +1

    formula 1 fan?

  • @Nolgath
    @Nolgath 8 месяцев назад

    Number 5 was meh

  • @GauravKumar-ti4oc
    @GauravKumar-ti4oc 8 месяцев назад

    Sir please speak in hindi

  • @Dexter101x
    @Dexter101x 8 месяцев назад

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

    • @KenjiExplains
      @KenjiExplains  8 месяцев назад +1

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

  • @0826254960
    @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