Hi Kenji, on the excle sheet when I was practicing there was an error and I don't know where it originated from. My calculation for time of arrival was showing show negative all the way down. e.g -225 What could be the cause of this? Thank you again for your cooporation
WOW KENJI! I'm improving a lot my EXCEL skills thanks to your courses. My workmates are hallucinating how fast I apply formulas and how quickly I finish my assignments! CHEERS!
Thanks for the video. A few comments: you can use CTRL + ~ to toggle show formulas on and off if you prefer keyboard vs the mouse click option. I would say that there is a level 6 or higher lookup formula that was not mentioned. Its a combination of indirect + index match. This lets you dynamically select the array in question as opposed to having to choose the array manually. The formula would look like index(indirect(concatenate("C", match(),":","C", match()), False),match(),match()). In this example, you can specify criteria by dynamically creating an array address and then looking up a value within that address based on separate criteria. It's definitely a complex scenario but I've come across this before and needed to break free from hard coding the initial array of the index.
Great video, thank you! I’m 57 and I used Lotus 1-2-3 on DOS where you had to tell it how many rows and columns you wanted. I’ve been using Microsoft Office since 1998 and the formulas and features that Excel has is always growing, but it continues to be glitchy. I have to use the Trim(Substitute formula to convert cells to where I can get rid of leading spaces and format them as currency or whatever I want. Your channel has great content but I’m pretty sure you’d get extra subs and a lot of views if you did a video on the 10 best Excel workarounds and how to auto save and find xlsb files that aren’t in Recover files. Those are the things that save time and frustration!
Thank you so much for sharing this, it is about 25 years since I did my Excel training, so this has been a brilliant refresher; as well as learning so much more. Many blessings.
Excellent video. As someone who wrote their own accounts programme in Excel and VBA, I knew all the formulas but didn’t know the level one stuff. Sequence, CTRL, shift down arrow and using the F4 key to lock in the $ sign. Well done!
Hey a few things to try. Xlookup replaces ALL functions of Vlookup and IndexMatch and Hlookup, so if you are using any of these functions in any way Xlookup can do it. If you want to look up 2 values you need to use &. Example =XLOOKUP(lookup_value & lookup_value, lookup_array & lookup_array, return_array,””,0,1) this would be used for a vertical data set like in your example. You can also have 2 lookups used in a table format to return the cell in the table where the two cells meet at. =XLOOKUP(lookup_value1,AH3:AH13,XLOOKUP(lookup_value2,AI2:AM2,AI3:AM13,"",0,1),"",0,1) I left the cell references in, but you can get the picture. Also don’t forget about returning non adjacent rows =XLOOKUP(lookup_value2, lookup_array,INDEX(return_array,SEQUENCE(ROWS(return_array)),{1,3,4}),"",0,1) These are just a few examples of what you can do. Great video, but I have to admit that I was more then a little upset with the title of the video being “Make You a PRO User” and then not to learn anything new. I am an average excel user so ….😅
I watch all the advertisements completely that runs at beginning, middle, at the end of video , to make sure the sponsors contribute to your efforts. Best regards from New Delhi India🇮🇳 🇮🇳
@KenjiExplains The content you produce is very practical and useful for working with finance. Thank you very much for sharing. I have a question, I am interested in the LBO valuation model, can you create a video on this topic? Thank you in advance.
Always learning new skills and tricks in Excel, since I started with spreadsheets back with Lotus 123 rel 3.01 a long time ago, and back when you were limited to a lot less columns and rows. I used to use the string functions to extract data from a single column that I stored information in.
Brother this will be my start from today and upto to the newest video i will develop myself to the professional expertise level so i can add this to my resume , im just college student but looking forward to develop further than i was than never ever i have been
Great video!!! The tips will make my life so much easier. 2 questions: 1) in the Revenue by State and Year I formatted the state and year as data validation lists so I could easily change either of the them (or both) and it seemed to still give me the correct Revenue amount. Is there a reason this would not be good practice? 2) When we used the Xlookup for the Product Arrival Date we returned one product (Dasani Water) for 5/16/22 but there were in fact 3 products with that same arrival date. How can you be sure you are returning ALL products arriving on that date? Thank you so much, I will continue to watch ever video you make.
On your first example of IF function where you compare two values... you can just also use the "CELL=CELL" ex: =C2=C19 then it will put a result indicating TRUE if the values are the same or FALSE if it is NOT... Then on the finding the link of the FORMULA created.. you can also doble-click on cell with formula in it and it will highlight all the included cell in that FORMULA.
Thanks for your video. I don’t use index match but the others are frequent flyers. Xlookup is a great replacement. Prior we had to do helper columns if our data was was in left to right. So many new features to include power Query which is a godsend. Nice job on your channel content.
I was kind of expecting the usage of spilling later on in the video. Also regarding the question how many I already knew… I already knew them all, but I still liked the video as I think it does have valuable information for people whom aren’t aware of these formulas
an alternative to the index match is SUMIFS. it can take different conditions from different columns, and since conditions are generally unique, it will sum only 1 value i.e, it will just return that value. if multiple conditions are to be selected from the same column, one can do a SUMPRODUCT(SUMIFS
Hi, I didn't know the bonus tips. Really useful. I knew a few of the formulas. I knew of them all but hadn't had the need to use them. Now I can see that they would be useful in my work: especially "Index - Match". Thanks
Great refresher Kenji! Bravo! Just a question regarding Xlookup; i.e., it can be the case that multiple products arrive at the same day. How do you capture multiple products that arrived on the same day?
This is so awesome. I knew a lot of the formulas but not all. I will definitely watch this and make notes. @kenji you should have a PDF summary for all formulas covered.
@@KenjiExplains Sell the summary for 2-3 dollars. You have many viewers who benefit from your videos. You should benefit as well. If you manage 200,000 sales by the end of the year at 2 dollars each…quick 400? Food for thought. I would gladly pay two dollars for this video’s summary.
I want to clean data based on calculated values and highlight them. For instance when a cell value is devided by 3 it should return a number without a coma. E. G 15/3 =5 "don't highlight. 14/3= 4.6 '' highlight"
Despite the clickbaity title and that most fas not useful funktion in my uses, this was a good quality video. And the IFERROR did finally fix one annoying issue I had. Although purely aesthetic.
Amazing video. I had an issue with the Vlookup. Although I had the correct items in the list it still showed N/A when I typed. When I copied the cell and pasted it showed the correct amount 🤔
Guess I'm a level 4.5 I've never used xlookup nor sumifs just sumif, but the rest I've used . I've needed xlookup I'll try it out going forward. Thanks 😊
How do you decide on a career and job profile to target in college like my batchmates are aiming for IB and stuff but I dont exactly know how to go about it? I am really confused there are so many different things .... I started learning financial modelling and excel not from you though 😅
Get 25% OFF Financial Edge using code KENJI25: bit.ly/3xNV4VW
Hi Kenji, does this course cover PowerPoint for investment presentations too?
Hi Kenji, on the excle sheet when I was practicing there was an error and I don't know where it originated from. My calculation for time of arrival was showing show negative all the way down. e.g -225 What could be the cause of this? Thank you again for your cooporation
This video is so underrated, it should've reached 100000 views by now
Thank you!! Hopefully does haha 😆
@@KenjiExplains good luck and keep em coming 👍👍👍👍👍👍
× 5
So true
@@KenjiExplains Thank you so much, Kenji 😊😊👍👍👍 You're our HERO 🙇🙇🙇🙇♀️🙇♀️🙇♂️🙇♂️🙇♀️🙇♀️🙇♀️
I can't thank you enough. You break each point down and put it in context.
Although it's not rocket science, some tutorials make it very much so!
You by far have the best tutorial videos. Thank you for providing so many helpful videos.
WOW KENJI! I'm improving a lot my EXCEL skills thanks to your courses. My workmates are hallucinating how fast I apply formulas and how quickly I finish my assignments! CHEERS!
They don’t k know Kenji
Careful! Management will often get rid of them, give you double-work and pay you the same! That's when you need to become a consultant! :-)
Thanks for the video. A few comments: you can use CTRL + ~ to toggle show formulas on and off if you prefer keyboard vs the mouse click option. I would say that there is a level 6 or higher lookup formula that was not mentioned. Its a combination of indirect + index match. This lets you dynamically select the array in question as opposed to having to choose the array manually. The formula would look like index(indirect(concatenate("C", match(),":","C", match()), False),match(),match()). In this example, you can specify criteria by dynamically creating an array address and then looking up a value within that address based on separate criteria. It's definitely a complex scenario but I've come across this before and needed to break free from hard coding the initial array of the index.
I think the formula you have written is something I'll have to work on to understand!
You say match() in your formula, why isn’t there anything filled in?
@@joukenienhuis6888 it's for your data
I am 46 and still learning excel, you have been of immense help. I knew some of these formulas but not all. Thank you kenji
Awesome to hear Antony :)
Great video, thank you! I’m 57 and I used Lotus 1-2-3 on DOS where you had to tell it how many rows and columns you wanted. I’ve been using Microsoft Office since 1998 and the formulas and features that Excel has is always growing, but it continues to be glitchy. I have to use the Trim(Substitute formula to convert cells to where I can get rid of leading spaces and format them as currency or whatever I want. Your channel has great content but I’m pretty sure you’d get extra subs and a lot of views if you did a video on the 10 best Excel workarounds and how to auto save and find xlsb files that aren’t in Recover files. Those are the things that save time and frustration!
Thank you so much for sharing this, it is about 25 years since I did my Excel training, so this has been a brilliant refresher; as well as learning so much more. Many blessings.
Thanks for the bonus tip. I am beginner it helps a lot for me 😄
Wow! Bonus tips are life savers when working with complex project files. Thanks!
Thank you for showing the index match. That is. Exactly what I need.
Great to hear!
best thing of your videos is, you keep it simple and short. thanks for sharing knowledge.
Kenji, You are a very good teacher /lecturer and a slick marketer. Wishing you all the best
I love your videos! Learning excel for free at home and I feel so confident!
Excellent video. As someone who wrote their own accounts programme in Excel and VBA, I knew all the formulas but didn’t know the level one stuff. Sequence, CTRL, shift down arrow and using the F4 key to lock in the $ sign. Well done!
thank you!
Excellent work, one of the most helpful excel tutorials i've ever seen.
the best excel teacher in youtube, good job.
Hey a few things to try. Xlookup replaces ALL functions of Vlookup and IndexMatch and Hlookup, so if you are using any of these functions in any way Xlookup can do it. If you want to look up 2 values you need to use &. Example =XLOOKUP(lookup_value & lookup_value, lookup_array & lookup_array, return_array,””,0,1) this would be used for a vertical data set like in your example.
You can also have 2 lookups used in a table format to return the cell in the table where the two cells meet at. =XLOOKUP(lookup_value1,AH3:AH13,XLOOKUP(lookup_value2,AI2:AM2,AI3:AM13,"",0,1),"",0,1) I left the cell references in, but you can get the picture.
Also don’t forget about returning non adjacent rows =XLOOKUP(lookup_value2, lookup_array,INDEX(return_array,SEQUENCE(ROWS(return_array)),{1,3,4}),"",0,1)
These are just a few examples of what you can do. Great video, but I have to admit that I was more then a little upset with the title of the video being “Make You a PRO User” and then not to learn anything new. I am an average excel user so ….😅
I knew all of them and use them constantly. Your videos are well done.
When Kenji posts, you watch. Simple. Best Xcel Channel ❤
I watch all the advertisements completely that runs at beginning, middle, at the end of video , to make sure the sponsors contribute to your efforts.
Best regards from New Delhi India🇮🇳 🇮🇳
you are the first who attach the file with which I can practice! thanks! you deserve my subscribe.
Thank you, its the first time ive understood some of the more complex formula's
@KenjiExplains The content you produce is very practical and useful for working with finance. Thank you very much for sharing.
I have a question, I am interested in the LBO valuation model, can you create a video on this topic? Thank you in advance.
I love how you explain everything. Easy to understand. Thank you,
thanks! I didnt know the xlookup and was always confuse on how to use index match. it was very clear
A big thumbs up for this video, those indexmatch functions save my day, thank you!
Thank you, Kenji! Insightful, clear and concise.
Always learning new skills and tricks in Excel, since I started with spreadsheets back with Lotus 123 rel 3.01 a long time ago, and back when you were limited to a lot less columns and rows. I used to use the string functions to extract data from a single column that I stored information in.
Thank you, i was trying very hard to understand this formula, your videos help me a lot. Thank you
I really need a 1 hr video of you explaining these excel analysis. great job bro and much love from kenya
Hi kenji
I know all, except for the sequence and the strings
Learnt something.
Thank you very much.
I've never used the indexmatch, well I learned a new one so thx for that...
So going by this video I'm an excel pro. Great. Please Kenji do a video explaing the Offset function that's one formular I'm yet to understand
Thank you make these formulas so easy to understand.
Hello Kenji ,you are a real tutor.love your lectures
Thanks sir for the lecture and for indirectly reminding me of what should be done to iron things in the future in particular.
Nice video. Up to the point. I use all of aforementioned formulas and I found it helpful for a quick reference/reminder
Hi Kenji, would love to see a video following up your one on PE. Could touch on carry, co-invest,waterfalls, etc.
Wow! Great video. I knew some of the 1 to 3 stars and vlookup formula. Very useful video as well as bonus tips. Thanks for sharing!
Very useful video thank you! I do use all these functions for my accounting job
Brother this will be my start from today and upto to the newest video i will develop myself to the professional expertise level
so i can add this to my resume , im just college student but looking forward to develop further than i was than never ever i have been
This was awesome thank you! I love these short vids.
Fantastic video! Will binge all your vids now , so helpful
It's nothing as satisfying as knowing your framework.
Great job with this collection of formulas/functions!
This is awesome. Thank you very much. The presentation is top notch.
Thank you again Kenji, appreciated!
Thank you soo much..I thought I knew excel but I was humbled quick..This video is so amazing.Learned a lot.Gonna watch the others too ❤🤩
Thanks for the course. Didn’t know the xlookup. You are a clear and straightforward instructor.
Great video!!! The tips will make my life so much easier. 2 questions: 1) in the Revenue by State and Year I formatted the state and year as data validation lists so I could easily change either of the them (or both) and it seemed to still give me the correct Revenue amount. Is there a reason this would not be good practice? 2) When we used the Xlookup for the Product Arrival Date we returned one product (Dasani Water) for 5/16/22 but there were in fact 3 products with that same arrival date. How can you be sure you are returning ALL products arriving on that date? Thank you so much, I will continue to watch ever video you make.
Excellent video, so useful, and sure will be handy on a day to day use. Thank you for sharing, I am so grateful.
great tips as alwas keep going kenji i've learned a lot from you thank you
Thanks Kenji, great video. You make complex formulae easy to follow and understand.
Outstanding! Dear Kenji: best regards from Limón, Costa Rica
Knew them all but I enjoyed the video.
I didn't know where those buttons were for showing formula and formula dependants.
Great vid as always.
Awesome to hear, glad you enjoyed it!
I knew O out of 10 formulas. THANK YOU!
today by chance saw your tutorial. thank you for great video.
Index match match is what I didn’t use much and really needed it
I proly use like 2-3 of these on a regular basis ; def not the first couple though. nice vid
thank you very much Kenji. This is very useful, just thanks a lot.
You absolute legend. Thank you.
Acabo de encontrar tu canal, es lo máximo. ¡Muchas gracias!
The best excel tutorial that I have come across
Thank you!
On your first example of IF function where you compare two values... you can just also use the "CELL=CELL" ex: =C2=C19 then it will put a result indicating TRUE if the values are the same or FALSE if it is NOT... Then on the finding the link of the FORMULA created.. you can also doble-click on cell with formula in it and it will highlight all the included cell in that FORMULA.
I was taking the excel course for the exam and this definitely helps reviews the formulas again so thanks for the video!!
1:03
2:11
4:39
8:14 Ag 11:27
your videos keep getting better keep it up!
Thank you man
Thank you very much. The content was useful for me.
Thanks for your video. I don’t use index match but the others are frequent flyers. Xlookup is a great replacement. Prior we had to do helper columns if our data was was in left to right. So many new features to include power Query which is a godsend. Nice job on your channel content.
I recently discovered power query too, it is indeed a time saver too
I was kind of expecting the usage of spilling later on in the video.
Also regarding the question how many I already knew… I already knew them all, but I still liked the video as I think it does have valuable information for people whom aren’t aware of these formulas
Great explanation. thank you for your videos.
an alternative to the index match is SUMIFS. it can take different conditions from different columns, and since conditions are generally unique, it will sum only 1 value i.e, it will just return that value.
if multiple conditions are to be selected from the same column, one can do a SUMPRODUCT(SUMIFS
Hi, I didn't know the bonus tips. Really useful. I knew a few of the formulas. I knew of them all but hadn't had the need to use them. Now I can see that they would be useful in my work: especially "Index - Match". Thanks
Thank you for improving my knowledge. :)
Great refresher Kenji! Bravo!
Just a question regarding Xlookup; i.e., it can be the case that multiple products arrive at the same day. How do you capture multiple products that arrived on the same day?
You'd have to change how the data is displayed perhaps by creating a pivot table?
Hi Basit, you can use the filter function to return multiple results.
thank you for being an amazing teacher !
this video was eye-opening for me, learnt some new formulas here. cheeers
This is so awesome. I knew a lot of the formulas but not all. I will definitely watch this and make notes. @kenji you should have a PDF summary for all formulas covered.
Thank you Brian, noted the suggestion :)
@@KenjiExplains Sell the summary for 2-3 dollars. You have many viewers who benefit from your videos. You should benefit as well. If you manage 200,000 sales by the end of the year at 2 dollars each…quick 400? Food for thought. I would gladly pay two dollars for this video’s summary.
Thanks Kenji! Really did not expect Index -- Match could be used so efficiently. Learned another tip for except from you today!
Good tips + great explanation
I appreciate your content. Thanks
Hi Kenji,
I don't know how to thank you for explaining such complicated formulas in a very simple way. Can I get classes from you please?
Hi Kenji, very informative!
I use all of these quite often with Baseball Pool Spreadsheets, Budgets/Tracking, GTSport stuff....
I want to clean data based on calculated values and highlight them. For instance when a cell value is devided by 3 it should return a number without a coma. E. G 15/3 =5 "don't highlight. 14/3= 4.6 '' highlight"
Despite the clickbaity title and that most fas not useful funktion in my uses, this was a good quality video.
And the IFERROR did finally fix one annoying issue I had. Although purely aesthetic.
You know you're an excel pro when all your formulas end with )))))),"").
Thanks Kenji! Very useful content!
Great video! Just curious as to why you gave the IfError formula four stars for difficulty?
Amazing video.
I had an issue with the Vlookup. Although I had the correct items in the list it still showed N/A when I typed. When I copied the cell and pasted it showed the correct amount 🤔
thank and best regards, it's very helpful to me.
Guess I'm a level 4.5 I've never used xlookup nor sumifs just sumif, but the rest I've used . I've needed xlookup I'll try it out going forward. Thanks 😊
How do you decide on a career and job profile to target in college like my batchmates are aiming for IB and stuff but I dont exactly know how to go about it? I am really confused there are so many different things .... I started learning financial modelling and excel not from you though 😅
Fantastic! Thank you.
Thank you for your excellent videos. Supper helpful and clear 🙏🙏
Just love your content! Thank youuu!
You sir! Hats off for crisp n precise tutorial! 😊👍
Thanks for those great tips. Appreciate and subscribed.