INDEX & MATCH functions Beginner to Advanced 19 Examples (Excel VLOOKUP WEEK Video #3)
HTML-код
- Опубликовано: 22 авг 2024
- Download file: people.highlin...
There are really about 30 example in this long video.
Excel INDEX and MATCH functions How To:
1 MATCH Functions Basics (about 0:46 minute mark)
2 INDEX Basics: Two way lookup (about 9:05 minute mark)
3 One way lookup vertical (lookup Left) (about 13:07 minute mark)
4 One way lookup horizontal (about 14:50 minute mark)
5 One way lookup with lookup column and match column orientated differently (about 17:51 minute mark)
6 INDEX & MATCH to do approximate lookup on table sorted Descending (about 19:15 minute mark)
7 Lookup whole row (about 21:41 minute mark)
8 Lookup whole column (about 24:49 minute mark)
9 Lookup Cell Reference (about 26:00 minute mark)
10 Create Dynamic Range For Chart (about 26:00 minute mark)
11 Lookup from more than one table when tables are on same sheet as formula (about 36:29 minute mark)
12 Lookup from more than one table when tables are on a different sheet than the formula: CHOOSE, INDEX, MATCH functions (about 43:31 minute mark)
13 INDEX and ROWS to Filp a Table (about 47:20 minute mark)
14 Get Amount in Next Bracket (about 50:34 minute mark)
15 Select a Random Value (about 53:18 minute mark)
16 Lookup first non-blank value (about 54:24 minute mark)
17 Handle arrays that require Ctrl + Shift + Enter CSE (about 54:24 minute mark)
18 Basic lookup tool used in complex array formulas for extracting data (about 59:59 minute mark, 1 hour 1 minute 10 seconds)
VLOOKUP Shark Week
Buy excelisfun products:
teespring.com/...
Here is the link: people.highline.edu/mgirvin/RUclipsExcelIsFun/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 RUclips 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
RUclips 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!
That is just a GREAT way to learn! Practice, practice, practice! After practice: "No big deal!!"
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!
You are a freak - thank you so much for all of this. The enthusiasm is almost as great as the lessons.
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!
fan of your tricks to make people understand the theme of match and indexes
Thank you for sharing with us the Bible of index match.
You are welcome, Kadiri!!!
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 welcome! I am glad that you have learned a lot from the videos!
Very nice video that we have learned excel very easily.
Glad it helps!
I don't usually leave comments, but well explained. This video has helped me a lot more than any other that i have watched
God save you excel fun
I got a lot.......knowladge excel trick from your tutorial
I'm so so happy
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!
You are great and thanks for doing this ,way better than reading boring books :)
+Shakir Tas You are welcome!
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
You are such a good teacher. Thanks so much for posting on youtube. I have learned so much from your videos.
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 : )
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.
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.
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!
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!!!
well done. i'll have to re-watch the last examples a few more times before i get it. that was pretty complicated.
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
Thanx, saved me much time and finally i found a solution for my work.
click link below video and go to VLOOKUP Week section, then look for the INDEX and MATCH workbook.
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!!!
I am glad that you like it!
Very, very good, congratulations!
Thank you very much!
That is good to hear! I am glad the videos help!
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!
I am glad that the videos helped!!
Excellent work of INDEX and MATCH Function,
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!!
Dear Tutor, thank you. Great concept.
I am glad that you love them! Love is good!
Lookup from more than one table when tables are on same sheet as formula (about 36:29 minute mark)
Lookup from more than one table when tables are on a different sheet than the formula: CHOOSE, INDEX, MATCH functions (about 43:31 minute mark)
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.
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 : )
I LEARNT ..MOST OF FORMULAS N FUNCTIONS..BRO.. SIMPLY SUPURB YOU ARE
You are welcome!
I am glad that it is fab and of use!!
Thank you made sending reports much easier.
I am not good with SQL and so I will post no videos on that topic.
Better than the $1,500 course provided by the company lol
Free is better than $1500. Thanks for the Thumbs Up and Sub : )
excellent vedios keep it up.
For back and forth dialog to get Excel solutions, THE best Excel question site is:
mrexcel [dot] com/forum
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 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 ;-)
You are welcome!!
This is awesome, a lot simpiler and less clunky than the VBA code I've previously used
Cool! I am glad that you like it!!
I am glad the you like it!
Mike, you are awesome, so generous in sharing your time and knowledge!!
Love the videos..
Thanks to you I've been complimented at work, hahaha. Thanks, once again, Mike.
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.
Great! Thank you.
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 ...
Glad you like it!
Thanks alot for all great sharing........... You are doing great job!!!!!!!!!!......... thanks youuuuuu soooooooo mucchhhh.... :)
+Shafqat Shah You are welcome!
Cool! You are welcome!
Thank you a lotttt! What a dedicated work! Please keep this spirit!!!!
sometimes i get lost following you. good job
Really nice guide! finally understand all this function. Thanks alot
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
Hats and socks of to you man, thanks for the videos =)
Thanks again for the lessons... love them allot
+Eduard Bogaerts You are welcome!
Thanks for video. It's help me👍
You are welcome!
thanks excellsfun
thank you for sharing this. It is very helpful and I recommend to my classmates
I am glad that the videos help : )
nice and great video. thank you for the enthusiastic lesson.
It makes learning a fun thing with this great tutorial, thanks a lot man!! You are definately a great teacher!
This is great stuff. Please keep it up. I have learned so much
I've done example 11&12 using XLOOKUP; in this case CHOOSE must be repeated for the return array, too
Thank you so lot this video
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!
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: ruclips.net/video/5zRab2Grz7Q/видео.html
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)
Aksante sana(Thank you)
Thanx
Glad it helps, Ali!!!!
Thanku for the correct link for excel file
You are welcome : )
Very helpful thanks!
You are welcome!
Nice work!
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.
so nice
I have two separate workbooks which I need to transfer individual test scores in to the other. Workbook 1 has all the students names in order, workbook 2 is random as the students are in random groups. What is the easiest and quickest formula to match names and results to return to workbook 1? Also, there are some students with the same English names. There are 16 sheets on each workbook. Can anyone help as I'm a newbie to working with excel formulas and functions.
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
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
Cheers mate excellent...
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.
It a great help thanks a lot
You are welcome!
It is so wonderful.
wow, supper helpful! I have another issue that I can't get the formula correct on. Can you please help me?
Gr8 video, love the part about dynamic charts
I love this lesson, and I am stuck on 9&10. Cell E12 returns a date, not the number, no matter what I do. What am I doing wrong?
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!
Great but this is what I'm looking for. I want to find the MAX of numbers in a column that has a + or - in front using a . as a decimal.