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.
So far, you are the best Excel tutor I have come across on TH-cam. 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.
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!
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))
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!!!👍😀
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 !!!!
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.
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!
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.
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.
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?
sorry a question, how to extract certain rows from a massive data table? for example, the data table is the whole sales of every company. one row one company, including name, sales amount, location, other information. i only want to extra several companies which are my clients. i can't user filter cos my clients are so many. if i have another table listing all my clients, how to extract rows that are my clients? I searched and searched but couldn't find the answer.
Hey Yesshiro - It sounds like to me you would want to use a lookup to accomplish this. I would make a separate tab and in column A copy and paste all possible clients. Next, remove duplicates from this row. Then, go ahead in column B and label each client as either “My client” or “not my client”. You could use this tab as your table array and then perform a lookup on the master data. I hope this helps - if not, please let me know.
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👍👍👍
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!
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
Hey Kenji. In level 3, when I tried to select two (or more columns) in the return-array, excel keeps showing !Spill or #Value! error. It does not seem to automatically give two matches like shown in the video. This problem keeps happening when the look up values and the return values are on different worksheets. Do you have any tips on how to fix it? Thank you :)
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!
What if I have a dashboard whereI want to list names and their total commission throughout those years? I listed them with unique but unable to figure out to get the total commission for each those names
👉 Take our Excel Course! www.careerprinciples.com/courses/excel-for-business-finance
thank u coach
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?
Heavens! Please dont ever stop making such content.. I learned so much and so effortlessly. Thank you very much Sir!
You are the more complete yet the more simple tutorial that I've seen, straight to the point, thank you.
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.
So far, you are the best Excel tutor I have come across on TH-cam. 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.
Me too. Kenji has really been able to make it "click" in my mind!
Kenji has a real gift for communicating complex ideas into simple easy to understand language. He is simply the best!
Minus the fact we need to sign up to download his sample file
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!
Kenji you're an Excel goat, always making things comprehensive and concised, thanks for the video and the quick tips and tricks
Thanks for watching!
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!
Glad to hear that!
Thanks!
thank you for supporting Nikhil!
Dear, Your way of explanations, is the easiest on TH-cam, thank you so much
Omg. You are the super master of explanation. You are the man, the God and the ultimate. ❤❤❤❤❤❤❤❤❤ so beautifully done.
the last formula ox xlookup was exactly what i was looking for!
You by far have the best tutorial videos. Thank you for providing so many helpful videos.
Thanks bro, i thought that excel was hard, but you make it so simple to understand and i'm getting better day by day.
Excellent! Learning Day by Day Again with Excel thru you YT Channel. Please share more sample worksheets for Industrial Setting.🥰
Kenji literally explained it like it was nothing. Kudos!
This was so precise and to the point. I understood everything you said and appreciated the visual reference. GREAT TUTORIAL!
Nice. More of these, please! Combinations of beast functions
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))
Haven’t tested it but I think you’re right! Thanks for the info 👏
I knew there had to be a shorter way still.
Whoever you are, thank you for pointing this out. 🙏
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
@@domaugustus72 This is what i'm also thinking...👌
The last part is the most important for me! Kudos to you for sharing this information🎉
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!!!👍😀
Best and easiest explanation I have seen. Thanks for the content and simplicity!
This guy is a gem. Thank you Brother for your efforts.
Thank you for watching!
Awesome guide just what I needed. Much appreciate it!
Thanks to this video, I finally understand how to look up a result in the last situation mentioned in the video. Thank you.
Combining that with data validation is a neat combo for dashboards
Bro I love the quality of your videos keep up the good work
nicely done, you make it easy to understand... now I just have to remember it when I need it :) thx!!
Thanks!!! I have been looking the last part for long and now I can sleep in peace.
Damn i always used to change the array arrangement in order to use vlookup. Thanks for introducing xlookup
This was SO helpful! Thank you so much for posting this video.
This is the most helpful video ever. Thank you so muchhhhhh.
Excellent. 5⭐ description and examples. This will help me so much.
Thank you for sharing this invaluable knowledge. Good things you did will rebound on you.
One of the Best video on Excel.
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 !!!!
Awesome learning. Thanks for your explanation. This truly helped me to learn xloopup and also filter / sum in a unique way!
Thank you for you clear examples and explanation.👌
Amazing 👏.. I've got excited more with excel thank you with this knowledge.
Your delivery is top notch. 👌
Very informative! Where can I see the filter function? It is not available/doesn't work with my excel. Thank you!
If you are using office 2019 or earlier, I don’t think the filter function will be available.
If only one record, use xlookup, if multiple and need total sum, use sum filter. Got it 👍
wow!! Just wow!! you are really amazing!! I didn't know this was possible.
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.
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.
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!
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.
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.
Thank you so much Kenji, Excellent function of Filter sum.
Thank you for watching! 🙌
Excellent video! Pls could you do "The ULTIMATE Sum Formulas Guide | SUMIF, SUMIFS, COUNTIF, COUNTIFS"? Thanks!
Great suggestion! will note it :)
@@KenjiExplains Thanks Kenji! I did not know about the XLookup
Cool idea, those are all super useful functions to use!
thank you. you always make it look so easy to understand
Really appreciated your expertise
Very clean explaning .. Thank you very much for your videos :)))
Glad you like them!
I am very excited with your mode of teaching. however, xlookup is not working on my system.Why?
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?
I have windows 10 in my laptop and 2016 excel i guess , but why it doesn't showing XLOOKUP in the formula row . i cant find it ?
x lookup was introduced in excel 2021. It is also available in office 365
EDIT: excel 2019 not 2021
For the last one, you can use a sumproduct formula with two criteria to find the answer
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?
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 !?
Thanks, I liked the most here at Sum-filter part
sorry a question, how to extract certain rows from a massive data table? for example, the data table is the whole sales of every company. one row one company, including name, sales amount, location, other information. i only want to extra several companies which are my clients. i can't user filter cos my clients are so many. if i have another table listing all my clients, how to extract rows that are my clients? I searched and searched but couldn't find the answer.
Hey Yesshiro - It sounds like to me you would want to use a lookup to accomplish this.
I would make a separate tab and in column A copy and paste all possible clients.
Next, remove duplicates from this row.
Then, go ahead in column B and label each client as either “My client” or “not my client”.
You could use this tab as your table array and then perform a lookup on the master data. I hope this helps - if not, please let me know.
I will definitely enroll in your excel course. No doubt
Best tutor Ever!!!
Great content bro!!
Appreciate it!
Those very Useful Tips Brother💪
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👍👍👍
Hey thank you the file should be in your inbox :)
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!
You solved a big problem for me. Thanks a lot
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?
Really awesome teaching. Thank You ; )
What would I do if there is any repeating data in the table do I make a pivot table for the raw data or is there any other method
you really do explain kenji !
Thanks a ton
Well-done, your explanation is the best and understandable. Thank you
Kenji I didn't get the dynamic part of vlook up right
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
Hey Kenji. In level 3, when I tried to select two (or more columns) in the return-array, excel keeps showing !Spill or #Value! error. It does not seem to automatically give two matches like shown in the video. This problem keeps happening when the look up values and the return values are on different worksheets. Do you have any tips on how to fix it? Thank you :)
Thank you ao much
Can we do a sumifs function for the last one?
Can we do index match for the level 5 ? (I tried it doesn't work, I don't know wether I did a mistake or it just can't work)
It’s gonna only get the first value instead of the sum which is what we want so you won’t get the same answer as the sumfilters
What will the formula if I want if not found array for year as well
I have to second all positive compliments herein 🤜🤛
Thanks, Kenji for the awesome video.
That was smooth. Thank you.
filter formula was very useful thanks
great tutorials! Thank you!
what if our lookup array in range such as for grade..thank you
Xlookup all the way! 🏆
lets go!
Bro, your making me the beloved Excel guru at work 😂
one question. Ain´t that a bit redundant to put the column since you already putting the range of what you are looking into?
Hi Ken,
Xlookup formula is not coming up in my excel...
Thank you... 😀
That's what i was looking for.
Level four 🌟 solved my problem.
Also how do I search in a table using values from another cell?
Great content Kenji! Deserving to see next week's video
Thank you. Great lessons.
Nice...everyday is a skool day...
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!
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.
Very well explained
What if I have a dashboard whereI want to list names and their total commission throughout those years? I listed them with unique but unable to figure out to get the total commission for each those names
Is there any way to filer those values horizontally
Wow Excellent information...m from pakistan❤