@@excelisfun I have learnt so much from you and I am really frustrated when I am trying to show to others how to do it .... and they just would not cooperate. Since not all companies are using 365 we end up doing things the old way and your videos are a blessing. Thanks
TH-cam is worthwhile just because people like you are present on this platform! Thank you for all your hard work and trust me it's making a remarkable difference in the lives of others!
I just wanted to take the time to express my sincere gratitude. My firm offers different programs that they pay for and I find your videos the most interesting and helpful. Thank you.
Excellent learning video and teaching technique with all the variation and example , super duper sir. I always watch all your video , great help in learning excel.
Thanks for this compilation of INDEX and MATCH Function of tutorials, I can just review this video if I encounter problems on that INDEX and MATCH Function.
this is really awesome, i was thinking i am excel guru, now i realized still so much to learn. you are doing awesome job. thank you for improving my knowledge.
You are doing an awesome job! I really wish you the very best. Thank you!!!!! oh and I almost forgot. I love when you say "....INDEX and MATCH, oooh sooooo powerful!"
I like how you have organized the sheet: In blue is the problem that we need to solve and immediately after that comes your answer in red. Very cool indeed. 10x ;-)
33:08, This cough in those minutes was to alert and wake up who was sleepy kkkk, in my case I was not scared, but surely some who were sleeping or flying, got a big scare kkkkk. Excellent class!
For work sheet4, you could put row#= 1, and still get result, to avoid confusion and extra thinking. Your videos help a lot. God bless you. I am a beginner and I am having an internship. I will really need your assistance.
Thank you very much! I worked at Samsung I calculated the material cost, and I had the same problem when some models need certain qty of certain material. If I use Vloopup this formula would only search for the first cell on the list. Now I have a factory and I have the same problem and with your help I could fix this!!!
Your accent is so similar to David Schwimmer's its ridiculous! I feel like I am watching friends whilst trying to learn excel! :D I mean thank you, great stuff!
Great Tutorial. In the chart Data. How to keep the list limited for a certain rows like 1 week for charts, even after new data is included automatically for new day.
Thank you for all your videos. Have you already tackled this problem in a video before? I'm trying to see if there is there a way to use Index & Match to get them to return an entire row of a table range similar to using a filter. I want to be able to update daily inventory count information with the use of my keyboard solely without having to constantly pick up my mouse to select the filter icon. Ideally, I would like to put my search criteria cell right below the column where I would update my inventory value, then when I key a new inventory part number in the cell for the search, the entire row would come up right above the cell where I typed in my criteria so that I can type in the new count value. And by entering it, my cursor would automatically jump back down to my criteria cell. Maybe this would not be an Index & Match problem, but I would love to know how to do this so that I can better streamline my work flow. Thank you.
This video shows how to lookup whole row at 21:41 . If you want to display all the values, in all versions of Excel but Office Insiders, you create just the INDEX part (do not add sum like in this video), but you have to highlight the extract number of columns before you start to create the formula, then create INDEX part, use Ctrl + Shift + Enter to enter the formula, then all the values will be shown. If you have Office Insiders, you can use the FILTER function as seen here: th-cam.com/video/5zRab2Grz7Q/w-d-xo.html
Good Afternoon! Its been a while since you had a comment, i have an issue i was hoping you could help with. Your video was great but it didn't quite hit the nail on the head.
Fantastic video. I understand how to add multiple row using index or multiple columns using index. Maybe I missed this but is there a way to add numbers from muliple columns and rows at the same time? For example across the columns is month 1, 2, 3, 4...12 and in the rows are Account # 4000, 4000 ,4005, 4010, 4010, 4010, etc. I would like to add month 1 and 2 for account 4010 only. So figures from multiple columns and rows at the same time. Thanks
I love your lessons with lots of examples! Can someone explain to me for Question 6, why can't I do =Index, Match(Select Rainfall Rate),Match(Pipe Size) instead it has to be =Index,Match(Pipe Size), Match(Select Rainfall Rate)? Thanks
I have a few somewhat "Epic" videos for PivotTables: Excel 2007 (12 examples) video title: Excel Basics #20: Data Analysis Pivot Tables PivotTables Excel 2010 (15 examples) video title: Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) Excel 2007 (20 examples) video title: Highline Excel Class 20: Pivot Tables 20 Examples
Thank you so very much for explaining Index and match for clearly! I am trying to figure out how I can Add the values in a column that are related to a perticular combination of row items? I combined the cell reference of rows using 2 match formula then by AND function, but And function changed the cell references to logical value, so I am not getting the result I am trying to get. Moreover, match function cannot find a range of cell references. Please let me know if there is a solution,Thanks
great video, Thanks! Quick Question. is it possible to use Index and Match formulas across sheets within the same book. Lets say my look up values are in sheet 1 and my array is in sheet 2. is that possible?
Any tips on doing three match features? I am trying to match year, month and account on a spreadsheet. Any help or pointing me in the right direction would be awesome! Thanks!
Hi Mike, Thank you for you efforts ..you are awesome ..I have learnt a lot from you and concept is more clear. But currently I am facing issue to solve a query ..the is I have to sort a list of names on basis of their length ..and if the length of two names are same then both the name should come .. means our index function should not return single name for all the names of same length. Please help me
At the 19 min mark and the -1 example seems to fit what I'm doing, however I want to do this in reverse order. I've got a user defined square footage and from that, I want to find the closest square footage in the array to what is user defined, and have it output the associated rainfall rate and pipe size from that. Do you have an example that covers this? or am i not connecting the dots and its just an easy rework of this example? Thanks for the videos, they are well presented and very informative!
Do you have a video where you are looking up items in multiple sheets? For example, I have a list of names in Sheet 1, I need to see what class that student is in. I have 3 sheets with names and class number. The student could be in Sheet 2, Sheet 3 or Sheet 4. I need to find the class in all sheets for the names in Sheet 1 column A and return the values in column B.
![เปิด อาณาจักร หมื่นล้าน "สาระตั้ม" l [Nickynachat]](http://i.ytimg.com/vi/3XE0nPF4YkQ/mqdefault.jpg)
Here is the link: people.highline.edu/mgirvin/TH-camExcelIsFun/EMT778.xlsx
Much. much appreciated. Learnt a lot from your training videos. You are awesome!
@@mohammedimani200 Glad the video helps : )
spreading out your endless knowledge for free, makes you a blessing for all of us. A very big thank you
For 14 years at TH-cam that is what I have been doing - it is just so much fun ; )
@@excelisfun I have learnt so much from you and I am really frustrated when I am trying to show to others how to do it .... and they just would not cooperate. Since not all companies are using 365 we end up doing things the old way and your videos are a blessing. Thanks
That is just a GREAT way to learn! Practice, practice, practice! After practice: "No big deal!!"
TH-cam is worthwhile just because people like you are present on this platform! Thank you for all your hard work and trust me it's making a remarkable difference in the lives of others!
+wajidnoor Cool! I am glad that the video help!
Very nice video that we have learned excel very easily.
Glad it helps!
There is so much crap on youtube. Thank you for presenting these concepts visually, functionally, and sequentially.
You are welcome!
Yes, INDEX and MATCH can do soooooo many things, it is a must have in any Excel tool kit!
fan of your tricks to make people understand the theme of match and indexes
You are a freak - thank you so much for all of this. The enthusiasm is almost as great as the lessons.
Sir,you are really great. I have learned so much from your videos that now I have become the Store Manager.Thank you so much sir
I just wanted to take the time to express my sincere gratitude. My firm offers different programs that they pay for and I find your videos the most interesting and helpful. Thank you.
You are welcome!
Excellent video. I was always intimidated by Index and Match, but not any more. Leila Gharani referred your videos often.
This has got to be the best excel youtube channel. I wanted to say thank you 🙂
Glad you like it!
Congratulations on becoming Store Manager!
I am glad that the videos help!
You're so awesome...I like your videos so much. The quick keyboard strokes, the speed of your lessons...so great... thanks
I don't usually leave comments, but well explained. This video has helped me a lot more than any other that i have watched
This is by far the tutorial on the subject that I have watched. Thank you.
Great lesson. Thank you for all of your lessons. This is one of the best sources on You Tube for super-using Excel.
I am glad the the videos help! I love this term:
"super-using Excel"
Can I use that?
ExcelIsFun Please us it.
You are such a good teacher. Thanks so much for posting on youtube. I have learned so much from your videos.
Excellent learning video and teaching technique with all the variation and example , super duper sir. I always watch all your video , great help in learning excel.
OMG! I finally really understand INDEX and MATCH and when to use them versus VLOOKUP! Thank you so much, Mike! Beast mode all the way!
Excellent work of INDEX and MATCH Function,
You are great and thanks for doing this ,way better than reading boring books :)
+Shakir Tas You are welcome!
Thanks for this compilation of INDEX and MATCH Function of tutorials, I can just review this video if I encounter problems on that INDEX and MATCH Function.
this is really awesome, i was thinking i am excel guru, now i realized still so much to learn. you are doing awesome job. thank you for improving my knowledge.
You are an Excel guru who is learning more fun topics : ) Glad the videos help, harish!!!
Dear Tutor, thank you. Great concept.
well done. i'll have to re-watch the last examples a few more times before i get it. that was pretty complicated.
Thanks this video saved me at work today ! totally checking out your conditional formatting video. Thanks for showing all the steps.
You are welcome, Nicole!!! Glad it saves time. Thank you for the comment, Thumbs Up and Sub : )
You are welcome! I am glad that you have learned a lot from the videos!
God save you excel fun
I got a lot.......knowladge excel trick from your tutorial
I'm so so happy
I am glad that the videos helped!!
Thanx, saved me much time and finally i found a solution for my work.
You are doing an awesome job! I really wish you the very best. Thank you!!!!!
oh and I almost forgot. I love when you say "....INDEX and MATCH, oooh sooooo powerful!"
+Abubakar Ahmed You are welcome!!
I like how you have organized the sheet: In blue is the problem that we need to solve and immediately after that comes your answer in red. Very cool indeed. 10x ;-)
I LEARNT ..MOST OF FORMULAS N FUNCTIONS..BRO.. SIMPLY SUPURB YOU ARE
Thank you made sending reports much easier.
Better than the $1,500 course provided by the company lol
Free is better than $1500. Thanks for the Thumbs Up and Sub : )
33:08,
This cough in those minutes was to alert and wake up who was sleepy kkkk, in my case I was not scared, but surely some who were sleeping or flying, got a big scare kkkkk. Excellent class!
Mike, you are awesome, so generous in sharing your time and knowledge!!
Love the videos..
That is good to hear! I am glad the videos help!
I am glad that you love them! Love is good!
Thank you so much for sharing such valuable knowledge. Highly appreciated!
You are welcome, berno 10! Thanks for the support with your comment, Thumbs Up and Sub : )
For work sheet4, you could put row#= 1, and still get result, to avoid confusion and extra thinking. Your videos help a lot. God bless you.
I am a beginner and I am having an internship. I will really need your assistance.
I am glad that it is fab and of use!!
Thank you very much! I worked at Samsung I calculated the material cost, and I had the same problem when some models need certain qty of certain material. If I use Vloopup this formula would only search for the first cell on the list.
Now I have a factory and I have the same problem and with your help I could fix this!!!
Very, very good, congratulations!
Thank you very much!
Hey this is an old video but still a very helpful one. Thank you for your kindness to share your knowledge. I learned a lot from this. Thank you.
Cool! I am glad that you like it!!
Thanks to you I've been complimented at work, hahaha. Thanks, once again, Mike.
It makes learning a fun thing with this great tutorial, thanks a lot man!! You are definately a great teacher!
Thank you a lotttt! What a dedicated work! Please keep this spirit!!!!
This is awesome, a lot simpiler and less clunky than the VBA code I've previously used
Really nice guide! finally understand all this function. Thanks alot
sometimes i get lost following you. good job
excellent vedios keep it up.
I am glad that you like it!
Your accent is so similar to David Schwimmer's its ridiculous! I feel like I am watching friends whilst trying to learn excel! :D I mean thank you, great stuff!
wooow, you are right ))
Yes! I realized the same ...
Aksante sana(Thank you)
This is great stuff. Please keep it up. I have learned so much
Great tuts as usual. You think you can post something about SQL?
Thanks again for the lessons... love them allot
+Eduard Bogaerts You are welcome!
nice and great video. thank you for the enthusiastic lesson.
I am glad the you like it!
Thanks for video. It's help me👍
You are welcome!
Great Tutorial.
In the chart Data. How to keep the list limited for a certain rows
like 1 week for charts, even after new data is included automatically for new day.
You are welcome!
You are welcome!!
Thanks alot for all great sharing........... You are doing great job!!!!!!!!!!......... thanks youuuuuu soooooooo mucchhhh.... :)
+Shafqat Shah You are welcome!
thank you for sharing this. It is very helpful and I recommend to my classmates
I am glad that the videos help : )
Glad you like it!
Hats and socks of to you man, thanks for the videos =)
Thank you for all your videos. Have you already tackled this problem in a video before? I'm trying to see if there is there a way to use Index & Match to get them to return an entire row of a table range similar to using a filter. I want to be able to update daily inventory count information with the use of my keyboard solely without having to constantly pick up my mouse to select the filter icon. Ideally, I would like to put my search criteria cell right below the column where I would update my inventory value, then when I key a new inventory part number in the cell for the search, the entire row would come up right above the cell where I typed in my criteria so that I can type in the new count value. And by entering it, my cursor would automatically jump back down to my criteria cell. Maybe this would not be an Index & Match problem, but I would love to know how to do this so that I can better streamline my work flow. Thank you.
This video shows how to lookup whole row at 21:41 . If you want to display all the values, in all versions of Excel but Office Insiders, you create just the INDEX part (do not add sum like in this video), but you have to highlight the extract number of columns before you start to create the formula, then create INDEX part, use Ctrl + Shift + Enter to enter the formula, then all the values will be shown. If you have Office Insiders, you can use the FILTER function as seen here: th-cam.com/video/5zRab2Grz7Q/w-d-xo.html
Cool! You are welcome!
Good Afternoon! Its been a while since you had a comment, i have an issue i was hoping you could help with. Your video was great but it didn't quite hit the nail on the head.
Thanku for the correct link for excel file
You are welcome : )
THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥ THANK YOU! ♥
+Pmb Rom You are welcome! You are welcome! You are welcome! You are welcome! You are welcome! You are welcome!
Fantastic video. I understand how to add multiple row using index or multiple columns using index.
Maybe I missed this but is there a way to add numbers from muliple columns and rows at the same time? For example across the columns is month 1, 2, 3, 4...12 and in the rows are Account # 4000, 4000 ,4005, 4010, 4010, 4010, etc. I would like to add month 1 and 2 for account 4010 only. So figures from multiple columns and rows at the same time.
Thanks
I love your lessons with lots of examples! Can someone explain to me for Question 6, why can't I do =Index, Match(Select Rainfall Rate),Match(Pipe Size) instead it has to be =Index,Match(Pipe Size), Match(Select Rainfall Rate)? Thanks
Becasue INDEX always needs: =INDEX(Array,Row Number, Column Number)
I have a few somewhat "Epic" videos for PivotTables:
Excel 2007 (12 examples) video title:
Excel Basics #20: Data Analysis Pivot Tables PivotTables
Excel 2010 (15 examples) video title:
Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis)
Excel 2007 (20 examples) video title:
Highline Excel Class 20: Pivot Tables 20 Examples
Great! Thank you.
Thank you so lot this video
Thank you so very much for explaining Index and match for clearly! I am trying to figure out how I can Add the values in a column that are related to a perticular combination of row items? I combined the cell reference of rows using 2 match formula then by AND function, but And function changed the cell references to logical value, so I am not getting the result I am trying to get. Moreover, match function cannot find a range of cell references. Please let me know if there is a solution,Thanks
Gr8 video, love the part about dynamic charts
That last expample made my head spin! how long did it take to come up with that formula?
Just years of doing a lot of Excel. I hope the video helped!
Very helpful thanks!
You are welcome!
Nice work!
great video, Thanks! Quick Question. is it possible to use Index and Match formulas across sheets within the same book. Lets say my look up values are in sheet 1 and my array is in sheet 2. is that possible?
Any tips on doing three match features? I am trying to match year, month and account on a spreadsheet. Any help or pointing me in the right direction would be awesome! Thanks!
Hi Mike, Thank you for you efforts ..you are awesome ..I have learnt a lot from you and concept is more clear. But currently I am facing issue to solve a query ..the is I have to sort a list of names on basis of their length ..and if the length of two names are same then both the name should come .. means our index function should not return single name for all the names of same length. Please help me
Thanks for your good teaching. How to down load this EMT778.xls?
Are these tables that you are getting drop-down menus for (e.g. the pipes) pivot tables?
wow, supper helpful! I have another issue that I can't get the formula correct on. Can you please help me?
Thanks so much! I have a question regarding sumif and sum(index(match())). What's the difference?
Thanx
Glad it helps, Ali!!!!
At the 19 min mark and the -1 example seems to fit what I'm doing, however I want to do this in reverse order. I've got a user defined square footage and from that, I want to find the closest square footage in the array to what is user defined, and have it output the associated rainfall rate and pipe size from that. Do you have an example that covers this? or am i not connecting the dots and its just an easy rework of this example? Thanks for the videos, they are well presented and very informative!
I've done example 11&12 using XLOOKUP; in this case CHOOSE must be repeated for the return array, too
Do you have a video where you are looking up items in multiple sheets? For example, I have a list of names in Sheet 1, I need to see what class that student is in. I have 3 sheets with names and class number. The student could be in Sheet 2, Sheet 3 or Sheet 4. I need to find the class in all sheets for the names in Sheet 1 column A and return the values in column B.
Great.
will it be possible in the two way lookup to enter data in the cell found
It a great help thanks a lot
You are welcome!
this is awesome man, thanks for the upload.
cool!