Excel INDEX Function - 5 Secret Features - do you know them all?

[@kangsungho1752]

You have actually taught the hidden rules of excel. Thanks million!

[@MyOnlineTrainingHub]

Glad you liked the video, Kang!

[@vladmykoliuk6222]

You are the best excel teacher in youtube. Thank you a lot

[@MyOnlineTrainingHub]

Wow, thank you, Vlad!

[@tolgaciik]

Queen of the Excel ☺️

[@MyOnlineTrainingHub]

:-) thanks so much!

[@Dmcbfa]

Yes correctly said

[@jueroe3753]

"MyOnline-Lady",even if Corona creates distance, you fill gaps and connect continents, Thank's again ! As ever, clear, structured and understandable.

[@MyOnlineTrainingHub]

:-) thanks so much, Jue!

[@wayneedmondson1065]

Hi Mynda.. great summary of the magic of INDEX and by association, other range returning functions like OFFSET, INDIRECT, CHOOSE, SWITCH and XLOOKUP. Also, an understanding of range operators completes the toolkit. After learning some of these lessons, I've been able to add a lot of formula trickery to my worksheets that was not possible before. Thanks for the comprehensive lesson and for sharing your tips and tricks. Thumbs up!!

[@MyOnlineTrainingHub]

Thanks, Wayne! Great to hear you're a fan of INDEX's hidden features too :-)

[@ilotvin]

Always a pleasure to learn new ways to use INDEX formula! Thanks.

[@MyOnlineTrainingHub]

Glad to hear it :-)

[@FsoOmar]

Wow! Truly amazing. Didn't know that INDEX could be so magical. 👏 👌 Thank you Mynda for the Excel file.😊

[@MyOnlineTrainingHub]

You're very welcome! So glad you'll find these uses for INDEX helpful 😊

[@jimfitch]

Count me among those who was unaware of INDEX’s other uses. No longer! Thanks, Mynda

[@MyOnlineTrainingHub]

Great to hear, Jim!

[@tibibara]

Must watch video, definitely! The intersect operator was new to me, I really enjoyed learning the 'Operators' part. Both the video and the practice file are very professional in every way🔝. I really-really love it, when I can find everything in one place/file/lesson and don't have to go through tons of material for info, so Mynda, thank you so much, it's a privilege to learn from one of the BEST!☺

[@MyOnlineTrainingHub]

🥰thanks so much for your kind words! Glad you found the tutorial and supporting file helpful.

[@illnatured55]

Thanks Mynda, this video helped me solve a real life problem at work. Incredibly useful!

[@MyOnlineTrainingHub]

Great to hear!

[@joshuamanampiu6489]

I was actually looking for a solid explanation on the INDEX function and this is it. Thank you so much.

[@MyOnlineTrainingHub]

So glad it was helpful, Joshua!

[@IvanCortinas_ES]

Very fine file Mynda. Thank you for your explanation!!!

[@MyOnlineTrainingHub]

Thanks, Ivan!

[@anv.4614]

Thank you very much Mynda. learnt a lot from your lessons.

[@MyOnlineTrainingHub]

I'm so glad to hear that! 🙏😊

[@XMegaJuni]

Love you! Thank you for sharing your experience and knowledge.

[@MyOnlineTrainingHub]

You are most welcome!

[@dashrathpanchal8393]

Mind blowing secret formulas madam...Loved ti ..Great Job ..thanks for sharing such a unique technique

[@MyOnlineTrainingHub]

My pleasure, Dashrath! 😊

[@jacop007]

Really worth to watch and thanks for this video.

[@MyOnlineTrainingHub]

Great to know it was worth your time, Jacop :-)

[@DougHExcel]

Thanks for the different example this could be used!

[@MyOnlineTrainingHub]

My pleasure, Doug :-)

[@shetkar911]

Love the way you explain. Thanks

[@MyOnlineTrainingHub]

Glad it was helpful!

[@abdulhaseeb8027]

Wow! Never seen Index do stuff like that. Thanks for bringing this to us.

[@MyOnlineTrainingHub]

Great to know you found some new ideas in the video :-)

[@darrylmorgan]

Hi Mynda!I Just Learned Something New About INDEX..Really Great Tutorial Thank You :)

[@MyOnlineTrainingHub]

Awesome to hear, Darryl!

[@nonoobott8602]

Wow...I didn't know about the intersect operator (space). This is very helpful. Thank you

[@MyOnlineTrainingHub]

So pleased you discovered some new uses for INDEX!

[@stingehte]

@@MyOnlineTrainingHub Me too. Heard this intersection thing first time. I think we need more explanation on that. Do you have a series starting from basic? Some expert should do it. Each command from elementary to expert level dive. Might run into 500 short 10-12 mins episode.

[@IvanCortinas_ES]

Very interesting the paragraph 5 with Non-contiguous Ranges. I didn't know the work of this function with 2 different areas. Thank you Mynda!

[@MyOnlineTrainingHub]

Glad it was of interest :-)

[@merrillmchenry1450]

Always great work and THANKS for sample model. I'm tactictile, have to be hands on to learn.

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@bravucod]

Brilliant as always!

[@MyOnlineTrainingHub]

Cheers, Daniel!

[@kennethstephani692]

Great video!!

[@MyOnlineTrainingHub]

Glad you enjoyed it

[@sachinrv1]

Even thought Office 365 has amazing functions like Xlookup, Filter, Sort etc., these old school functions Indes, Match, Vlookup, Hlookup, Lookup, Offset, Sumproduct, Sumif(s) the list goes on.. that helped us for so many years are difficult to forget. Every function has it's own usefulness. Thanks Mynda for that colan, space and comma examples, not many know this. Happy Excel :)

[@MyOnlineTrainingHub]

So pleased you enjoyed it! 🙏

[@Saddam-Abdulameer]

Well Done Myanda

[@shakiraasfoor7599]

Me Too

[@MyOnlineTrainingHub]

Thank you!

[@msoffice6037]

Like for a video as always, hope your youtube gets bigger and bigger audience - videos are straight to the point, intermediate and higher level and no waffling :) Thanks for sharing!

[@MyOnlineTrainingHub]

Thanks for your support!

[@davidabuang]

I had no idea the column number could be entered in the row parameter when the array only contains one row. Cool tip! Works great with the new BYROW function, ie: INDEX(r, 3) instead of INDEX(r, 1, 3). Thanks!

[@MyOnlineTrainingHub]

Awesome! Glad it was helpful 😊

[@gyozakeynsianism]

So useful. Thank you!

[@MyOnlineTrainingHub]

Great to hear :-)

[@GeorgeAJululian]

Many thanks it is very helpful and useful

[@MyOnlineTrainingHub]

Glad to hear that, George!

[@Hhunted]

This was a bit over my head, but like your videos.

[@MyOnlineTrainingHub]

Thanks, Jaco! It is an advanced topic, but I encourage you to download the Excel file and work through the examples: www.myonlinetraininghub.com/5-excel-index-function-secrets

[@ivespocock7193]

Great video. Very well explained and illustrated examples of the index function. The excel download is nice to save for the time that I really need to use this function - which I probably will soon now that I know its potential...

[@MyOnlineTrainingHub]

So pleased to hear it'll be useful to you :-)

[@ayetinners]

Thanks, this is help me to combine index function

[@MyOnlineTrainingHub]

Great to hear!

[@mirrrvelll5164]

Amazing video!

[@MyOnlineTrainingHub]

Glad you think so!

[@merbouni]

Excellent performance, clear, useful and very important content, really I learned some useful features today, thank u Mynda, Great job ❤️

[@MyOnlineTrainingHub]

Fantastic to hear, Reda! Thanks for your kind words :-)

[@gyozakeynsianism]

I wish I could make a video this clear and helpful for my students. I will keep trying!

[@johnborg5419]

Thanks Myndy. That was Fun. : )

[@MyOnlineTrainingHub]

Glad you enjoyed it, John!

[@mon_rich5366]

the best channel

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@trumphy911]

I have always tended to use an INDIRECT(ADDRESS (MATCH, MATCH,,,SHEETNAME)) structure, before index came along. It seems pretty similar except of course, that Index does take array functions. Thanks for an interesting set of examples.

[@MyOnlineTrainingHub]

Glad you found it helpful 🙏

[@chrism9037]

Excellent!

[@MyOnlineTrainingHub]

Many thanks, Chris!

[@breakingwest]

Thank you

[@MyOnlineTrainingHub]

You're welcome :-)

[@WilliamOliveiraExcel]

sensacional, muito top

[@MyOnlineTrainingHub]

Glad you enjoyed it :-) Hope there were some new ideas you can use.

[@WilliamOliveiraExcel]

I'm from Brazil and I follow your channel, I thought you wouldn't answer me for being famous and for having an Excel channel

[@Saddam-Abdulameer]

@@WilliamOliveiraExcel she offers her life effort

[@rahulshewale9206]

Thank you so much madam

[@MyOnlineTrainingHub]

My pleasure, Rahul :-)

[@christoslefkimiotis9889]

Nice video

[@MyOnlineTrainingHub]

Thanks so much, Christos!

[@ricos1497]

I almost never use the area part of index. It's good to see it in a useable situation!

[@MyOnlineTrainingHub]

Yeah, me neither, Rico, but it's bound to be handy at some point :-)

[@afhlmd]

Minda, just a piece of advice. When i click the link that comes in your mail i go to a page with the video in which i can´t give you like. In order for me to like your video I have to find the video inside youtube. Thanx for all the tips and shared knowledge!

[@MyOnlineTrainingHub]

Thanks for that, Affonso. I think I used the embed link in the email in error instead of the link to KZbin. Thanks for making the effort to find the video on KZbin.

[@dharmendrasharma5449]

5. Non-contiguous References : =INDEX(INDIRECT("Product"&B19),MATCH(B20,B6:B9,0),MATCH(C19,C5:F5,0)) this also can be used for dynamic range(Product1 or Product2).

[@MyOnlineTrainingHub]

Thanks for sharing! You can also use CHOOSE to reference non-contiguous ranges on different sheets, but I didn't want to open that can of worms :-)

[@nadermounir8228]

So how can we use the area number if the ranges of the index function are on different sheet ?

[@MyOnlineTrainingHub]

The same way. Just select the ranges with your mouse when writing the formula and Excel will prefix the ranges with the sheet name.

[@nadermounir8228]

@MyOnlineTrainingHub as u mentioned in the video, the index function gives an error when using different ranges from different sheets when using the area number

[@rodneyplunkett6688]

This is excellent. How do these techniques work on LARGE dB's, I've a file with some 9 Million cells.

[@MyOnlineTrainingHub]

Cheers, Rodney. Comparing to other lookup functions, INDEX is far more efficient. Of course it depends on what you want to do exactly.

[@KhushnoodViccaji]

Hi Mynda... wouldn't it be more efficient / appropriate to user PowerQuery in such situations (LARGE dBs / HUGE number of cells)?

[@kurtneubek3200]

Good information presented clearly; thank you. But will you also please consider concluding with something like: "I hope you learned some things about INDEX. But you should also know that for all of the examples I just gave, Excel has a MUCH simpler feature built in to accomplish this. Simply highlight the table and choose Formulas / Create (Name) from Selection. That will instantly name the rows North, South, East and West, and the columns Q1_, Q2_, Q3_, Q4_. Now if you want the value from East Q4, simply use the formula "=East Q4_" (without quotes). Want the total of East? "=SUM(East)". You can still set up the selection table that I used, with "=INDIRECT(B34) INDIRECT(C33)""

[@MyOnlineTrainingHub]

Thanks, Kurt and thanks for your suggestion. The purpose of this video was to demonstrate some lesser known uses for INDEX as opposed to the simplest way to lookup a single value. I agree, the space operator is useful, but it isn't very well known, so I'd be more inclined to use a lookup function to return a single value as it's more easily interpreted by other users, assuming the workbook isn't solely for your use.

[@nkowk]

👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍

[@MyOnlineTrainingHub]

Thank you!

[@inxanadu8576]

I recently started using index and it is amazing but I’m curious, I had to completely eliminate all of my formulas from my workbook because of “calculating threads” messages that were mind numbing. Is that common using index?

[@MyOnlineTrainingHub]

No! I've never had that issue. Perhaps you'd like to share your file and formulas that are causing the problem on our forum and we can see if there is a more efficient way to write them: www.myonlinetraininghub.com/excel-forum

[@asadali2304]

HELLO GOOD MORNING MAM CAN, U EXPLAIN ME HOW I CAN GET MOBILE DATA IN EXCELL WHILE SCANING WITH BARCODE SCANER,

[@MyOnlineTrainingHub]

Hi Asad, Excel can't natively read bar codes. You'd need software to read the bar code and then import the data it reads from there to Excel.

[@rory1653]

wow

[@MyOnlineTrainingHub]

Cheers, Rory :-)

[@khersheonteoh5697]

i turn on the ceiling light and look at the floor and tell that shadow "YOU STILL KNOW SO LITTLE!!"

[@MyOnlineTrainingHub]

:-D Glad you discovered something new!

[@ShuvamNayak]

I still don’t understand the use case of using space operator in index. One index can do the same and it will still be dynamic

[@dp70737]

I did not know the intersect operator was a thing

[@MyOnlineTrainingHub]

Glad it was useful 😊

[@AR-rs2dy]

A little too quick. Maybe do 5 minute videos on each function.

[@MyOnlineTrainingHub]

Thanks for the feedback. This is an advanced topic. I recommend you download the Excel file here: www.myonlinetraininghub.com/5-excel-index-function-secrets then watch the video at a slower speed while working through the examples. There are also step by step written instructions at the link above. Hope that helps.

[@tommyharris5817]

I appreciate that you want to churn out more videos but they can become useless as there are quicker and simpler methods. Just because the Index function can do 100 things doesn't mean you should use them or know them. Please keep things in perspective as there are many, like me, that want the best buck for the money.

[@MyOnlineTrainingHub]

I don't agree. The INDEX function is the most powerful function in Excel's library. I make use of all of these examples and don't think you'll find a simpler/quicker or more efficient function, particularly since INDEX is not volatile, which gives it an advantage over OFFSET for returning dynamic ranges. I encourage you to keep these techniques in mind...one day they may come to your rescue.

[@alistairstanger2940]

What money exactly? These videos are free.