Your Excel training videos are best on TH-cam. 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.
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
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.
Thanks for the video. Your explanation is super clear making it easy to follow and understand. I love how short videos like this serve the purpose of explaining the concept with no time-wasters. Cheers.
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.
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?
#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
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 🙏
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
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.
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: th-cam.com/video/pP7AMLJa0Vc/w-d-xo.html
@@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 😀
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: th-cam.com/video/pP7AMLJa0Vc/w-d-xo.html
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.
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!
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
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.
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?
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.
@@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.
#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
Why doesn't Microsoft Excel formulate a simple function which brings you automatically to the "cell" you are looking for? This is a certified $1M question I guess?
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.
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
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
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))
This is the best video explaining how INDEX and MATCH work together. Thank you so much for sharing!
Wow, thank you! Please share it with your co-workers.
@@MyOnlineTrainingHubThank you! You are my assistant and support on a few occasions already 😊🎉🎉🎉
I agree. It was so easy to grasp
This is the best Index/Match tutorial on TH-cam. None of the other videos made sense, and this one was short. Thank you!
Wow, thanks so much! Glad I could help 😊
Bravo!, You helped me a lot in learning Excel formulas. Hats off to you! You are such a nice teacher. Thank you.
Wonderful to hear 🙏
Your Excel training videos are best on TH-cam. 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.
Wow! That's awesome to hear 🙏 🥰
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
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.
Thanks for the video. Your explanation is super clear making it easy to follow and understand. I love how short videos like this serve the purpose of explaining the concept with no time-wasters. Cheers.
Glad it was helpful!
I some cases, INDEX MATCH is better than even XLOOKUP. Thanks Mynda.
Thanks for watching, Segio!
The best explanation ever. Thank you Mynda!
Thanks so much! Please share it with your co-workers 😊
Must be laundry day at Mynda's house. No green shirt!
😁 trying something different! Do you prefer the green shirt?
@@MyOnlineTrainingHub both. I love surprises.
Direct and clear, as always. Thank you Mynda.
Thanks so much, Ivan!
mind-expanding! thank you so much Mynda for your effort with this channel... you make complicated stuff so much easier to work with!! 💚
I'm so glad to hear that! 🙏
Awesome! thanks for sharing. I'm a regular watcher of your videos. You are getting lovelier and lovelier each time.
Thank you so much!
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.
Glad I could help 🙏
The way you explain is incredible!!
Thanks for your kind words!
Awesome videos! How do you know when you need to use absolute reference? What is the difference?
Thank you! This tutorial explains absolute references: www.myonlinetraininghub.com/excel-absolute-references-the-missing-link Hope that helps.
I am very curious as to the performance of index/match vs xlookup
Maybe a topic for another day!
@@MyOnlineTrainingHub that would be an interesting topic because i find index-match too complicated vs xlookup
I had a slow file with xlookups…changed them to index/match and the calculation speed doubled AT minimum…index/match is much faster.
@@Travelershockey5 That's very interesting! Thank you for commenting.
This is just what I was looking for.
So well explained.
Thanks Mynda.
So pleased I could help 😊
Very helpful. I much appreciate this video.
Glad it was helpful!
Hi Mynda,
Beside, There is an another good usage of index to navigate areas which you want to select correct range of data
👍 I cover that in my INDEX video I mention at the end 😉
Thank you . simple and clear explanation
Glad it was helpful!
Tq so much. Awesome presentation!
Glad you liked it!
Thank you very much for the useful tutorial.
My pleasure!
Always very comprehensive❤😊.
Glad you think so!
Thank you so much for sharing!
My pleasure!
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?
#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
It was pretty useful,
Thank you
Great to hear!
Great video
I'm so glad you found it useful!
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 🙏
Absolutely. AI in Excel will be slow to evolve.
@@MyOnlineTrainingHub thank you 🧡
Thank you for sharing 😊 You are awesome 👍
Thank you!
Wow, just Wow - Thanks.
Glad you liked it!
Thank, obrigado, from Brazil
Thanks for watching!
for 2023 which all functions are necessary to know to work in corporate Do you have any paid specifice progrom where I can learn
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
In the last videos she has dressed casually, I like it, it's just an opinion! Thanks for your excellent videos!
Great to hear!
this is amazing!
So pleased you liked it!
THANK YOU
Pleasure 😊
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?
Unprotect the cells the objects are linked to so that when you click on the, the values can be updated.
Thanks for sharing...
My pleasure 😊
Hi Mynda, what about using xlookup ?
I say that at the start 😉
two thumbs up , thank you
Thanks so much!
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.
Thank you very much! 😊
another perfect post
Glad you think so! 🙏
Do I still need to learn index and match if I can use xlookup? It seems easier?
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: th-cam.com/video/pP7AMLJa0Vc/w-d-xo.html
thank you so much
Pleasure!
Thanks Mynda!
You're welcome!
Excellent thank you
Glad you liked it!
Thanks
My pleasure!
Thank you, Mynda. As for auditor such functions are really useful! But what about clothes rebranding?))
What do you think of my rebranding: Keep or go back to Excel t-shirt?
@@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 😀
Wonderful to know!
I never learnt to use index match. Always used vlookup and xlookup. Anyone have good reason to learn this too?
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: th-cam.com/video/pP7AMLJa0Vc/w-d-xo.html
Gracias Mynda!!
My pleasure!
@@MyOnlineTrainingHub and very good for your new image! congrats!
Thank you!
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
Thanks for spotting it. Fixed now 👍
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.
All formulas return something, some return a range, some return an array, some return a single value. HTH.
thats great
Thank you 🙏
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!
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
can i use it to turn 1 column data into 4 columns? name, phone, address, id
Probably best to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
How about XLookup vs Index XMatch XMatch?
I'd use XLOOKUP over INDEX anything if it's available to you.
@@MyOnlineTrainingHub I agree, love XLookup. But I sometimes mention XMatch just to impress people!
but i have one question can you solve it please?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Ok, I have tried Index Match function both at a time but it didn't work.😢😢😢😢
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
the helping, rather more helping
Glad you enjoyed it 🙏
I think Xlookup can do all of that with less complexity 😊
I say that at the very beginning of the video 😉
True... but Xlookup is not available on older versions. But yeah, if you have 365, Xlookup is definitely the way to go.
Why Mynda when we have xlookup
Sounds like you didn’t watch the video 😉
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.
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?
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.
Still love you.
Index / xmatch is still better than xlookup!
meh...I'm not convinced. Maybe for some edge cases, but most of the time I'm XLOOKUPing 😁
@@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.
I don't trace precedents very often, so that was super interesting to hear! Thanks for sharing 🙏
i get the #N/A error for both Match functions and can't get it to work 😞
#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
Wait, so we can VLookup, HLookup AND XLookup by using Index Match only?🤨
Yes, you can! 😜
❤Team Mynda 👍😎✊
Cheers, Steve!
Why doesn't Microsoft Excel formulate a simple function which brings you automatically to the "cell" you are looking for?
This is a certified $1M question I guess?
You mean like the HYPERLINK function?
@MyOnlineTrainingHub oh.....hmmm How's that?
@MyOnlineTrainingHub oh.....hmmm How's that?
@@MyOnlineTrainingHub if it that certain function brings the user directly to the certain cell the user is looking for!
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.
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
👍
xlooksup says hi...
XLOOKUP is the best 🥰which is why I mention it here 0:21
This is hard
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
Thanks very much
My pleasure 😊
XLOOKUP !!!
That’s what she said at the start
Still worse than index / xmatch
@bluetaurean thanks for having my back 😊
@@MyOnlineTrainingHub didn’t even watch. Maybe put a qualifier in the screenshot like “if you don’t have 365…”
WAY TOO COMPLICATED AND UNPRACTICAL FOR ME AND I STILL DONT UNDERSTAND WTH JUST HAPPENED
I recommend you download the practice file and step through the formula yourself.
No more vlookups?? What sorcery is this???
😁
Ok 😂😂
A really ugly way to find something.
If you just want to find something, you can use CTRL+F.
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))
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks Mynda!
Cheers, Chris!