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))
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.
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.
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!
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.
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?
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.
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 :)
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
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!!!👍😀
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👍👍👍
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
The biggest issue I have with VLOOKUP (apart from the requirement for lookup range to be on the left of the range) is that you can't change the structure of your data table (order of the columns) or it will break silently (no #N/A, just the wrong return value). XLOOKUP fixes this, and for pre-2019, INDEX & MATCH
Hi Kenji would it be possible to index match based on a value of the comission belonging to a year (2021) and return the name if the person that did this sale? I want to display the name of the person depending on the rank of that year (my data is column A : Name Column B : Data for year 2021/01 (rank of the names column) thanks in advance!
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 !!!!
Firstly thanks for the making the video, i have tried Level 3 formula but its not working, get error as #value!. I am using the same file to check and practice... can you please help me in understanding why is this error is coming..
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!
Hey, how can we apply lookup for a Date range, That is suppose if have a date range from-to and want it to return the same value in all How can I do that, please guide
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!
Hi Thank you , question i have pms file i want to know is there any way to excel calculate by own and find 3rd date between start and finish date with formula and gives best match date between 2 start and finish date , it's example that can expand for 1000 tasks Imagine i have 3 tasks with 5 days duration start is 1/1/22 and finish is 1/12/22 so first task start 1 day and 3rd task start 1/7/22 now I want excel calculate and find best date between in 2 tasks that must be 1/4/22 Now is there anyway excel calculate and find this date, this is can use for 1000 tasks more or less that I need to find it 1000000 tnx if you help me 🌹🌹🌹🌹 Thank you 🌹🌹
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.
in xlookup level 4 - match nodes: what if there is another branch of the company like Amazon EU or Apple APAC, what would happen when there are multiple matches of the same name within the look up array?
The nested filter formula breaks once I convert the data array to a table format. The error lies with the horizontal year filter. How can I prevent this?
👉 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?
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
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
Heavens! Please dont ever stop making such content.. I learned so much and so effortlessly. Thank you very much Sir!
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.
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!
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!
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
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!
If only one record, use xlookup, if multiple and need total sum, use sum filter. Got it 👍
Why not just use SUMIFS for the last examples? Great video though.
sumifs wouldn't work because the data is vertical (the names) and horizontal (the years)
Damn i always used to change the array arrangement in order to use vlookup. Thanks for introducing xlookup
For the last problem you could just use a sumif function.
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.
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?
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.
Excellent! Learning Day by Day Again with Excel thru you YT Channel. Please share more sample worksheets for Industrial Setting.🥰
Thank you. Lord Jesus Christ loves you all
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.
Xlookup How to Create Mark Sheet in MS Excel step by step Fully Automatic Marksheet In Excel . horizontal to vertical converter
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
XLOOKUP will definitely save me a lot of time to ge the data faster. (Me only know vlookup 🤣🤣)
Omg. You are the super master of explanation. You are the man, the God and the ultimate. ❤❤❤❤❤❤❤❤❤ so beautifully done.
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
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?
You are the more complete yet the more simple tutorial that I've seen, straight to the point, thank you.
For the last one, you can use a sumproduct formula with two criteria to find the answer
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
What's stopping our colleges to teach us like this?
Like why not?
Thank you❤
Thank you for watching! 🙏
Couldn't have the download for this lookup concise tutorial video
Kenji I didn't get the dynamic part of vlook up right
Hi Ken,
Xlookup formula is not coming up in my excel...
Thank you Brother ❤️ May ALLAH bless you 🤍
Every time I try it I am having value errors can somebody help
Also how do I search in a table using values from another cell?
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.
I did not get xlookup 3 and 4 huhu some fucntion onmy excel did not show up
Just when I thought you were done you add SUM, dude you are scary!
Awesome guide just what I needed. Much appreciate it!
can anyone tell me those who have excel 2010 how will they use xlookup?
what if our lookup array in range such as for grade..thank you
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!!!👍😀
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 :)
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
The biggest issue I have with VLOOKUP (apart from the requirement for lookup range to be on the left of the range) is that you can't change the structure of your data table (order of the columns) or it will break silently (no #N/A, just the wrong return value). XLOOKUP fixes this, and for pre-2019, INDEX & MATCH
What will the formula if I want if not found array for year as well
Nice. More of these, please! Combinations of beast functions
Thanks
Thanks
Quick question can I everytime I use VLOOKUP/HLOOKUP also use XLOOKUP?
yes
Level 5 is similar to combination of index and match function!
Yes i agree!
9:59 He felt so sad saying and I can feel his expressions when he said XLOOKUP does have limitations.
Can I introduce range in xlookup?
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?
Thank you so much Kenji, Excellent function of Filter sum.
Thank you for watching! 🙌
why there is no xlookup in my excel?
Great video mate, for level 4 XLOOKUP, what if there are multiple values, it only shows the first one.
any one got any suggestions?
Can we do a sumifs function for the last one?
Hi Kenji would it be possible to index match based on a value of the comission belonging to a year (2021) and return the name if the person that did this sale? I want to display the name of the person depending on the rank of that year (my data is column A : Name Column B : Data for year 2021/01 (rank of the names column) thanks in advance!
File in the Description???? I cannot find it
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 !?
What do eat in breakfast? you are soo smart.
Amazing 👏.. I've got excited more with excel thank you with this knowledge.
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 !!!!
nicely done, you make it easy to understand... now I just have to remember it when I need it :) thx!!
Firstly thanks for the making the video, i have tried Level 3 formula but its not working, get error as #value!. I am using the same file to check and practice... can you please help me in understanding why is this error is coming..
wow!! Just wow!! you are really amazing!! I didn't know this was possible.
Xlookup = vlookup on steroids 😂😂😂
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!
HI!! can you upload a video explaining Vlookup when there are du[plicate Lookupvalues in lookup array ?
Hey, how can we apply lookup for a Date range, That is suppose if have a date range from-to and want it to return the same value in all
How can I do that, please guide
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.
bye bye to vlookup and hlookup thern
Thanks bro, i thought that excel was hard, but you make it so simple to understand and i'm getting better day by day.
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 Thank you , question i have pms file i want to know is there any way to excel calculate by own and find 3rd date between start and finish date with formula and gives best match date between 2 start and finish date , it's example that can expand for 1000 tasks
Imagine i have 3 tasks with 5 days duration start is 1/1/22 and finish is 1/12/22 so first task start 1 day and 3rd task start 1/7/22 now I want excel calculate and find best date between in 2 tasks that must be 1/4/22
Now is there anyway excel calculate and find this date, this is can use for 1000 tasks more or less that I need to find it
1000000 tnx if you help me 🌹🌹🌹🌹
Thank you 🌹🌹
Bro, your making me the beloved Excel guru at work 😂
Sum filter filter aka pivot table
Thank you... 😀
That's what i was looking for.
Level four 🌟 solved my problem.
Wow Excellent information...m from pakistan❤
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
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.
Thanks, Excellent
Glad you liked it!
Really awesome teaching. Thank You ; )
Thanks!!! I have been looking the last part for long and now I can sleep in peace.
the last formula ox xlookup was exactly what i was looking for!
Why i can't find xlookup in my excel?
Think it just came in 2021 so if you have earlier version of excel you won’t see it
in xlookup level 4 - match nodes: what if there is another branch of the company like Amazon EU or Apple APAC, what would happen when there are multiple matches of the same name within the look up array?
Xlookup 3 sheet formula did not work. It shows error
Nice and Thanks very informative and useful
The nested filter formula breaks once I convert the data array to a table format. The error lies with the horizontal year filter. How can I prevent this?
Thanks, I liked the most here at Sum-filter part
Combining that with data validation is a neat combo for dashboards
Just asking what if there is same name but different lastname, what will be the output? unless we use the fuzzy for the percentage rate.
You by far have the best tutorial videos. Thank you for providing so many helpful videos.
This was so precise and to the point. I understood everything you said and appreciated the visual reference. GREAT TUTORIAL!
Excellent. 5⭐ description and examples. This will help me so much.
I have to second all positive compliments herein 🤜🤛