Impressive how Leila saved my life so many times!!! hehehe Everytime when I have a problem I will check if my Guardian angel can help me.... most of the times she can! 😇🥰 Thank you!!
The concept of index and match is now clear ...what a simple and effective way of presenting, not only for this function but for every function she narrates !!! Excellent !!
Thank you Leila! It really help me getting a quick data from a Table (7728 Cells - 112 Rows - 69 Columns) in a single table using the List and both options you taught in this video. Now i can get the data from any row/column combination i need with a few clicks.
I watched the prior INDEX/MATCH video, and moved on to this one. This really fits my needs as an engineer with tons of data to evaluate or look up from a 2D data array with thousands of rows/columns. (raw data in CSV format, imported into Excel for the ease of eyes) THANK YOU!
This is genius! Thank you, I’ve been looking for this answer for more than 6 hours trying to figure out how to combine data from 2 rows with the info from the same account into 1 line row. Eg: John | Revenue from Fish: | $200 John | Cost of Fish: | $50 John | Revenue from Fish: $200 | Cost of Fish: $50 | Profit: $150 Because of your video I finally understand index and match.
Thanks for the video. It also works without an auxiliary column: = INDEX (C22: F31; MATCH (H22; B22: B31; 0); MATCH (I20; C20: F20; 0) + (MATCH (I21; C21: F21; 0)> 1))
Thank you @LeilaGharani for all that you do! Solution #3 was perfect for my challenge. I appreciate your methodical step by step explanation in all of your videos, you are great!
I want to cry my heart out! 💃💃By using a unique identifier instead of combining rows and columns. Aren't you brilliant! You have saved me from hours of brain deadlock. Your analogy is so easy to understand. I can now move on to finalise the financial report. I can't thank you enough but thank you, thank you! I will surely enroll in yr course soon.
I watched this couple of months ago and today i have used in my project work to get information from Database, which is complex [=INDEX('Exported DB-TPR'!$A$2:$A$27301,MATCH($I3&$J3&$K3,'Exported DB-TPR'!$B$2:$B$27301&'Exported DB-TPR'!$C$2:$C$27301&'Exported DB-TPR'!$D$2:$D$27301,0)) ] than the example used in the video. It was really helpful. Thank you.
I should have used without CSE [=INDEX('Exported DB-TPR'!$A$2:$A$27301,MATCH($I3&$J3&$K3,index('Exported DB-TPR'!$B$2:$B$27301&'Exported DB-TPR'!$C$2:$C$27301&'Exported DB-TPR'!$D$2:$D$27301,0),0))] for the better .
Thank you for making this quick and easy. This video was so good, I wish I found this years ago! YOU are good at explaining the logic and steps. You've got a new excel formula nerd fan!
Am late to this tutorial . . . but unlike other people's videos, your relaxed way of explaining, actually clicked for me (the light bulb came to life) and helped me understand how to play around with the data better and its presentation. So, now you have a late subscriber to your channel.
Great teaching style! Wonderful format with great examples You break formula's down and explain them step by step. Well done thanks. Great learning video, series thanks
Hi Leila, I stumbled upon your videos by random and love that your videos are high quality and very elegantly created compared to other Excel videos. I hope you continue to make great videos on Excel with emphasis on functions that'll be helpful for a marketing or finance person :)
Hi Leila, I really like your tutorial, I've learned a lot from you. Just want to share this concerning the matching formula, I didn't use the exact formula you use but I got the result and it's working perfectly with the dropdown list =INDEX(A20:F31,MATCH(H22,B20:B31,0),MATCH(I21,A21:F21,0)) : Your furmula =INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C19:F19,0)) : Mine. Thanks
Thank you for these Excel instructional videos. You explain the formulas in a way that makes them easy to learn and understand. Best Excel training videos on the web.
Two minutes in and I had the answer to the question that had been bugging me for hours! I had transposed the Index and Match ranges so it was returning and N/A. So clear. Thanks. 👍🏻😎
Nice way of explaining... you are highlighting common mistakes as if you are actually making them, helps a lot in remembering such errors and how not to make such mistakes. Keep it up!!
Awesome, I have been somewhat confused or at least vague on how this works, I could do simple functions but you really walked me through the logic and how to build the formulas from the ground up.
You miss, have saved me from my 5 hour agony from figuring this out. Turns out I was doing columns THEN rows instead of rows THEN columns. Thank you very much!
Leila, you saved me for the second time. Thank you!!!! ❤️ I am forwarding your videos to all my friends and colleagues. The world should now how great you are!
is there any word like ULTRA AWESOME ? no worries I just wanna tell you that it was so so outstanding ...priceless ...you are being blessed everyday by all who have'd benefited form this video daily.
You are Great. I was looking forward to changing 6 nested =IF(AND( = bla bla in one formula and I found this great formula you just showed and gave me the same result with less functions.
Immense respect! As a teacher, you foresee what kind of problems a pupil could encounter and give a solution for that! Simply amazing! I have a question though, how could we apply index & match to an array (taking data from an array and putting answers on an array) using CSE! I could work it around by using additional index within match but otherwise with CSE it ceases to be dynamic on the match argument!
Hello Ms Gharani, I discovered your videos only shortly and they are fantastic! Really like your precise, logical, clean approach with good examples and with great awareness where the mouse has to stop a little while for the viewer's eye to be able to follow! You are really good! Thank you for these videos! May I add another non-array solution _without_ helper cells that is based on the the rarely used reference form of INDEX (with 4 parameters): =INDEX( (C22:D31;E22:F31); MATCH(H22;B22:B31;0); MATCH(I21;C21:D21;0); MATCH(I20;D20:E20;0)) The trick here is that in the 1st parameter both arrays for the "Actual" values (C22:D31) and for the "Budget" values (E22:F31) are given as a list: (C22:D31;E22:F31) The 4th parameter then chooses which of these actually gets searched, so 1 (match result for 'Actual') gives array C22:D31 and 2 (match result for 'Budget') gives E22:F31. The 3rd parameter now selects column 1 (for "Revenue)" or 2 (for "Profit") of this chosen array, and 2nd parameter selects the row we're seeking. This version of INDEX rarely fits the problem, but here it does nicely. :)
Another fab video. Looks interested but can be complicated for many people. I have been working on complex INDEX MATCH situations and have mastered it through a lot of real life cases. during my training sessions. Your videos are great to learn from, especially your techniques. Thanks.
Thank you Leila, you are an amazing teacher. You really have a knack for explaining and making it easy to understand, even if it is not that easy. Keep up the good work. IMO, you should have much more views. I am recommending you to my my frends. Cheers.
One advise...it would be much more easier to follow if you left us a link to download the workbook, so we can practise and do the functions as you speak. ;) Hope you will think about it.
Hi Leila, For my colleague in the office; who are more comfortable with the VLOOKUP and HLOOKUP; the following integration also worked well. Here, I inserted a helper row giving the column no. which will help the HLOOKUP function. The HLOOKUP function further compliments the column criteria of INDEX and gives the result. =INDEX($C$7:$F$16,MATCH(I11,$B$7:$B$16,0),HLOOKUP(J9&J10,C3:F4,2,0))
If you're using INDEX MATCH you're definitely not amateur :) The good thing with Excel is there is always something new to learn. Glad you find the videos useful.
@@LeilaGharani Hi Leila, I have a problem and hope you would help. My table looks like this A1 France A2 405 in seperate sheet A1 France B2 400 C1£3 A2 France B2 500 C2 £5 I want to look France between =< 400 less than 500 return matching value from column C, hope you can help
You're very welcome. I'm glad you like the explanation! It's difficult to get the hang of it. It took me a while....but once it clicks, it's just so much more flexible than Vlookup.....
great explanation, very easy to understand with the way you explain the formula, even for beginners (first time learn index formula) like me Very Good and useful video
Grab the file I used in the video from here 👉 pages.xelplus.com/index-match-advanced-file
I am watching on my phone.
I have trouble seeing the equations.
Awesome INDEX video!!! It a great solution that so many business people encounter!
Thanks Mike. Very true! I get this question all the time....
Leila & Mike, discovering both your channels has been an absolute revelation for me!
You Do Some Awesome explanations too Man and I love how you speak :D
2 of my favorite excel gurus 😃
Two genius at one place... 😘😘
The Excel Goddess! No matter what crazy scenario in which I find myself, Leila Gharani has the answer!
😘
That extra index within the match to avoid CSE is genius, thank you,... Really learning from you channel! Keep it up.
That extra index within the match to avoid CSE is apparently no longer necessary with EXCEL 360 since C20:F20&C21:F21 = INDEX(C20:F20&C21:F21,0)
Impressive how Leila saved my life so many times!!! hehehe
Everytime when I have a problem I will check if my Guardian angel can help me.... most of the times she can! 😇🥰
Thank you!!
I finally get it!! After all these years getting frustrated with lookups this is going to be so helpful. Thank you for your amazing videos
YES! YES! I'm very happy to hear that :)
My mind is blown away by the beauty and simplicity of these insanely complex formulas. Your method is very helpful. Thank you!
The concept of index and match is now clear ...what a simple and effective way of presenting, not only for this function but for every function she narrates !!! Excellent !!
I'm happy to hear that. Thanks for the feedback.
Thank you Leila! It really help me getting a quick data from a Table (7728 Cells - 112 Rows - 69 Columns) in a single table using the List and both options you taught in this video. Now i can get the data from any row/column combination i need with a few clicks.
I watched the prior INDEX/MATCH video, and moved on to this one. This really fits my needs as an engineer with tons of data to evaluate or look up from a 2D data array with thousands of rows/columns. (raw data in CSV format, imported into Excel for the ease of eyes) THANK YOU!
Glad it was helpful!
Thanks!
Wow!!! scratched my head out for an entire day, until I found this genius solution. You're the best
Glad it helped!
Your clarity in excel is amazing. The way you have explained it here is unmatchable. Keep it up.
I recommend your channel to my friends who are heavily relying in excel functions to transform data and they find your video tutorials very useful.
This is genius! Thank you, I’ve been looking for this answer for more than 6 hours trying to figure out how to combine data from 2 rows with the info from the same account into 1 line row. Eg:
John | Revenue from Fish: | $200
John | Cost of Fish: | $50
John | Revenue from Fish: $200 | Cost of Fish: $50 | Profit: $150
Because of your video I finally understand index and match.
I'm glad I could help with that Brian.
I followed step-by-step and it works... and I understand the thinking behind it (that matters a lot!).
So true! I'm glad the video was helpful.
Thank you for this helpful video! The way you explain excel is so much easier to comprehend than other instructors.
These are the best tutorials I've found anywhere!!!
Thanks for the video.
It also works without an auxiliary column:
= INDEX (C22: F31; MATCH (H22; B22: B31; 0); MATCH (I20; C20: F20; 0) + (MATCH (I21; C21: F21; 0)> 1))
Thank you @LeilaGharani for all that you do! Solution #3 was perfect for my challenge. I appreciate your methodical step by step explanation in all of your videos, you are great!
You are the absolute best teacher of complex excel functions ever!
Thanks for the kind words Carol!
Omg, i have two days looking for a formula that can find my result and i finally find this video.
It helps me a lot , thank you very much😊
Glad it helped!
I depended heavily on helper cells till now, but no more now. Thank you, Leila. Your every video is a treat to Excel lovers and learners.
I want to cry my heart out! 💃💃By using a unique identifier instead of combining rows and columns. Aren't you brilliant! You have saved me from hours of brain deadlock. Your analogy is so easy to understand. I can now move on to finalise the financial report. I can't thank you enough but thank you, thank you! I will surely enroll in yr course soon.
You are so welcome!
I watched this couple of months ago and today i have used in my project work to get information from Database, which is complex [=INDEX('Exported DB-TPR'!$A$2:$A$27301,MATCH($I3&$J3&$K3,'Exported DB-TPR'!$B$2:$B$27301&'Exported DB-TPR'!$C$2:$C$27301&'Exported DB-TPR'!$D$2:$D$27301,0)) ] than the example used in the video. It was really helpful.
Thank you.
I should have used without CSE [=INDEX('Exported DB-TPR'!$A$2:$A$27301,MATCH($I3&$J3&$K3,index('Exported DB-TPR'!$B$2:$B$27301&'Exported DB-TPR'!$C$2:$C$27301&'Exported DB-TPR'!$D$2:$D$27301,0),0))] for the better .
That's a great formula! Well done! Many thanks for sharing.
super.. am going to user index & match hereafter, so many years i was doing things in lookups. Thank you.. for such a learning videos..
Thank you for making this quick and easy. This video was so good, I wish I found this years ago! YOU are good at explaining the logic and steps. You've got a new excel formula nerd fan!
Perhaps the best excel channel i have come across to explain these concepts!
Wow, thanks!
I had got into trouble with INDEX & MATCH until I found your video. Thanks & please keep it up
Am late to this tutorial . . . but unlike other people's videos, your relaxed way of explaining, actually clicked for me (the light bulb came to life) and helped me understand how to play around with the data better and its presentation.
So, now you have a late subscriber to your channel.
Welcome to the channel! Glad you liked the video :)
@@LeilaGharani - Yes there is lots to like and so-o-ohh much still to learn.
Great teaching style! Wonderful format with great examples You break formula's down and explain them step by step. Well done thanks. Great learning video, series thanks
You're very welcome Stephen. Glad to hear that.
Hi Leila, I stumbled upon your videos by random and love that your videos are high quality and very elegantly created compared to other Excel videos. I hope you continue to make great videos on Excel with emphasis on functions that'll be helpful for a marketing or finance person :)
Happy to hear that :) Sure - will do my best. Let me know if there is anything specific you're looking for and I'll gladly add to my list.
Hi Leila, I really like your tutorial, I've learned a lot from you. Just want to share this concerning the matching formula, I didn't use the exact formula you use but I got the result and it's working perfectly with the dropdown list
=INDEX(A20:F31,MATCH(H22,B20:B31,0),MATCH(I21,A21:F21,0))
: Your furmula
=INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C19:F19,0))
: Mine. Thanks
Sorry I inverse the order. The second is your formula
Thank you for these Excel instructional videos. You explain the formulas in a way that makes them easy to learn and understand. Best Excel training videos on the web.
That's very kind Walter! I'm glad you find the explanations easy to follow. Appreciate your support.
This is the reason why I loved index match function.
Two minutes in and I had the answer to the question that had been bugging me for hours! I had transposed the Index and Match ranges so it was returning and N/A. So clear. Thanks. 👍🏻😎
Nice way of explaining... you are highlighting common mistakes as if you are actually making them, helps a lot in remembering such errors and how not to make such mistakes. Keep it up!!
Thanks Vinay - Very happy to hear that. I still make mistakes when writing formulas so it's easy to remember the common ones :)
nice very effective ...thanks Leila ..actually your all tutorials are very useful and educative .
My pleasure. I'm glad you like the tutorials.
Awesome, cool, many thanks, sister. Explained a complicated formula in a very simple way with a simple example.
My pleasure. Glad the video is helpful.
thanks so much Leila, I've been using this formula repeatedly since discovering your channel
Awesome, I have been somewhat confused or at least vague on how this works, I could do simple functions but you really walked me through the logic and how to build the formulas from the ground up.
You miss, have saved me from my 5 hour agony from figuring this out. Turns out I was doing columns THEN rows instead of rows THEN columns. Thank you very much!
Such a helpful video! literally had this up as I was running through some numbers for work.
Glad it was helpful!
Leila, you saved me for the second time. Thank you!!!! ❤️
I am forwarding your videos to all my friends and colleagues. The world should now how great you are!
You're very welcome. Thanks for your support and the kind words!
I am very much attracted by your explanation. Really you are very intelligent and Pragmatic. Hats Off to your intelligence in Excel. You are my guru.
is there any word like ULTRA AWESOME ? no worries I just wanna tell you that it was so so outstanding ...priceless ...you are being blessed everyday by all who have'd benefited form this video daily.
Thank you so much Simini. I'm very glad you like the video and find it helpful :) thank you also for your support.
Queen of Excel, Leila Thank you for this video
You are Great. I was looking forward to changing 6 nested =IF(AND( = bla bla in one formula and I found this great formula you just showed and gave me the same result with less functions.
very simple explanation and thank you so much Leila for making it this simple... Thank you soo much..
You're welcome 😊
You saved my life again today, 4 years since you throw the lifeline !
Happy to help, Ismael!
Thanks , I was searching this type video of multiple criteria in index match.. many thanks Mam
easy to understand and follow, saved me so much time compared to other articles without videos
These are some of the best tutorials I've seen!!
Immense respect! As a teacher, you foresee what kind of problems a pupil could encounter and give a solution for that! Simply amazing!
I have a question though, how could we apply index & match to an array (taking data from an array and putting answers on an array) using CSE! I could work it around by using additional index within match but otherwise with CSE it ceases to be dynamic on the match argument!
Thanks! all your tips are helping me to be efficient and free up my energy :)
Hello Ms Gharani, I discovered your videos only shortly and they are fantastic! Really like your precise, logical, clean approach with good examples and with great awareness where the mouse has to stop a little while for the viewer's eye to be able to follow! You are really good! Thank you for these videos!
May I add another non-array solution _without_ helper cells that is based on the the rarely used reference form of INDEX (with 4 parameters):
=INDEX( (C22:D31;E22:F31); MATCH(H22;B22:B31;0); MATCH(I21;C21:D21;0); MATCH(I20;D20:E20;0))
The trick here is that in the 1st parameter both arrays for the "Actual" values (C22:D31) and for the "Budget" values (E22:F31) are given as a list: (C22:D31;E22:F31)
The 4th parameter then chooses which of these actually gets searched, so 1 (match result for 'Actual') gives array C22:D31 and 2 (match result for 'Budget') gives E22:F31.
The 3rd parameter now selects column 1 (for "Revenue)" or 2 (for "Profit") of this chosen array, and 2nd parameter selects the row we're seeking.
This version of INDEX rarely fits the problem, but here it does nicely. :)
Amazingly explained I must say ,now i dont need to google up every time i put in the formula.
Thank you so much
You are welcome!
Another fab video. Looks interested but can be complicated for many people. I have been working on complex INDEX MATCH situations and have mastered it through a lot of real life cases. during my training sessions. Your videos are great to learn from, especially your techniques. Thanks.
You're welcome Awesh. I agree - it does some practice to get the hang of these....
Thank you Leila, you are an amazing teacher. You really have a knack for explaining and making it easy to understand, even if it is not that easy. Keep up the good work. IMO, you should have much more views. I am recommending you to my my frends. Cheers.
One advise...it would be much more easier to follow if you left us a link to download the workbook, so we can practise and do the functions as you speak. ;) Hope you will think about it.
Forget about it, haha. You already did that :) I sould have my eyes examined :D
You're welcome Stefanija & thank you for the kind words & recommendations.
Oh my god, this is EPIC. You're so clear with your explanations! Love it!
Yay, thank you!
Hi Leila,
For my colleague in the office; who are more comfortable with the VLOOKUP and HLOOKUP; the following integration also worked well. Here, I inserted a helper row giving the column no. which will help the HLOOKUP function. The HLOOKUP function further compliments the column criteria of INDEX and gives the result.
=INDEX($C$7:$F$16,MATCH(I11,$B$7:$B$16,0),HLOOKUP(J9&J10,C3:F4,2,0))
That's a good one too Sachin! Thanks for sharing!
Your content is an absolute LIFESAVER. Thank you so much for all the videos, your content is so easy to understand!
This is the best explanation of this function I have ever seen
Thank you! I'm happy to hear that the explanation is clear.
You're a very good teacher. I had struggle understanding this but you made it so easy. Thanks so much. Keep sharing!
I'm glad it makes sense! Thank you.
You're the best...certainly the best I've seen among many instructors. Keep making our lives better in Excelandia😊
Great job --- It has some unique applications that are more precise and part of the real world of analysis.
Thank you. I'm glad you find it useful.
Ma'am, you are the ocean of Excel, I am a beginner.
I finally figured this formula out after watching your video. You are the best
Glad it helped!
Another brilliance from you Leila. Thanks once again.
Thanks for the kind feedback. I'm glad you like the tutorial.
Thanks Mrs Gharani - You are a blessing.
You are great Leila... Awesome I'm a big fan of your teachings...
......very clear and logical...so easy to follow. Thank You Leila.
Glad to hear that. You're very welcome Elisa.
it's awesome and easy to learn for an amateur person to deal with excel....thanks for explaining well.
If you're using INDEX MATCH you're definitely not amateur :) The good thing with Excel is there is always something new to learn. Glad you find the videos useful.
What a wonderful & awesome explanation of such a complex program.
Your all videos are so different from others. Simple and easy to understand for everyone. Thanks you so much.💯
This is very helpful...As always your videos are way above the others...Thx.
Mam I'm really impressed with ur way of explaining n thanks a lot
You're very welcome Teja.
Your teaching method is very very clear. Thanks for this video.
Simply brilliant!! Just like every other vids in this channel 👍👍
Thank you so much 😀
Thank you Leila, I have learnt a lot from all your videos.. Great work👍
Happy to hear that!
O Man! i wish could hug you for this Index & Match. Thank you soooooooooooooo much!
wow, very helpful video, i tried it with xmatch to avoid some steps and it works.. :)
learning excel to excel ... love your tricks, its amazing.
Glad you enjoy it!
I can't tell you how much you have helped me...again. Thank you.
My pleasure Cathy. Glad the tutorials are helpful for you.
You are great.....really I mean it...your session helped me solving a typical problem..
Brilliant! I mastered VLOOKUP and helper cells to the extreme. This is a new dimension - thanks!
Great! It will take you to the next level Joachim :)
This is like magic to me as a beginner, precise and understandable. you are great.
Thank you for the kind words. I'm really happy to hear that!
@@LeilaGharani
Hi Leila, I have a problem and hope you would help. My table looks like this A1 France A2 405 in seperate sheet A1 France B2 400 C1£3 A2 France B2 500 C2 £5 I want to look France between =< 400 less than 500 return matching value from column C, hope you can help
wow that seemed simple but it blew my mind to pieces. You made it look so simple. Thanks a bunch!
Finally, I found the solution for N/A thanks a lot for that knowledge about control shift enter ❤️❤️
You're welcome 😊
Best excel teacher! You do thing in such a creative way that makes people become to love this application. Thank you.
Great video! I finally got to understand the combination of Match and Index! Thank you so much Lelia!
My pleasure. It's a great tool to have in your kit :)
Brilliant! Thanks again Leila. Very easy to understand and follow as usual!
Thanks Steve! Glad you like it :)
Awesome video on index n match! Very clear and practical. Kudos
THANK YOU.... I really like how well you explain things.. I will try to use this instead of VLookup to learn better.
You're very welcome. I'm glad you like the explanation! It's difficult to get the hang of it. It took me a while....but once it clicks, it's just so much more flexible than Vlookup.....
Wow. Great explanation of syntax and how it can be used for alternate solutions. Thank you so much for posting this.
You're very welcome. Glad to hear you find these useful...
i love the way you teach
I'm glad to hear that.
great explanation, very easy to understand with the way you explain the formula,
even for beginners (first time learn index formula) like me
Very Good and useful video
Thank you! I'm glad you find it easy to follow :)
Great Lila, you impress me by your knowledge and your beauty.
I'm glad to hear that :)
Very clear & understandable. Well done, Cheers
It was very use full for me i used this in my project work in amazon and it made it smartly with the help of this video we need more from you
I'm very happy to hear that Affan! Glad it helped for your work :)
+Leila Gharani why cant you join Amazon....whats your professional leila