I am glad that the videos I post have been so helpful for so long!!!! Thank you for your support with comments and Thumbs Up on each video that you watch : ) and Sub (it does not look like you are subscribed).
Thank you for your support : ) (When I looked at your channel, it did not show excelisfun sub; I think the new RUclips Format is causing this mistake...)
Not sure about that but I really am subscribed. Even get notifs when new videos are available. I hope your excel genius gets you tothe Philippines so I can meet you i person. :)
I am glad that you get the updates when I post and I am glad that the videos have helped for so many years! That is why I post: so we can all be more efficient and have more fun with Excel : ) : )
I'm yet to find a better excel teacher than you. You are just TOOO MUUUCH Do you also make videos for Access, I will love to have a link to your channel if you have one for access
Thanks for the timely reply, I will remain glued to your excel videos. You can't believe your RUclips channel exclusively turned me into a renowned excel trainer and consultant. God Bless You
EXCELlent as always indeed. I am a big fan of your work Mike, love the way you explain things and for sharing the files with us. Keep up the good work.
Great use of keyboard shortcuts. An alternative to F9 that may also be useful in these demos is F5+Enter. Highlight INDIRECT(D7) in the formula, press F5+Enter to show the range referred to then Ctrl+z to go back. (Other functions that resolve to ranges and work with F5 are OFFSET INDEX, IF, CHOOSE, IFS and SWITCH )
Elegant indeed! You had a bit over 5000 rows. If we didn't have other formulas in the sheet, how many rows might it take for using Indirect to become unwieldy and an alternate option the better choice?
It TOTALLY depends on what other formulas and data you have. In my book and Video Series Ctrl + Shift + Enter, Mastering Excel Array Formulas, I talked extensively about the many topics involved and do lots of timing; but in the end, most of the time, the larger the ranges and the more cells that the engine has to traverse, the longer it takes to calculate. You will know if you hit the limit, especially with large array formulas, because the whole workbook will take seconds to calculate each time you hit enter.
I also use Match function to get dynamic column index specially when lookup tables are having same structure as the main report or dashboard ! Thanks a lot for your videos, please do more videos on Power Query and parameters in power query
nice. I assume you know this already but the HOME and END keys also work when you are in cell edit mode. Home jumps you to the beginning of the formula bar and End jumps you to the end. And also, Ctrl+left or right arrow will jump you around one word at a time.
Yes, and it is funny, I use those all the time in file names and web addresses and Word documents, but I almost never use them in Excel... I'll have to work on that : ) Thanks for the tip.
Mike, you use the approximate match in the VLOOKUP function, but if there is an error in the size it will return a value anyhow. Wouldn't it be better to use an exact match? So if the pipe size doesn't exist in the specific table you will get an error!
Fabio Gambaro I agree. In a situation where you're looking up item codes I would use an exact match method and then wrap the vlook-up and last column calculations inside an iferror to provide substituted values when errors are made. Or, use validation rules to control size selection.
Nice practical video. I think this is usefull for example different price list for different categories. You could even extend the formula if the collumnrs in the different lists are different. One critical remark: why leave the optional parameter blank? How do you know what is the default parameter? It is visible in the function assistent, but in the editor? Or is thsi always the first? I always prefer to enter these optional parameters, so it is clear what you choose (and not a coincidence)....;)
I leave it blank because I have used this function about 10,000 times in my life. All Help for functions indicate what the default is. all the way back to the early 1990s when I started until today, the help for functions tells you what the default is. The intent of Microsoft is to allow fewer arguments entered for great formula creation speed. There is also a visual que with the square brackets that indicate that if you know what the default is you can leave that argument out. I have been teaching this technique for decades because in the end small details like this executed over a life of Excel adds up to saved time : )
Well Mike, your are nr 1 in Excel, but I do have another opinion, this is why: you and I both agree that it is better to enter formulas directly than by the assistent/wizzard. Not only for the speed, but sometimes you even get better info, (just compare WEEKDAY with the assistant). So students who learn Excel should type the formulas. In doing so you CAN see that a parameter is optional (you are right again) by the brackets [ ], but you CAN'T see what the default value is, you have to deep in to the formula assistant/wizzard (or you need experience like you ;). There is another reason: if you use VLOOKUP (for looking up an article nr) and forget or do not use the optional parameter, this is dangerous. Suppose the article list is sortet at the beginning, you enter the formula and test it with different numbers, this works so everyone is happy. But later for some reason someone sorts the list by price or so: BOOM (your language I am listening...;), gone is the correct formula. So I want my students always to consider the optional parameter and make a decision/choice that is visible. Time savings do not weight against possible failures....
Yes, but by leaving it out, you are 100% "consider the optional parameter and make a decision/choice that is visible." By leaving it out you are saying: I am doing Approximate Match!!!! There is a visual que: you left the whole argument out. Anyway, Microsoft lets us have different ways of doing things, and it is fine to always put default argumnets explicitly in the formula. But from the very beginning of Excel, many functions have had defaults and the help menu and written manuals and textbooks have made it clear what the defaults have been, and so I have always taught it this way, and it makes our formula creation time just a bit shorter : )
It is much more complicated. I have never done it with multiple tables, but I have done a video about how to simulate Approximate Match lookup. But again, Power Pivot does NO have a direct way of doing Approximate Match lookup like in Excel. Here is a video: ruclips.net/video/oNasRFXagT0/видео.html
You are welcome! Thanks for your continued support : ) The Blue Cover is the newer version with a few updates and an extra chapter at the end. If you only get one, get the blue one. If you are a collector or ant to support the excelisfun channel here at RUclips, then you can get both; that is... if you can even find a yellow collector version...
Hi mike, can you please explain how i can combine this 2 formulas in excel Formula 1=iferror(if((D4>4);D4-4;0)*vlookup(F4;sheet2!J3:N37;3)+vlookup(F4;sheet2!J3:N37;2);"") Formula 2=ifna(if(match(A4;sheet2!E4:E16;0);vlookup(F4;sheet2!J3:N37;2;FALS);vlookup(F4;sheet2!J3:N37;4;FALS));vlookup(F4;sheet2!J3:N37;4;FALS) This 2 formulas bring both a price And I want them to bring price for a specific costumers, FORMULA 2 when the costumer id in column A match, lookup for table B and in and if it is not a match lookup table A, and in the same cell when lookup table A or B use FORMULA 1 to give me the proper price for the amount of goods Please help with some suggestion
Basically, I'm splitting budget by year . Within that year it is split up by account. There are only two account and for every table it is only returning the travel account and not the business account
On my 11th year as a Reports Analyst. Without your tutorials I don't know if I would ever survive the 1st month.
I am glad that the videos I post have been so helpful for so long!!!! Thank you for your support with comments and Thumbs Up on each video that you watch : ) and Sub (it does not look like you are subscribed).
I am subscribed since the day I first watch your video.
Thank you for your support : ) (When I looked at your channel, it did not show excelisfun sub; I think the new RUclips Format is causing this mistake...)
Not sure about that but I really am subscribed. Even get notifs when new videos are available. I hope your excel genius gets you tothe Philippines so I can meet you i person. :)
I am glad that you get the updates when I post and I am glad that the videos have helped for so many years! That is why I post: so we can all be more efficient and have more fun with Excel : ) : )
Thank you for the great explanation of INDIRECT & combination with VLOOKUP - and all the other useful little tips and tricks in there :)
You are welcome, Teammate!
You are genius man ❤️
Just having fun with Excel ; )
AWESOME! Each of your video has made me more of a fan of Excel. Your teaching style is soooo effective. Thank you!!
You are welcome so much! Thanks for your support with comment, Thumbs Up and Sub : )
Very very nice! I like the F3 trick to past the names. And I'd never thought about using a named range inside a VLOOKUP. Very clever stuff.
Yes Names are great efficiency in VLOOKUP, maybe table names are even better : )
I'm yet to find a better excel teacher than you. You are just TOOO MUUUCH
Do you also make videos for Access, I will love to have a link to your channel if you have one for access
I am not good with Access, so although I have made a few videos, I do not make videos about Access. Sorry.
Thanks for the timely reply, I will remain glued to your excel videos. You can't believe your RUclips channel exclusively turned me into a renowned excel trainer and consultant. God Bless You
Really elegant formula. It’s was also useful to know its performance depending on dataset size, and I liked your custom number formatting and F3
Glad it all helped! Thanks for your support : )
EXCELlent as always indeed. I am a big fan of your work Mike, love the way you explain things and for sharing the files with us. Keep up the good work.
Okay, I will keep it up! Thanks as always for your support with comment, thumbs up and Sub : )
ExcelIsFun You deserve these thumbs up, likes and more, Mike.
Great use of keyboard shortcuts. An alternative to F9 that may also be useful in these demos is F5+Enter. Highlight INDIRECT(D7) in the formula, press F5+Enter to show the range referred to then Ctrl+z to go back. (Other functions that resolve to ranges and work with F5 are OFFSET INDEX, IF, CHOOSE, IFS and SWITCH )
EXCELlent, FUNtastic and very useful. There is no "MATCH" of Mike the Great even "Lookup" can't find !!!! :))
Thanks : ) : )
Excellent lookup video! INDIRECT is a great way to do this.
: )
Elegant indeed! You had a bit over 5000 rows. If we didn't have other formulas in the sheet, how many rows might it take for using Indirect to become unwieldy and an alternate option the better choice?
It TOTALLY depends on what other formulas and data you have. In my book and Video Series Ctrl + Shift + Enter, Mastering Excel Array Formulas, I talked extensively about the many topics involved and do lots of timing; but in the end, most of the time, the larger the ranges and the more cells that the engine has to traverse, the longer it takes to calculate. You will know if you hit the limit, especially with large array formulas, because the whole workbook will take seconds to calculate each time you hit enter.
Wow just amazing we always lern so many things from you sir...thanks for your work for us
You are welcome! Thanks for your support!
Thanks, I use this trick all the time in my various reports !
Yes, it is such a cool trick : )
I also use Match function to get dynamic column index specially when lookup tables are having same structure as the main report or dashboard ! Thanks a lot for your videos, please do more videos on Power Query and parameters in power query
What would i do without you. Thank you so much
You are welcome so much, Emirhan!!! i am happy to help : ) Thanks for your support on each video that you learn from with a thumbs up and comment.
@@excelisfun I"ll return the favour :)
nice. I assume you know this already but the HOME and END keys also work when you are in cell edit mode. Home jumps you to the beginning of the formula bar and End jumps you to the end. And also, Ctrl+left or right arrow will jump you around one word at a time.
Yes, and it is funny, I use those all the time in file names and web addresses and Word documents, but I almost never use them in Excel... I'll have to work on that : ) Thanks for the tip.
Mike, you use the approximate match in the VLOOKUP function, but if there is an error in the size it will return a value anyhow. Wouldn't it be better to use an exact match? So if the pipe size doesn't exist in the specific table you will get an error!
Fabio Gambaro
I agree. In a situation where you're looking up item codes I would use an exact match method and then wrap the vlook-up and last column calculations inside an iferror to provide substituted values when errors are made. Or, use validation rules to control size selection.
Thanks so much for this tip. so simple and straight forward. love always!
You are welcome so much! Thanks for your support with comment, Thumbs Up and Sub : )
Excellent as always...
Thanks for the EXCELlent comment : )
awesome video! helped out at work significantly
Glad it helped significantly! Thanks for your continued support : )
Nice practical video. I think this is usefull for example different price list for different categories. You could even extend the formula if the collumnrs in the different lists are different.
One critical remark: why leave the optional parameter blank? How do you know what is the default parameter? It is visible in the function assistent, but in the editor? Or is thsi always the first? I always prefer to enter these optional parameters, so it is clear what you choose (and not a coincidence)....;)
I leave it blank because I have used this function about 10,000 times in my life. All Help for functions indicate what the default is. all the way back to the early 1990s when I started until today, the help for functions tells you what the default is. The intent of Microsoft is to allow fewer arguments entered for great formula creation speed. There is also a visual que with the square brackets that indicate that if you know what the default is you can leave that argument out. I have been teaching this technique for decades because in the end small details like this executed over a life of Excel adds up to saved time : )
Well Mike, your are nr 1 in Excel, but I do have another opinion, this is why: you and I both agree that it is better to enter formulas directly than by the assistent/wizzard. Not only for the speed, but sometimes you even get better info, (just compare WEEKDAY with the assistant). So students who learn Excel should type the formulas. In doing so you CAN see that a parameter is optional (you are right again) by the brackets [ ], but you CAN'T see what the default value is, you have to deep in to the formula assistant/wizzard (or you need experience like you ;).
There is another reason: if you use VLOOKUP (for looking up an article nr) and forget or do not use the optional parameter, this is dangerous. Suppose the article list is sortet at the beginning, you enter the formula and test it with different numbers, this works so everyone is happy. But later for some reason someone sorts the list by price or so: BOOM (your language I am listening...;), gone is the correct formula. So I want my students always to consider the optional parameter and make a decision/choice that is visible. Time savings do not weight against possible failures....
Yes, but by leaving it out, you are 100% "consider the optional parameter and make a decision/choice that is visible." By leaving it out you are saying: I am doing Approximate Match!!!! There is a visual que: you left the whole argument out. Anyway, Microsoft lets us have different ways of doing things, and it is fine to always put default argumnets explicitly in the formula. But from the very beginning of Excel, many functions have had defaults and the help menu and written manuals and textbooks have made it clear what the defaults have been, and so I have always taught it this way, and it makes our formula creation time just a bit shorter : )
Nice one Mike !! Thanks :)
You are welcome : )
Quick refresh go excelisfun
Thanks! Let's go together to have more fun with Excel!
Thanks you u sir i always time this trick
Nice explanation
Thank you
I have to stop binge-watching your videos, Mike. I see a Dutch dropdown at 4:51.
Excelent video thanks
You are welcome for the EXCELlent vid!
Awesome!
How can we do the same thing using Power Pivot and Data model?
It is much more complicated. I have never done it with multiple tables, but I have done a video about how to simulate Approximate Match lookup. But again, Power Pivot does NO have a direct way of doing Approximate Match lookup like in Excel. Here is a video:
ruclips.net/video/oNasRFXagT0/видео.html
Mike, thanks for great vids!...BTW what's the difference between you book - CSE with blue cover vs. Green/Yellow cover?...hey! I thumbs UP! :)
You are welcome! Thanks for your continued support : ) The Blue Cover is the newer version with a few updates and an extra chapter at the end. If you only get one, get the blue one. If you are a collector or ant to support the excelisfun channel here at RUclips, then you can get both; that is... if you can even find a yellow collector version...
Sir, It was useful as always. Thanks.
You are welcome!!!
Thank you for the Thumbs Up!!!!
Thanks for the Sub : )
Thanks Mike! Awesome as always! BTW, Don't you think about making a video about custom formats creating? It's really incomprehensive thing.. :(
I have a few videos on this topic. Here is one:
ruclips.net/video/RpHEgFSI3GA/видео.html
Highline Excel 2013 Class Video 30: Custom Number Formatting & Text Functions, (33 Examples)
ruclips.net/video/RpHEgFSI3GA/видео.html
Thanks for your support in clicking that Thumbs Up and commenting and Sub too : )
Thanks, Mike! I dreamed about this video and here it is! :)
There it is!!! Thanks for the continual support : )
Thanx for awesome video, but I am not able to find ur excel file, please share the link of your excel file.
The link is below the video : ) You can try that link.
ExcelIsFun 🙏 Thanks
Love the video Mike. I would like to take your statistics class. How would I go about that?
It is all free here at RUclips.
Hi Mike, I cannot download the file from the link you provided. Is there any other way that can download and pratice? Thx
The link is working. You can try again : )
Hi mike, can you please explain how i can combine this 2 formulas in excel
Formula 1=iferror(if((D4>4);D4-4;0)*vlookup(F4;sheet2!J3:N37;3)+vlookup(F4;sheet2!J3:N37;2);"")
Formula 2=ifna(if(match(A4;sheet2!E4:E16;0);vlookup(F4;sheet2!J3:N37;2;FALS);vlookup(F4;sheet2!J3:N37;4;FALS));vlookup(F4;sheet2!J3:N37;4;FALS)
This 2 formulas bring both a price
And I want them to bring price for a specific costumers, FORMULA 2 when the costumer id in column A match, lookup for table B and in and if it is not a match lookup table A, and in the same cell when lookup table A or B use FORMULA 1 to give me the proper price for the amount of goods
Please help with some suggestion
I do not know. Try posting question to this Excel Question site to achieve back and forth dialog to get solutions : mrexcel.com/forum
sir can you plz tell me excel 2016 version has change case short key?
I do not understand what you are trying to communicate.
+ExcelIsFun In excel can we change case upper,lower,proper with short key
I do not know : (
I followed this tutorial step by step. But the problem is that when it pulls values from my table it only reads off the first row.
Basically, I'm splitting budget by year . Within that year it is split up by account. There are only two account and for every table it is only returning the travel account and not the business account
8:39
First :)
#1!!!! Thanks : )