The Ultimate LOOKUP Guide (XLOOKUP, VLOOKUP, HLOOKUP and more)

[@KenjiExplains]

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

[@solomonbhandari-young4154]

thank u coach

[@weihuang8597]

hi mate, can you help with Multiple criteria lookup? index match works on some of cells but not others, can you do it with XVLOOKUP?

[@JamieMigadde]

Heavens! Please dont ever stop making such content.. I learned so much and so effortlessly. Thank you very much Sir!

[@joel9784]

You are the more complete yet the more simple tutorial that I've seen, straight to the point, thank you.

[@theterminaldave]

THANK YOU! Your XLOOKUP section completely saved me hours of soul-crushing monotony, and made me look good at a temp job. Your short cut videos have also REALLY helped. THANK YOU.. again! You've made a massive difference in my working life, which isn't something I think I've ever said in a youtube comment.

[@ekeretteekpo3004]

So far, you are the best Excel tutor I have come across on KZbin. Damn! I was struggling with understanding the lookups, although I did ace the mini test but missed the question on vlookup/hlookup/xlookup and how they operate. It led me to search for explanations and I landed on your video here. Awesome stuff.

[@donnavolz6268]

Me too. Kenji has really been able to make it "click" in my mind!

[@richardgordon]

Kenji has a real gift for communicating complex ideas into simple easy to understand language. He is simply the best!

[@codename5773]

Minus the fact we need to sign up to download his sample file

[@AyushSingh-cl8px]

I have been watching your videos from a very long time as they are very helpful and easy to understand. Keep up the good work!

[@KenjiExplains]

Glad to hear that!

[@charlestoneoyoo]

Kenji you're an Excel goat, always making things comprehensive and concised, thanks for the video and the quick tips and tricks

[@KenjiExplains]

Thanks for watching!

[@lanahinds1264]

This was fantastic! Easy to follow and SO SO helpful! I didn’t realize you all of this was possible! Thank you for taking the time to walk through all of this!

[@aperson2020]

Omg. You are the super master of explanation. You are the man, the God and the ultimate. ❤❤❤❤❤❤❤❤❤ so beautifully done.

[@6690501]

Hey kenji it might be easier to just do a single dual criteria filter function rather than two separate (nested ) filter functions syntax: =filter(array,(criteria 1)*(criteria 2))

[@KenjiExplains]

Haven’t tested it but I think you’re right! Thanks for the info 👏

[@ekeretteekpo3004]

I knew there had to be a shorter way still. Whoever you are, thank you for pointing this out. 🙏

[@domaugustus72]

Or you can do a single index match formula for that last example, matching the salesperson for the row and the year for the column

[@ChandanKumar98158]

​@@domaugustus72 This is what i'm also thinking...👌

[@yashpandey1851]

Kenji literally explained it like it was nothing. Kudos!

[@mirelaion5514]

the last formula ox xlookup was exactly what i was looking for!

[@mohamedtahaahmed3306]

Dear, Your way of explanations, is the easiest on KZbin, thank you so much

[@Fel04]

Thanks bro, i thought that excel was hard, but you make it so simple to understand and i'm getting better day by day.

[@kindredspiritoflife]

This was so precise and to the point. I understood everything you said and appreciated the visual reference. GREAT TUTORIAL!

[@VickiMcCorkle]

You by far have the best tutorial videos. Thank you for providing so many helpful videos.

[@nikhiljadhav4343]

Thanks!

[@KenjiExplains]

thank you for supporting Nikhil!

[@karlvictorsumbeling5694]

Excellent! Learning Day by Day Again with Excel thru you YT Channel. Please share more sample worksheets for Industrial Setting.🥰

[@Tamar907]

Thank you for sharing this invaluable knowledge. Good things you did will rebound on you.

[@junnaaquino2510]

Nice. More of these, please! Combinations of beast functions

[@renalynbanhaw3203]

The last part is the most important for me! Kudos to you for sharing this information🎉

[@freethinker1970]

Awesome guide just what I needed. Much appreciate it!

[@sachin9yadav]

This guy is a gem. Thank you Brother for your efforts.

[@KenjiExplains]

Thank you for watching!

[@321BestLife]

Best and easiest explanation I have seen. Thanks for the content and simplicity!

[@equiwave80]

Thanks for this video as you have demystified the LOOKUP functions. This is the best 13 minutes I have ever spent on MS-Excel. BTW you got a new subscriber. Thanks for all your efforts!!!👍😀

[@jujoromar]

I was trying to sum some similar headings on different columns , but couldn’t find a solution until watching this video , offset fórmula made it posible . Thanks !!!!

[@taladiv3415]

Thank you for you clear examples and explanation.👌

[@shangds75]

Thanks to this video, I finally understand how to look up a result in the last situation mentioned in the video. Thank you.

[@anishsoni9929]

Bro I love the quality of your videos keep up the good work

[@shubhamverma3030]

One of the Best video on Excel.

[@chulanessa9248]

Amazing 👏.. I've got excited more with excel thank you with this knowledge.

[@ikennaewurum6415]

Your delivery is top notch. 👌

[@vikramadityarathore97]

Very informative videos as usual kenji , it would be nice if you could make videos based on synergy valuation in m&a in the future?

[@MohammadNatsirrockwood]

Thanks!!! I have been looking the last part for long and now I can sleep in peace.

[@MrHugawa]

nicely done, you make it easy to understand... now I just have to remember it when I need it :) thx!!

[@cycleSCUBA]

Excellent. 5⭐ description and examples. This will help me so much.

[@ishasaini1082]

Damn i always used to change the array arrangement in order to use vlookup. Thanks for introducing xlookup

[@m2badri]

Awesome learning. Thanks for your explanation. This truly helped me to learn xloopup and also filter / sum in a unique way!

[@rugmaable]

wow!! Just wow!! you are really amazing!! I didn't know this was possible.

[@naivedhyakhatri5142]

Combining that with data validation is a neat combo for dashboards

[@jonathanpazmino6157]

This is the most helpful video ever. Thank you so muchhhhhh.

[@christineliberty1900]

This was SO helpful! Thank you so much for posting this video.

[@ahmadHarunadalibi99]

Best tutor Ever!!!

[@bolajiadedasola6369]

I will definitely enroll in your excel course. No doubt

[@adhamdodzy2855]

Very clean explaning .. Thank you very much for your videos :)))

[@KenjiExplains]

Glad you like them!

[@ramin31tube]

You solved a big problem for me. Thanks a lot

[@natalieharrison5202]

If only one record, use xlookup, if multiple and need total sum, use sum filter. Got it 👍

[@knvishnu25]

Thank you so much Kenji, Excellent function of Filter sum.

[@KenjiExplains]

Thank you for watching! 🙌

[@evelinaswiech7244]

Amazing videos! You have a new fan 🙋🏼‍♀️ I was wondering, rather than dragging the formula down, how can I spread it through the entire column?

[@CPIVCR]

thank you. you always make it look so easy to understand

[@AkshayKumar-ep5qd]

Thank you... 😀 That's what i was looking for. Level four 🌟 solved my problem.

[@vocabsman]

Well-done, your explanation is the best and understandable. Thank you

[@janithjayashanka1578]

Those very Useful Tips Brother💪

[@michahsimmons7568]

For the last example, it might be easier to do a sumifs instead of a nested filter (little cleaner for whoever's coming behind). :) Very informative video!

[@logo8105]

Regarding the SumFilter sheet, the proposed function "=SUM(FILTER(FILTER(C4:F25,B4:B25=H8),C3:F3=I8))" works great but cannot handle wildcard (at least for me). A solution for this may be "=SUMPRODUCT(--ISNUMBER(SEARCH(I8&"*",B4:B25)),FILTER(C4:F25,C3:F3=I12))" that works well for me. Here we get an array of 0 and 1 based on the results of the SEARCH function (which works with wildcard) by using the ISNUMBER with "--" in front. Then we multiply it by the results of the FILTER function by year and sum using SUMPRODUCT. Cheers

[@shivagoel6576]

you really do explain kenji ! Thanks a ton

[@praveenm4319]

Really awesome teaching. Thank You ; )

[@tsegaye7548]

Thanks, I liked the most here at Sum-filter part

[@jBXV]

I'm trying to find historical values of products purchased from old invoices records and probably XLookup level 4 06:24 would work for me hopefully.

[@syedhussain7967]

Wow Excellent information...m from pakistan❤

[@opl1443]

filter formula was very useful thanks

[@sdbnmr]

Thanks, Kenji for the awesome video.

[@hamodeosso6507]

Great content bro!!

[@KenjiExplains]

Appreciate it!

[@kenzokubo6352]

For the last one, you can use a sumproduct formula with two criteria to find the answer

[@sundaramvenkataraman9592]

Thanks Kenji on your wonderful explanation, i am still not clear on how both sale amount and commission gets auto populated ( timings 5:40 to 6.18) where xlookup formulae is input only for sale amount only, though with both the ranges covered for sale amount and array, it still does not work.. please clarify.

[@maldridge7630]

I have to second all positive compliments herein 🤜🤛

[@PradhumnShandilya]

9:59 He felt so sad saying and I can feel his expressions when he said XLOOKUP does have limitations.

[@AMDCIT]

great tutorials! Thank you!

[@manel1185]

Excellent video! Pls could you do "The ULTIMATE Sum Formulas Guide | SUMIF, SUMIFS, COUNTIF, COUNTIFS"? Thanks!

[@KenjiExplains]

Great suggestion! will note it :)

[@manel1185]

@@KenjiExplains Thanks Kenji! I did not know about the XLookup

[@learningdistilled]

Cool idea, those are all super useful functions to use!

[@paulmeek537]

Bro, your making me the beloved Excel guru at work 😂

[@ahmadsheikh5710]

Very well explained

[@michaelquach7046]

Xlookup all the way! 🏆

[@KenjiExplains]

lets go!

[@ikkusoft]

Thank you, this was very helpful

[@halagaming2603]

Thank you ao much

[@sharondavis3535]

Thank you. Great lessons.

[@allenqueen]

That was smooth. Thank you.

[@JackKing12.]

Nice...everyday is a skool day...

[@blakevalenciatv]

Nice and Thanks very informative and useful

[@kayodeolusogo9571]

I am very excited with your mode of teaching. however, xlookup is not working on my system.Why?

[@andymarci6766]

Great content Kenji! Deserving to see next week's video

[@DkC-j4y]

Thank you for the 12 minutes tutorial. It was great to learn from you. One question though, how comes I cannot download the free excel file? Would be appreciated if you would let us know. Many thanks👍👍👍

[@KenjiExplains]

Hey thank you the file should be in your inbox :)

[@ГеворгБабин]

The best explanation

[@nathaliemartz]

Thanks for your great explanaiton, Kenji! So very useful! One quick question... how do you set up Google Sheet to show you the formulas in the help window in this kind of interactive way? I've seen that you can even click on a part and just substitute the information... I didn't know this option was a thing. Could you explain how to do it? Thanks so much again!

[@ArthurTassinello]

I have a 3-column list of Make, Model, and Car Class. For the Make, I'm using the data list drop-down menu. Now I want a drop-down menu of only those makes associated with that model. Is there a combo of lookup and data list? Can you help with that? Thanks. By the way, your videos are awesome.

[@mechiemenil2521]

Very informative! Where can I see the filter function? It is not available/doesn't work with my excel. Thank you!

[@learningdistilled]

If you are using office 2019 or earlier, I don’t think the filter function will be available.

[@DaniyalAhmed-d9f]

Hey Kenji, I love your vidz I got a question related to sum and filter part ; what if I am type Harley instead of Harley Fritz !?

[@diakolass2883]

Great video mate, for level 4 XLOOKUP, what if there are multiple values, it only shows the first one. any one got any suggestions?

[@Eternal_Truth8]

Thanks for the tutorial!

[@KenjiExplains]

Cheers!

[@magdahassib694]

Thanks, Excellent

[@KenjiExplains]

Glad you liked it!

[@lste]

Very useful. Thank you.

[@XLLearner_Courses]

Pro explainer. 👍

[@gurharkrishanbureau187]

V well explained

[@KenjiExplains]

thank you!

[@MatthijsRosman]

Thanks for this! Very helpful. What if you have Nike EU, Nike EMEA, Nike US in the table and want to add all the Nike results (so all three values)? Now, the formula stops at the first hit of the word Nike in the column. Thanks for your help!

[@joukenienhuis6888]

Nathalie already answered it, but i will repeat the answer, you have to use the filter and the sum together. Just find the right filter.

[@carlmondala9960]

This is very useful. Thanks a lot!

[@KenjiExplains]

Thanks for watching!

[@Yan-c8w]

Hi Kenji, Excel expert. I have a question about homework that the lookup value is a date from a table, when you put vlookup formule, after submited and was always informed the data lookup is incorrect and the intial data is also changed. In this case, how to solve it? Thank you very much.

[@MySpin23]

Thank you for informative videos

[@camlex6310]

Really comprehensive!! Cheers

[@KenjiExplains]

Thanks for watching!

[@saurabhramane1478]

Hey kenji, can you please make one video on equity research tutorial.

[@marriamrasib874]

Amazing video

[@MarkHughes-po4iz]

Hi Kenji, can you write a VLOOKUP and then an XLOOKUP? I'm trying to make a Change Order sheet where the data comes from another tab. I want to look up my Change Order number with the VLOOKUP, then as the column reference It would jump into XLOOKUP so it would pick up a few rows and columns. Not sure it is possible but I've been trying for a couple days and I can only get as close as a "#VALUE". What do you think?