I have always wanted to take an advanced excel/google sheets course. I have used spreadsheets since the old lotus 123 days. I use the count features a lot at work but don't know how yet to group and automate. however. I am learning pivot tables to help me with reports
I would really watch your excel classes for the whole day! You really make the formula so easy to understand and the places where it could be probably used. Thank you so much!
Seriously you explain things so easily that's faster for me to grasp! Straight to the point, easy and clear examples. You're my go to excel teacher on youtube!
4:55 Very informative! So the inner xlookup returns a column from a group of columns, while the outer xlookup returns a cell from a column (group of cells). 9:14 or return a row from a group of rows.
You are the man Kenji. I've worked in accounting for 7 years and consider myself very proficient in excel, however you've taught me so much in the few short videos I've found. New subscriber!
Kenji thank you for your help, I am also wondering how to use the formula for a different output. For example, in your third example, if I wanted to know which employee had 7,392 in sales in February how would I use the formula to populate the name Sergio Perez?
This is a great video, simple, easy to follow, and I appreciate the file to follow along with. Question, is part 2, why do we use the wildcard character match "2"? Is it to catch the last name associated with wildcard search?
Kenji, can you lookup multiple criteria and return multiple arrays with "&"? could you demonstrate how to set this up? i think i had it working at one poin
Hi Kenji, This was an excellent refresher of the XLOOKUP function for me. Thanks for all your efforts. 👍 For the Limitation example can we also make use of the FILTER function as mentioned below: =SUM(FILTER($D$3:$D$18,$B$3:$B$18=F3)) Please confirm. Regards, Deepak.
dude, how is it better than the index match? how will you use an xlookup when the source data table and the results table have column headers in different orders?? in that case please tell me how xlookup can solve it.
Will xlookup provide non-contiguous rows as output. It was good to know it displays multiple. Thank you for the video. It helps to understand xkoolup basics
Hello sir I need some help that by interning only ID Number of an employee so I can get all the information about that employee like Name,last Name,department that he/she work in if can you help me that will be very nice?
Can copilot 365 and other ai tools do all type of works in ms Excel Or is it still important to learn ms Excel deeply all functions and advance formulas ??
Amazing tutorial, only thing was on example 5, I was thinking it might be easier to do the following as it allows the cells to be populated as well as different headings to be used =XLOOKUP($H3,$B$3:$B$17,XLOOKUP(I$2,$C$2:$F$2,$C$3:$F$17))
Very insightful video and thank you for that. But you mentioned that Xlookup only allows one lookup value. I believe I can add up multiple lookup values by adding the & in the lookup_value section right? But I can also see that it wouldn't work efficiently in this specific example because we have more than one months, so a nested xlookup is better option of course.
@@zmm3492 Xlookup in general replaces Vlookup in many ways, like Vup can't look to the left or look horizontally or it can't give you back multiple answers as shown in the video.
I have a different issue. I want to look-up a value on a separate sheet, so price! No problem However, I have 2 columns. EFT and Cash Different return array, Individually I can make this happen. The problem I'm trying to simplify is as follows. If a value is entered in the EFT column then Cash might be 0 or a split payment value. Assuming split is a rare option, I want to say If EFT is empty then cash should look-up price! Cash column, if EFT is filled then cash should be considered null. The total value is split So if payment is cash $190, house keeps $60, worker gets $130. If payment is EFT then total is $205 so house keeps $75 and worker gets the same $130 as before. Therefore many fields rely on other values. At the moment I have cash setup to look-up time then return value, then worker looks-up and returns a value and house is one subtracted from the other eg cash 190 - worker 130 so house = 60 If you are now confused then I guess you understand why I am also 😅
Thanks for this impactful teaching. However, I keep getting this error on the last one 5 (#VALUE!). I am using 2019 Excel. don't know if that's the reason for the error. Kindly assist
Very helpful video! If I had more than one employees with the same name, could I use the wildcard method to find whichever employee I'd want? If not, which method is the most efficient?
👉 Take our Excel Course: www.careerprinciples.com/courses/excel-for-business-finance
I have always wanted to take an advanced excel/google sheets course. I have used spreadsheets since the old lotus 123 days. I use the count features a lot at work but don't know how yet to group and automate. however. I am learning pivot tables to help me with reports
I would really watch your excel classes for the whole day!
You really make the formula so easy to understand and the places where it could be probably used.
Thank you so much!
Seriously you explain things so easily that's faster for me to grasp! Straight to the point, easy and clear examples. You're my go to excel teacher on youtube!
4:55 Very informative! So the inner xlookup returns a column from a group of columns, while the outer xlookup returns a cell from a column (group of cells). 9:14 or return a row from a group of rows.
Thanks Teachers, the way of your teaching style is clear , perfect and easy to understand.
You are the best !
I am in a bootcamp for data analytics and I found this video so helpful
I love how you clearly explain how to apply formulas .....sending love from Kenya
I'm from Tanzania
Thanks for your clear, direct and informative instruction. I have subscribed.
You are the man Kenji. I've worked in accounting for 7 years and consider myself very proficient in excel, however you've taught me so much in the few short videos I've found. New subscriber!
May god bless you, you made it very very easy, my teacher couldnt explain it in 3 weeks
Deep Heartly Gratitude and Love for all your precious time and efforts....
God bless you ❤️🎉
Thank you for making Excel so much easier to learn and apply. Looking forward to more such amazing content!
You are such an amazing tutor.Thankyou so much.I subscribed
Excellent tutorial. Really appreciate you breaking it down and making it simple. Love the shortcuts
I am from India, Kolkata (West Bengal) Learn a lot watching your video. Wow...................
You are a good Excel teacher. I understand you easily. Thanks
Thanks for mentioning the shortcuts everytime you use them!
Yes, I agree. One of the functions I use daily
really helpful to explain this by you kenji
glad it was helpful!
Great video Kenji🎉
brilliant. thank you so much Kenji for the tutorial video! love from Malaysia
Kenji is the real GOAT 😊😊❤❤. Thank you for all your videos man,..
I am a beginner, still receiving class. I enjoy watch your videos, very self explanatory
💯/ 💯 brilliant..Bro Kenji💪Dubai
You are amazing man!
We appreciate you, keep on good work
From Nigeria
Pieeeeerre Gaaaslyyyyyyy 😁
Your videos are simply lovely :)
hahahhaa Thank you!
Daniel Ricciardo
Kenji thank you for your help, I am also wondering how to use the formula for a different output. For example, in your third example, if I wanted to know which employee had 7,392 in sales in February how would I use the formula to populate the name Sergio Perez?
This is a great video, simple, easy to follow, and I appreciate the file to follow along with. Question, is part 2, why do we use the wildcard character match "2"? Is it to catch the last name associated with wildcard search?
9.02 if the return array is not in order then what should we de
amazing always, very highly appreciated Kenji
Thanks for watching :)
Kenji, can you lookup multiple criteria and return multiple arrays with "&"? could you demonstrate how to set this up? i think i had it working at one poin
I used sumproduct for the last limitations part
Thanks for your helpful video!
Great video!!
Glad you enjoyed it
Hi Kenji,
This was an excellent refresher of the XLOOKUP function for me. Thanks for all your efforts. 👍
For the Limitation example can we also make use of the FILTER function as mentioned below:
=SUM(FILTER($D$3:$D$18,$B$3:$B$18=F3))
Please confirm.
Regards,
Deepak.
Very informative. Thanks.
In Level 3... You looked up one row and one column but is it possible to look up 2 columns and return an array? Please advise.
You did great Job.. you got one more subc.
I like this video
Super video yet again!!!
dude, how is it better than the index match? how will you use an xlookup when the source data table and the results table have column headers in different orders?? in that case please tell me how xlookup can solve it.
Will xlookup provide non-contiguous rows as output. It was good to know it displays multiple. Thank you for the video. It helps to understand xkoolup basics
you are the best
u made this shit fun, i didnt jnow u could xlookup xlookup
hhaah thank you!
Hi Kenji, what laptop do you use? thanks
Cute that you think Verstappen is a difficult last name! Greetings from The Netherlands :)
I have a dropdown list, and I want each list to return to a specific link. What would the formula be for hyperlink xlookup??
great videos, thank you.
Love the F1 references
Thanks!
Kenji, what if the columns were not named in months? On Vlookup we used column number of the table array.
Thank you
hi i am having a issue i am selling some item for someone but i will like to see what from the sale is mine and what is his how do i work that out ?
Amazing! I was only familiar with the first formula😂❤
Really helpful and informative, but I have a query : What if we have same first name but different last name, How to apply the formula then?
Hello sir I need some help that by interning only ID Number of an employee so I can get all the information about that employee like Name,last Name,department that he/she work in if can you help me that will be very nice?
How to ignore blank cell reference in xlookup function? (Its returning blank cell value if cell reference and search criteria blank)
Can copilot 365 and other ai tools do all type of works in ms Excel
Or is it still important to learn ms Excel deeply all functions and advance formulas ??
Sir please ans me
Great thanks so much
Excellent video,,
Amazing tutorial, only thing was on example 5, I was thinking it might be easier to do the following as it allows the cells to be populated as well as different headings to be used
=XLOOKUP($H3,$B$3:$B$17,XLOOKUP(I$2,$C$2:$F$2,$C$3:$F$17))
which one is best xlookup or index match?
I am wondering what would happen in the Example 2 if there are more people with the same surname?
Thank you very much.
I agree with you with SUMIF.
youtube.com/@markrowley9801
how it works for the last part for text
Sumif or sum & filter .. both will work
Amazing :)
Wonderful 😍
One request please make video on Three statement model from scratch using assumptions...
coming next week! Stay tuned :)
@@KenjiExplains Thankyou Man 🥺❤️
what are you pressing with F4 to append $? It doesn't work for me
Depending on your keyboard you may need to press the fn key simultaneously
Just for fix/lock the formula for dollars $ sign
Fn
Hello! For each formula I added I received an error #VALUE! Any ideas?
Xlookup gang
Lets go!
How can I use the Xlookup formula to find information across multiple worksheets (not workbooks)?
That's easy, just select the area on the desired tab. Info doesn't all need to be on the same sheet.
Wow Kanji you are he goat i will replicate this and tagged you .. take your 🌺🌺🌺🌺🌹🌹
Very insightful video and thank you for that. But you mentioned that Xlookup only allows one lookup value. I believe I can add up multiple lookup values by adding the & in the lookup_value section right? But I can also see that it wouldn't work efficiently in this specific example because we have more than one months, so a nested xlookup is better option of course.
How does this replace vlookup then?
@@zmm3492 Xlookup in general replaces Vlookup in many ways, like Vup can't look to the left or look horizontally or it can't give you back multiple answers as shown in the video.
Nice presentation, Maybe you needed to add that this function is available for Office 365, Excel 2019 and above.
Nice
I have a different issue.
I want to look-up a value on a separate sheet, so price!
No problem
However, I have 2 columns.
EFT and Cash
Different return array,
Individually I can make this happen.
The problem I'm trying to simplify is as follows.
If a value is entered in the EFT column then Cash might be 0 or a split payment value. Assuming split is a rare option, I want to say
If EFT is empty then cash should look-up price! Cash column, if EFT is filled then cash should be considered null.
The total value is split
So if payment is cash $190, house keeps $60, worker gets $130. If payment is EFT then total is $205 so house keeps $75 and worker gets the same $130 as before.
Therefore many fields rely on other values.
At the moment I have cash setup to look-up time then return value, then worker looks-up and returns a value and house is one subtracted from the other
eg cash 190 - worker 130 so house = 60
If you are now confused then I guess you understand why I am also 😅
thanks
i am been frustrated with with #NAME? error. i followed your steps but still ends up pop up the error. Xlookup( xlookup value,array, return value)
Plzzzzz.... Plzzzz..pllzz sir make a video of basic to advance level excel.🙏
It is something good, but for the Value I do the Data Validation which saves typing time 😉
Last problem can be solved using sumif 10:15
Sir can U plz make a video of basic excel to advance level. 🙏
SumIf for me in the last one too.
also works XLOOKUP combined to SUM formula
Interesting.
where are your country ?
Thanks for this impactful teaching. However, I keep getting this error on the last one 5 (#VALUE!).
I am using 2019 Excel. don't know if that's the reason for the error. Kindly assist
I have tried many times and do not know why it's not working in the MULTIPLE ANSWERS scenario.
Love the F1 names lol
Hahaha glad you noticed!!
Can we use XLOOKUP in Excel tables?
Very helpful video! If I had more than one employees with the same name, could I use the wildcard method to find whichever employee I'd want? If not, which method is the most efficient?
If you can make one video that will be so nice
i didn't get output
Always work in tables, never in ranges.
Why?
Lewis Hamilton should earn the highest
You're using vertical arrays, but can't undersatnd how to use the function in horizontal arrays...
formula 1 fan?
ahah glad you noticed