4 Mins. to MASTER Excel INDEX & MATCH formulas!

Рет қаралды 134,824

MyOnlineTrainingHub

Күн бұрын

Пікірлер: 179

@lww8132 Жыл бұрын

This is the best video explaining how INDEX and MATCH work together. Thank you so much for sharing!

@MyOnlineTrainingHub Жыл бұрын

Wow, thank you! Please share it with your co-workers.

@NihonDream Жыл бұрын

The best explanation ever. Thank you Mynda!

@MyOnlineTrainingHub Жыл бұрын

Thanks so much! Please share it with your co-workers 😊

@IvanCortinas_ES Жыл бұрын

Direct and clear, as always. Thank you Mynda.

@MyOnlineTrainingHub Жыл бұрын

Thanks so much, Ivan!

@voiceofsoul7865 Жыл бұрын

Bravo!, You helped me a lot in learning Excel formulas. Hats off to you! You are such a nice teacher. Thank you.

@MyOnlineTrainingHub Жыл бұрын

Wonderful to hear 🙏

@anillpatel Жыл бұрын

This is just what I was looking for. So well explained. Thanks Mynda.

@MyOnlineTrainingHub Жыл бұрын

So pleased I could help 😊

@Brownbeardad 5 ай бұрын

Your Excel training videos are best on KZfaq. Your instructions are easy to follow and understand, and presented professionally. This video provided me with the information needed to determine my application required the match function combined with offset function. I watched other training videos until I came across your channel. I have subscribed and will go to your channel for future training. Thanks.

@MyOnlineTrainingHub 5 ай бұрын

Wow! That's awesome to hear 🙏 🥰

@hansgulbranson9506 9 ай бұрын

This is the best Index/Match tutorial on KZfaq. None of the other videos made sense, and this one was short. Thank you!

@MyOnlineTrainingHub 9 ай бұрын

Wow, thanks so much! Glad I could help 😊

@chaitanyaparwal4896 Жыл бұрын

The way you explain is incredible!!

@MyOnlineTrainingHub Жыл бұрын

Thanks for your kind words!

@Kursatural Жыл бұрын

Thank you so much for sharing!

@MyOnlineTrainingHub Жыл бұрын

My pleasure!

@VP-yp8ip 5 ай бұрын

Very helpful. I much appreciate this video.

@MyOnlineTrainingHub 5 ай бұрын

Glad it was helpful!

@gospelmoto2833 Жыл бұрын

Awesome! thanks for sharing. I'm a regular watcher of your videos. You are getting lovelier and lovelier each time.

@MyOnlineTrainingHub Жыл бұрын

Thank you so much!

@silverfunnel6819 Жыл бұрын

Always very comprehensive❤😊.

@MyOnlineTrainingHub Жыл бұрын

Glad you think so!

@TheShimming Жыл бұрын

Thank you very much for the useful tutorial.

@MyOnlineTrainingHub Жыл бұрын

My pleasure!

@Lightscripture 6 ай бұрын

I love your content! Your youtube content has expanded my excel game (and powerbi) so much thank you!

@MyOnlineTrainingHub 6 ай бұрын

That's awesome to hear!🙏😊

@MrDhunpagla Жыл бұрын

Thank you for sharing 😊 You are awesome 👍

@MyOnlineTrainingHub Жыл бұрын

Thank you!

@drbahsha Жыл бұрын

Tq so much. Awesome presentation!

@MyOnlineTrainingHub Жыл бұрын

Glad you liked it!

@danish2606 Жыл бұрын

It was pretty useful, Thank you

@MyOnlineTrainingHub Жыл бұрын

Great to hear!

@ivicai3281 Жыл бұрын

mind-expanding! thank you so much Mynda for your effort with this channel... you make complicated stuff so much easier to work with!! 💚

@MyOnlineTrainingHub Жыл бұрын

I'm so glad to hear that! 🙏

@dompedro14 Жыл бұрын

Wow, just Wow - Thanks.

@MyOnlineTrainingHub Жыл бұрын

Glad you liked it!

@chrism9037 Жыл бұрын

Thanks Mynda!

@MyOnlineTrainingHub Жыл бұрын

Cheers, Chris!

@danielgoncalveslima9350 Жыл бұрын

Thank, obrigado, from Brazil

@MyOnlineTrainingHub Жыл бұрын

Thanks for watching!

@shamroc34 Жыл бұрын

Excellent thank you

@MyOnlineTrainingHub Жыл бұрын

Glad you liked it!

@thinkleclt Жыл бұрын

Mynda, always love the content ( this is Tom hinkle ) on this one I want to offer another perspective. None of what you say about index-match is wrong, but…. In my experience, many times I help business user create spreadsheets and mini models. I have found that many basic users get more confused with index/ match than vlookup. Also, index match functions can get big and confusing FAST. So I was mostly team vlookup. Now probably the biggest limitation to vlookup is that your key field had to be the leftmost column. For me, the way I structured tables and queries, that was always the case anyway. There were time where it didn’t work though. And 1000%. Team XLookup all the way now. Best of both worlds

@MyOnlineTrainingHub Жыл бұрын

Hi Tom, I can relate to INDEX & MATCH being confusing. I remember learning it myself a loooooong time ago, but it's worth the pain to master it if you want to extend your Excel skills. But I agree with you, for the basic user who doesn't need/want to, V/XLOOKUP is the way to go.

@amrkashoob554 11 ай бұрын

THANK YOU

@MyOnlineTrainingHub 11 ай бұрын

Pleasure 😊

@ayugaleri Жыл бұрын

Thanks for sharing...

@MyOnlineTrainingHub Жыл бұрын

My pleasure 😊

@realkewlmann Жыл бұрын

this is amazing!

@MyOnlineTrainingHub Жыл бұрын

So pleased you liked it!

@souptonuts Жыл бұрын

Must be laundry day at Mynda's house. No green shirt!

@MyOnlineTrainingHub Жыл бұрын

😁 trying something different! Do you prefer the green shirt?

@TGithinji Жыл бұрын

@@MyOnlineTrainingHub both. I love surprises.

@shoaibmohammad9847 Жыл бұрын

thank you so much

@MyOnlineTrainingHub Жыл бұрын

Pleasure!

@melissadiaz7900 Жыл бұрын

Thanks

@MyOnlineTrainingHub Жыл бұрын

My pleasure!

@alfamale985azhang4 Жыл бұрын

another perfect post

@MyOnlineTrainingHub Жыл бұрын

Glad you think so! 🙏

@leorc564 Жыл бұрын

Gracias Mynda!!

@MyOnlineTrainingHub Жыл бұрын

My pleasure!

@leorc564 Жыл бұрын

@@MyOnlineTrainingHub and very good for your new image! congrats!

@MyOnlineTrainingHub Жыл бұрын

Thank you!

@erdal4687 Жыл бұрын

thats great

@MyOnlineTrainingHub Жыл бұрын

Thank you 🙏

@ivanbork4175 Жыл бұрын

First, congratulations on 13 years of very good work, having had the pleasure to follow you some of the way, thank you. And I agree, index/match is efficient, and sometimes still more robust (at least in my opinion) than xlookup, and that is if the columns change places.

@MyOnlineTrainingHub Жыл бұрын

Thank you very much! 😊

@teoxengineer Жыл бұрын

Hi Mynda, Beside, There is an another good usage of index to navigate areas which you want to select correct range of data

@MyOnlineTrainingHub Жыл бұрын

👍 I cover that in my INDEX video I mention at the end 😉

@Bimbolalaoshe Жыл бұрын

You've gained a follower today ❤❤, Explains my question “How do I get the row & column number of a content in between Thousands of data?” Thanks for explaining better than videos I've watched.

@MyOnlineTrainingHub Жыл бұрын

Glad I could help 🙏

@devinlewis5116 Жыл бұрын

Awesome videos! How do you know when you need to use absolute reference? What is the difference?

@MyOnlineTrainingHub Жыл бұрын

Thank you! This tutorial explains absolute references: www.myonlinetraininghub.com/excel-absolute-references-the-missing-link Hope that helps.

@steven.h0629 Жыл бұрын

❤Team Mynda 👍😎✊

@MyOnlineTrainingHub Жыл бұрын

Cheers, Steve!

@Luciano_mp Жыл бұрын

In the last videos she has dressed casually, I like it, it's just an opinion! Thanks for your excellent videos!

@MyOnlineTrainingHub Жыл бұрын

Great to hear!

@alexanderbaranov5418 Жыл бұрын

Thank you, Mynda. As for auditor such functions are really useful! But what about clothes rebranding?))

@MyOnlineTrainingHub Жыл бұрын

What do you think of my rebranding: Keep or go back to Excel t-shirt?

@alexanderbaranov5418 Жыл бұрын

@@MyOnlineTrainingHub Not a t-shirt itself makes us all to gather here watching you videos... I guess, most appreciate the content, ways of explanations and simply a beautiful woman behind all this. Keep on watching regardless of the color scheme 😀

@MyOnlineTrainingHub Жыл бұрын

Wonderful to know!

@SergioAlejandroCampos Жыл бұрын

I some cases, INDEX MATCH is better than even XLOOKUP. Thanks Mynda.

@MyOnlineTrainingHub Жыл бұрын

Thanks for watching, Segio!

@ukelelelou2911 Жыл бұрын

I am very curious as to the performance of index/match vs xlookup

@MyOnlineTrainingHub Жыл бұрын

Maybe a topic for another day!

@natashacelestineanderson5000 Жыл бұрын

@@MyOnlineTrainingHub that would be an interesting topic because i find index-match too complicated vs xlookup

@Travelershockey5 Жыл бұрын

I had a slow file with xlookups…changed them to index/match and the calculation speed doubled AT minimum…index/match is much faster.

@ukelelelou2911 Жыл бұрын

@@Travelershockey5 That's very interesting! Thank you for commenting.

@mohamedsours8175 Жыл бұрын

Thank you so much Mynda for your efforts. your are the best❣ I just wanna ask you now adays we manifests the evolution of AI, in these circumstences is it stell worth it to learn Excel Data Analysis ? thanks a lot 🙏

@MyOnlineTrainingHub Жыл бұрын

Absolutely. AI in Excel will be slow to evolve.

@mohamedsours8175 Жыл бұрын

@@MyOnlineTrainingHub thank you 🧡

@arkai59 Жыл бұрын

Hi Mynda, what about using xlookup ?

@MyOnlineTrainingHub Жыл бұрын

I say that at the start 😉

@Ninjaturtlesalreadytaken Күн бұрын

Its a great explanation. I tried to insert the data on my own and work the formula but it gave me #N/A though I have followed you step by step. Any idea what went wrong?

@MyOnlineTrainingHub 2 сағат бұрын

#N/A means the value you're looking up isn't in the lookup array. You can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@toshiyo822 9 ай бұрын

Do I still need to learn index and match if I can use xlookup? It seems easier?

@MyOnlineTrainingHub 9 ай бұрын

If you're never going to use Excel 2019 or earlier again, then you can probably get away with not learning INDEX & MATCH, however, I would still learn INDEX as there's a lot more to it than what it can do with MATCH: kzfaq.info/get/bejne/prZndLB9r8eUhpc.html

@josephachieve Жыл бұрын

Mynda, how can we ensure that the sort order and sort by buttons continue to work while protecting the sheet to hide the cell formulas?

@MyOnlineTrainingHub Жыл бұрын

Unprotect the cells the objects are linked to so that when you click on the, the values can be updated.

@BMGroup1 Жыл бұрын

the helping, rather more helping

@MyOnlineTrainingHub Жыл бұрын

Glad you enjoyed it 🙏

@sureshskn 9 ай бұрын

for 2023 which all functions are necessary to know to work in corporate Do you have any paid specifice progrom where I can learn

@MyOnlineTrainingHub 9 ай бұрын

Thanks for your interest in my courses. The functions that will be necessary will depend on your job, but a solid grounding in the most common functions will stand you in good stead for most roles. My Advanced Excel formulas course will get you up to speed quickly: www.myonlinetraininghub.com/advanced-excel-formulas-course

@IamTheReaper911 Жыл бұрын

👍

@robertcooper3759 Жыл бұрын

Still love you.

@humblebeginner Жыл бұрын

Does anyone know a solid method for finding partially matching data in multiple spreadsheets without exact matching data or unique IDs? I am struggling to find a way to compare two sheets with similar lists, but no exact matching data... To see what I already have vs don't. TIA!

@MyOnlineTrainingHub Жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@dawn3948 5 күн бұрын

Can someone explain to me the simple reason behind "returning a value?" I never really understood that concept. It sounds like Excel is "borrowing values from the selected cells in order to figure out the formula.

@MyOnlineTrainingHub 4 күн бұрын

All formulas return something, some return a range, some return an array, some return a single value. HTH.

@azleeeffendy305 Жыл бұрын

can i use it to turn 1 column data into 4 columns? name, phone, address, id

@MyOnlineTrainingHub Жыл бұрын

Probably best to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@jayxpea Жыл бұрын

I never learnt to use index match. Always used vlookup and xlookup. Anyone have good reason to learn this too?

@MyOnlineTrainingHub Жыл бұрын

Now that we have XLOOKUP, there's less argument for INDEX. However, not everyone has a version of Excel that has XLOOKUP, so I'd still take the time to master it. Plus, INDEX is a super versatile function. See this video on 5 Secret INDEX features: kzfaq.info/get/bejne/prZndLB9r8eUhpc.html

@danielhartjes2479 5 ай бұрын

How about XLookup vs Index XMatch XMatch?

@MyOnlineTrainingHub 5 ай бұрын

I'd use XLOOKUP over INDEX anything if it's available to you.

@danielhartjes2479 5 ай бұрын

@@MyOnlineTrainingHub I agree, love XLookup. But I sometimes mention XMatch just to impress people!

@TheCatcher111 Жыл бұрын

I think Xlookup can do all of that with less complexity 😊

@MyOnlineTrainingHub Жыл бұрын

I say that at the very beginning of the video 😉

@stephencooper3583 Жыл бұрын

True... but Xlookup is not available on older versions. But yeah, if you have 365, Xlookup is definitely the way to go.

@pc-doctor1416 Жыл бұрын

Small error in the download file. "BatMan" is spelled "Bat Man" (with a space) so initially the formula does not work until you rename cell C18

@MyOnlineTrainingHub Жыл бұрын

Thanks for spotting it. Fixed now 👍

@Seftehandle Жыл бұрын

Why Mynda when we have xlookup

@MyOnlineTrainingHub Жыл бұрын

Sounds like you didn’t watch the video 😉

@shoaibmohammad9847 Жыл бұрын

but i have one question can you solve it please?

@MyOnlineTrainingHub Жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@iaincrawford5472 4 ай бұрын

i get the #N/A error for both Match functions and can't get it to work 😞

@MyOnlineTrainingHub 4 ай бұрын

#N/A means whatever you're looking up is not in the lookup array. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@wilmarkjohnatty4924 8 ай бұрын

Is there a way in excel to do query like formulas that find corresponding values in a Table? For example I am in table1 in one sheet and i want to cross reference a value in another Table2 that contains the value i want: INDEX(Table2[Column to Return],MATCH([@Col_in_Table1],Table2[Column to Match],0)) Returns the desired value from Table 2. But this seems unintuitive and clumsy. There is no direct reference to the Foreign Table and the Match part of the always returns 1 (if it doesnt, it returns the wrong row in the foreign table or gives an error). The Expected Translation should be: Take This value in the current table, Look for it in table 2 and Return the corresponding value in Other Column named [col name]. But the Index/Match formula isnt this. This is why one forgets these clumsy syntaxes.

@wilmarkjohnatty4924 8 ай бұрын

It seems that the newer XLOOKUP does exactly what i want although still not referencing the Table itself and is just referencing and returning 'naked' columns that you can define by standard Table[column] notation. The question now is whether or not using XLOOKUP is more efficient than INDEX/MATCH. VLOOKUP was said to be inefficient, especially if you have tens of thousands of rows with them. Both all of the AI bots (Baird, CGPT and COPilot) say that XLOOKUP is more efficient than INDEX/MATCH because its newer. Whats your take?

@MyOnlineTrainingHub 8 ай бұрын

VLOOKUP and INDEX used to be inefficient when searching through unsorted lists, but the Excel team have since improved both of these functions, so the inefficiencies the AI speak of is outdated (assuming you're using Excel 2021 or 365, which you must be to have XLOOKUP). In terms of which is more efficient, it depends, but over 10s of thousands or rows of data it would be negligible. I would use XLOOKUP because it's easy to write and understand, and searches for an exact match by default, which VLOOKUP doesn't.

@user-nd7vl9pm3d Жыл бұрын

I do have a question, can this be combined with other functions such as the ifs function? I am currently working on a pricing worksheet and I am debating in using a index function and a vlookup to calculate future price percentage increase based on a reference sheet.

@MyOnlineTrainingHub Жыл бұрын

Yes, you can use it with other functions. You're welcome to post your question and sample Excel file on our forum where we can give you some advice: www.myonlinetraininghub.com/excel-forum

@krishnaprasadamudala 10 ай бұрын

Ok, I have tried Index Match function both at a time but it didn't work.😢😢😢😢

@MyOnlineTrainingHub 10 ай бұрын

I'd love to help you with it. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@MJ-cg8vp 10 ай бұрын

xlooksup says hi...

@MyOnlineTrainingHub 10 ай бұрын

XLOOKUP is the best 🥰which is why I mention it here 0:21

@xant8344 Жыл бұрын

Index / xmatch is still better than xlookup!

@MyOnlineTrainingHub Жыл бұрын

meh...I'm not convinced. Maybe for some edge cases, but most of the time I'm XLOOKUPing 😁

@xant8344 Жыл бұрын

@@MyOnlineTrainingHub do you use ctrl+[ to trace precedents? I use it all the time. With index/xmatch, when I'm pulling from data on another tab or workbook, it takes me to that tab or workbook. With xlookup, it just shows me the lookup value, which I always already know since it's usually just a label in the same table as the formula. I review other people's work a lot, and when they use index/xmatch it saves me time. It's especially nice when the precedent workbook is closed, since it opens it for me and takes me directly to the relevant spot. One nice thing about vlookup is the column number argument, which you can use a helper row to make dynamic and then you can pull data from the source table in any column order pretty smoothly. You can drag the vlookup formula across rows and columns, since only the column number argument usually varies by column. With xlookup that's not as convenient, but you can use relative references to go column by column, which is also nice sometimes. The index formula lets you do both of these. Xlookup is easier to set up initially, but I find that it's worth the time to use index/xmatch any time I'm looking something up from a different sheet or workbook, especially when looking up from large tables.

@MyOnlineTrainingHub Жыл бұрын

I don't trace precedents very often, so that was super interesting to hear! Thanks for sharing 🙏

@_Manimal_ 10 ай бұрын

This is hard

@MyOnlineTrainingHub 10 ай бұрын

Hey Lenny, if you're stuck, I'd love to help you. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@adamgoodrich80 Жыл бұрын

XLOOKUP !!!

@bluetaurean Жыл бұрын

That’s what she said at the start

@xant8344 Жыл бұрын

Still worse than index / xmatch

@MyOnlineTrainingHub Жыл бұрын

@bluetaurean thanks for having my back 😊

@adamgoodrich80 Жыл бұрын

@@MyOnlineTrainingHub didn’t even watch. Maybe put a qualifier in the screenshot like “if you don’t have 365…”

@pepper_lab Жыл бұрын

Ok 😂😂

@tun-tunninc.6492 Жыл бұрын

No more vlookups?? What sorcery is this???

@MyOnlineTrainingHub Жыл бұрын

😁

@StopWhining491 2 ай бұрын

A really ugly way to find something.

@MyOnlineTrainingHub 2 ай бұрын

If you just want to find something, you can use CTRL+F.

@RollingEasy 5 ай бұрын

I have 18 football teams in two columns. 9 in each. I need to match which team is playing which team where half will be 'home' (in the left column) and the other half will be 'away' (right column). The end result will be that all 18 teams in alphabetic order are matched with their opponent for this weeks' games. This formula is the best I've managed, to get these matches. I'm wondering if anyone might have anything simpler. =IF(ISERROR(INDEX(C302:C313,MATCH(P299,A302:A313,0),1)),INDEX(A302:A313,MATCH(P299,C302:C313,0),1),INDEX(C302:C313,MATCH(P299,A302:A313,0),1))

@MyOnlineTrainingHub 5 ай бұрын

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum