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

[@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!

[@yashpandey1851]

Kenji literally explained it like it was nothing. Kudos!

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

[@mohamedtahaahmed3306]

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

[@mirelaion5514]

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

[@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!

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

[@naivedhyakhatri5142]

Combining that with data validation is a neat combo for dashboards

[@VickiMcCorkle]

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

[@sachin9yadav]

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

[@KenjiExplains]

Thank you for watching!

[@renalynbanhaw3203]

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

[@karlvictorsumbeling5694]

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

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

[@kindredspiritoflife]

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

[@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 !!!!

[@Tamar907]

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

[@ishasaini1082]

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

[@shubhamverma3030]

One of the Best video on Excel.

[@ikennaewurum6415]

Your delivery is top notch. 👌

[@freethinker1970]

Awesome guide just what I needed. Much appreciate it!

[@natalieharrison5202]

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

[@taladiv3415]

Thank you for you clear examples and explanation.👌

[@cycleSCUBA]

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

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

[@chulanessa9248]

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

[@shangds75]

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

[@jonathanpazmino6157]

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

[@MohammadNatsirrockwood]

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

[@321BestLife]

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

[@tsegaye7548]

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

[@junnaaquino2510]

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

[@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!

[@kayodeolusogo9571]

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

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

[@rugmaable]

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

[@ramin31tube]

You solved a big problem for me. Thanks a lot

[@shivagoel6576]

you really do explain kenji ! Thanks a ton

[@kenzokubo6352]

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

[@christineliberty1900]

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

[@ahmadHarunadalibi99]

Best tutor Ever!!!

[@anishsoni9929]

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

[@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 :)

[@knvishnu25]

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

[@KenjiExplains]

Thank you for watching! 🙌

[@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!!!👍😀

[@praveenm4319]

Really awesome teaching. Thank You ; )

[@m2badri]

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

[@CPIVCR]

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

[@MrHugawa]

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

[@adhamdodzy2855]

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

[@KenjiExplains]

Glad you like them!

[@opl1443]

filter formula was very useful thanks

[@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!

[@bolajiadedasola6369]

I will definitely enroll in your excel course. No doubt

[@AkshayKumar-ep5qd]

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

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

[@maldridge7630]

I have to second all positive compliments herein 🤜🤛

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

[@sdbnmr]

Thanks, Kenji for the awesome video.

[@vocabsman]

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

[@janithjayashanka1578]

Those very Useful Tips Brother💪

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

[@JackKing12.]

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

[@halagaming2603]

Thank you ao much

[@sharondavis3535]

Thank you. Great lessons.

[@ahmadsheikh5710]

Very well explained

[@allenqueen]

That was smooth. Thank you.

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

[@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!

[@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!

[@hamodeosso6507]

Great content bro!!

[@KenjiExplains]

Appreciate it!

[@saurabhramane1478]

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

[@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 !?

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

[@ikkusoft]

Thank you, this was very helpful

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

The best explanation

[@mautrunk]

one question. Ain´t that a bit redundant to put the column since you already putting the range of what you are looking into?

[@blakevalenciatv]

Nice and Thanks very informative and useful

[@licao2565]

The last one we can use pivot table as well

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

[@syedhussain7967]

Wow Excellent information...m from pakistan❤

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

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

[@rohan-cj8vg]

Kenji I didn't get the dynamic part of vlook up right

[@paulmeek537]

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

[@michaelquach7046]

Xlookup all the way! 🏆

[@KenjiExplains]

lets go!

[@Eternal_Truth8]

Thanks for the tutorial!

[@KenjiExplains]

Cheers!

[@themessycook4912]

HI!! can you upload a video explaining Vlookup when there are du[plicate Lookupvalues in lookup array ?

[@kayodedayo3250]

Couldn't have the download for this lookup concise tutorial video

[@magdahassib694]

Thanks, Excellent

[@KenjiExplains]

Glad you liked it!

[@andymarci6766]

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

[@creatorkannan675]

Hey for the xlook up 5 we can use index+math functions

[@anneeeev]

Hey Kenji, So I know you’re into Investment Banking. But as I was going through some of your older videos, I noticed your Resume/Cover letter mentions the fact that you did an audit and assurance internship at some point?? I was wondering if you’d be able to do a video sharing tips to build a CV and cover letter for audit?

[@nikhilpatil3087]

Also how do I search in a table using values from another cell?

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