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

Learn all about xlookups, vlookups, hlookups and their limitations.
👉 Take our Excel Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD the Free Excel file for this video: view.flodesk.com/pages/62ed12...
In this video you'll learn the pros and cons of the main lookup formulas.
First, we'll go over the vlookup and where it can be useful. Then we'll look at the hlookup. Following this, we'll look at the main limitation of these two lookups, which is that it can only work if your data is the to right.
As such, we introduce a solution in the form of an xlookup, going over 5 different scenarios where it can come handy, from easiest, all the way to hardest.
1. Basic xlookup
2. xlookup if not found feature
3. xlookup match mode
4. xlookup with multiple solutions
5. a nested xlookup (meaning 2 xlookups combined)
Lastly, we'll go over the limitation of xlookups, and propose a complex solution involving a nested filter and sum formula.
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
📊 Get 25% OFF Financial Edge Using Code KENJI25: bit.ly/3Ds47vS
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑‍💻 LinkedIn - / careerprinciples
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00​ -​ Vlookup
1:50​ - Hlookup
2:53​ - Lookup Limitations
3:50​ - Xlookup Level 1
4:37​ - Xlookup Level 2
5:29​ - Xlookup Level 3
6:24​ - Xlookup Level 4
8:22​ - Xlookup Level 5
9:55​ - Xlookup Limitations
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

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

[@mirelaion5514]

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

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

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

[@ekeretteekpo3004]

So far, you are the best Excel tutor I have come across on RUclips. 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

[@joel9784]

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

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

[@321BestLife]

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

[@freethinker1970]

Awesome guide just what I needed. Much appreciate it!

[@kindredspirit.9941]

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

[@junnaaquino2510]

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

[@shangds75]

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

[@christineliberty1900]

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

[@MrHugawa]

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

[@m2badri]

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

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

[@user-rj8bw7vf3q]

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

[@aperson2020]

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

[@CPIVCR]

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

[@mohamedtahaahmed3306]

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

[@cycleSCUBA]

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

[@MrJlaryea]

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

[@andymarci6766]

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

[@chulanessa9248]

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

[@sdbnmr]

Thanks, Kenji for the awesome video.

[@jonathanpazmino6157]

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

[@vitale6633]

Very Helpful - Thank You !!!

[@shivagoel6576]

you really do explain kenji !
Thanks a ton

[@ikkusoft]

Thank you, this was very helpful

[@ramin31tube]

You solved a big problem for me. Thanks a lot

[@AMDCIT]

great tutorials! Thank you!

[@renalynbanhaw3203]

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

[@rugmaable]

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

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

[@anishsoni9929]

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

[@shubhamverma3030]

One of the Best video on Excel.

[@AkshayKumar-ep5qd]

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

[@allenqueen]

That was smooth. Thank you.

[@karlvictorsumbeling5694]

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

[@sharondavis3535]

Thank you. Great lessons.

[@ikennaewurum6415]

Your delivery is top notch. 👌

[@praveenm4319]

Really awesome teaching. Thank You ; )

[@knvishnu25]

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

[@KenjiExplains]

Thank you for watching! 🙌

[@bolajiadedasola6369]

I will definitely enroll in your excel course. No doubt

[@opl1443]

filter formula was very useful thanks

[@lste]

Very useful. Thank you.

[@MySpin23]

Thank you for informative videos

[@VERTICALcube]

I love it...Thanks.

[@janithjayashanka1578]

Those very Useful Tips Brother💪

[@blakevalenciatv]

Nice and Thanks very informative and useful

[@athemkashung8266]

Thanks. Really helpful.

[@tsegaye7548]

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

[@user-hb5vg1me5l]

Best tutor Ever!!!

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

[@ahmadsheikh5710]

Very well explained

[@camlex6310]

Really comprehensive!! Cheers

[@KenjiExplains]

Thanks for watching!

[@topioproni]

Really Amazing

[@user-xy7xp8uk7l]

The best explanation

[@marriamrasib874]

Amazing video

[@carlmondala9960]

This is very useful. Thanks a lot!

[@KenjiExplains]

Thanks for watching!

[@ishasaini1082]

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

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

[@halagaming2603]

Thank you ao much

[@MohammadNatsirrockwood]

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

[@Triple_HHH]

Awesome!

[@EmiDKing]

Great video

[@Sjlove37]

You are cool! Thank you so much 🧡🧡🧡

[@Eternal_Truth8]

Thanks for the tutorial!

[@KenjiExplains]

Cheers!

[@Pedrinhololbr]

Amazing video! Thanks a lot

[@KenjiExplains]

Glad it helped!

[@maldridge7630]

I have to second all positive compliments herein 🤜🤛

[@sachin9yadav]

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

[@KenjiExplains]

Thank you for watching!

[@XLLearner_Courses]

Pro explainer. 👍

[@JackKing12.]

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

[@hamodeosso6507]

Great content bro!!

[@KenjiExplains]

Appreciate it!

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

[@magdahassib694]

Thanks, Excellent

[@KenjiExplains]

Glad you liked it!

[@issamelsayd]

Thanks Maestro

[@hisokaamorou4211]

learned a lot

[@Yo_Kelz]

Excellent. thanks!

[@KenjiExplains]

Glad it was helpful!

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

[@wooddog007]

Thanks

[@gurharkrishanbureau187]

V well explained

[@KenjiExplains]

thank you!

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

[@flourishwithMJ]

Thank you❤

[@KenjiExplains]

Thank you for watching! 🙏

[@user-id4rb3qo7k]

amazing

[@user-mo9vh6ig8r]

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

[@muntaser1988]

You are really cool
Thank you for the nice explanation❤❤❤❤

[@KenjiExplains]

Thank you! 😃

[@kenzokubo6352]

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

[@moesadr3342]

Thanks for all amazing training videos, short, sweet and right to the point but very through! Would you kindly let me know how I can use XLOOKUP up to search for the number of rows with a field includes certain value for This and/ or Last Week, This and/ or Last Month, This and/ or Last Quarter and This and/ or Last Year? How would the formula could look? Do you know a better solution without NO VBA? Thank you!

[@syedhussain7967]

Wow Excellent information...m from pakistan❤

[@sson3038]

Hi Kenji,
You are super good at explaining!
Do you know how to use the last formula on this video for summing up values but coming from another excel file?

[@joukenienhuis6888]

I am not that much of a professional, but to me it looks easier to import that figure into a cell with another function or a vba function and then use that result in the xlookup function.

[@natalieharrison5202]

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

[@saurabhramane1478]

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

[@nikhiljadhav4343]

Thanks!

[@KenjiExplains]

thank you for supporting Nikhil!

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

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

[@creatorkannan675]

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

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

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