Excel's INDEX + MATCH - How to use it // 7 real-world examples & tips

แชร์
ฝัง
  • เผยแพร่เมื่อ 25 พ.ย. 2024

ความคิดเห็น • 260

  • @vijayarjunwadkar
    @vijayarjunwadkar 3 ปีที่แล้ว +56

    No MATCH for Chandoo's training and our learning INDEX is going high for sure! Thank you for this nice tutorial on these very useful functions! 😊👍

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +4

      Thanks Vijay for such lovely words :)

    • @Jafortgroup
      @Jafortgroup 2 ปีที่แล้ว

      @@chandoo_ pp

  • @harshitdhupia
    @harshitdhupia 3 ปีที่แล้ว +15

    I have been following Chandoo since last 12 years. And believe me, when ever I face any issue in MS Excel his tutorials, mails and blogs were really helpful for me to solve the issue.
    Whichever organization I join people know me as Excel expert.
    Thanks Chandoo Sir.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +3

      Wow.. thanks Harshit... Always a pleasure to hear from long time fans such as yourself. 😀

  • @manojpai8506
    @manojpai8506 3 ปีที่แล้ว +14

    You never cease to amaze me Chandoo.you turned around a simple index and match function to solve complex problems for people having no access to Microsoft Excel 365..you are an exceptional genius and i genuinely thank you for all your efforts in helping the excel community in furthering their knowledge in this field 🙏

  • @jameszack7158
    @jameszack7158 3 ปีที่แล้ว +1

    The best Excel instructions channel on Earth has only 110,000 subscribers.

  • @BockarieTNgegba
    @BockarieTNgegba 3 หลายเดือนก่อน +1

    Good morning, Sir. Thank you for your hard work. I personally have learned a lot from your videos on TH-cam here. It's 2:14 AM here in Sierra Leone local time. I Strongly want to become a Business data Analyst.
    Once again thank you very much and God Richly Bless you.

  • @vishalbhati912
    @vishalbhati912 3 ปีที่แล้ว +1

    8:50 few days ego. I solved same problem. In large data. Same article in multiple warehouse. And i solved it with helper column. And concatinate both field. Then used vlookup. But ur solution is expert type. Thank u

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      Thanks Vishal... :)

  • @AmitKumar-pz7tu
    @AmitKumar-pz7tu 2 หลายเดือนก่อน

    The way you presented the data is great. You mentioned it in a very clean, clear, and concise manner.

    • @noobkerala
      @noobkerala หลายเดือนก่อน +1

      Did you understand? Tell me why he use 1 ? He said you will understand why he used 1 .

    • @navajiskhan9305
      @navajiskhan9305 หลายเดือนก่อน

      ​@@noobkeralayeah bro..😂
      Btw I also having same doubt

  • @thidarwin5029
    @thidarwin5029 10 หลายเดือนก่อน +1

    I have watched some index, match videos on youtube. This one helped me to find out needed formula for my work. Thank you Chandoo.

    • @chandoo_
      @chandoo_  10 หลายเดือนก่อน +1

      Glad it was helpful!

  • @anil9985
    @anil9985 2 ปีที่แล้ว

    Chandu me videos chusi excel ni easy ga nerchuko vachu. U r doing amazing work.

    • @chandoo_
      @chandoo_  2 ปีที่แล้ว

      Thank you so much 🙂

  • @shaikusman536
    @shaikusman536 3 ปีที่แล้ว +1

    AWESOME Chandoo....Every day im becoming better in Excel.....Thanks a ton.

  • @rammsteinx6358
    @rammsteinx6358 2 ปีที่แล้ว

    The best explanation so far that I probably (hopefully) will remember for the rest of my life!!!!!!! 😸😸

  • @donbosco7535
    @donbosco7535 2 ปีที่แล้ว

    U have given one of the best index and match examples

  • @ndabawangure4322
    @ndabawangure4322 3 ปีที่แล้ว +1

    Chandoo, many thanks for your practical "hands on" straightforward examples.

  • @bharathramc.n7796
    @bharathramc.n7796 3 ปีที่แล้ว

    Hi Chandoo
    I am following from 2008 and enrolled in almost all your course you have made which has made a good excel user for the day to day activity.
    In your example you have shown the value to fetch when the match values are unique (referring to the first eg) what if there are more than one similar values.
    Index having few more of same name with same date or in match having two more similar dates.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Bharathram for your love and support all these years :)
      In your case, you need to use FILTER() to get all matching results. See this video for a demo and explanation of FILTER formula.
      th-cam.com/video/ONaS7IMKJPM/w-d-xo.html

    • @bharathramc.n7796
      @bharathramc.n7796 3 ปีที่แล้ว

      @@chandoo_ Thanks for the solution Please make similar in Power Query referring to DAX i know I am asking too much Kindly consider

  • @preetichadichal9791
    @preetichadichal9791 2 ปีที่แล้ว

    Your content is awesome....apart from that what I like the most about your videos is that your tone is very composed and slow....unlike most youtubers who talk rapidly, your clear and steady pace is a break from others....

  • @specificgravity-thedancing9700
    @specificgravity-thedancing9700 2 ปีที่แล้ว

    I'm going to watch and work along with this video until I have this 100%. Chandoo is the BEST!!

  • @jalaluddin8276
    @jalaluddin8276 ปีที่แล้ว

    Thank You Chandoo! Learned a lot from this video. God Bless You and your kids.

  • @prakashrao3607
    @prakashrao3607 3 ปีที่แล้ว +3

    Very crisp and clear explanation with good practical examples. Please keep posting more videos on normal Excel as most of us don't have 365.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thank you, I will

    • @themediq999
      @themediq999 ปีที่แล้ว

      it is free now, at least online

  • @joncannaday250
    @joncannaday250 3 ปีที่แล้ว +1

    INDEX(A:C,MATCH("TRUE TRUE",(B1=Sheet2!A2:A201)&" "&(A1=Sheet2!B2:B201),0)+1,1)
    You can look up one value in a list of duplicates by forging a unique grouping and having the array as a logic table
    Thank you for the videos, they have helped make my job more user friendly

  • @excelworx8712
    @excelworx8712 3 ปีที่แล้ว +4

    Chandoo has cut, cooked and pasted Index-Match!!

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +2

      🔪👨‍🍳🍛😋

  • @madhunisha5115
    @madhunisha5115 3 หลายเดือนก่อน

    Very well explained. I was too much worried but you made it too easy. Thanks!!

  • @hadianasliwa
    @hadianasliwa 9 หลายเดือนก่อน

    first time hearing "this video is sponsored by me!" awesome Chandoo keep it up!

  • @lotpottrack4015
    @lotpottrack4015 2 ปีที่แล้ว

    Chandoo, it been long time for me to know easiest method and today i got chance to learn in one click... Where did you get those simple magic words... Love you bro..

  • @hussainakhtar1094
    @hussainakhtar1094 3 ปีที่แล้ว +2

    Awesome Video explanations. Just want to add we can also use Xlookup without including ifError, so in the above example at 3.50 we can also use formula =XLOOKUP(L13,G5:G20,C5:C20,"Not found")

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Hussain. Great tip on using the not found parameter of XLOOKUP.

  • @gladyscancino1790
    @gladyscancino1790 3 หลายเดือนก่อน

    Why indians are sooooooooogood on explaining formulas and codes. You are making hard codes so easy. Thank you I ve learn a lot.

    • @chandoo_
      @chandoo_  3 หลายเดือนก่อน

      It's my pleasure

    • @gladyscancino1790
      @gladyscancino1790 3 หลายเดือนก่อน

      @@chandoo_ I have problem though Chandoo.On your first example what if a lot of names joined on Nov 18? Hope you can help me :) is index match still the solution?

    • @chandoo_
      @chandoo_  3 หลายเดือนก่อน

      In such cases, INDEX MATCH won't work. You need FILTER. See this - th-cam.com/video/JuTdj2j-9Kg/w-d-xo.html

  • @prashanthek5036
    @prashanthek5036 หลายเดือนก่อน

    It was very easy to understand chandoo!!!!

  • @ndukaanalogbei1043
    @ndukaanalogbei1043 ปีที่แล้ว

    Great job. This video solved my query under 1 minute...

  • @FanMusicIndian
    @FanMusicIndian 2 ปีที่แล้ว +1

    Great video and thanks for your patience in explaining in detail.

  • @kezeiah3644
    @kezeiah3644 ปีที่แล้ว

    THAT WAS SO CLEAR AND CRISP! THANK YOU SO MUCHHHH.

    • @chandoo_
      @chandoo_  ปีที่แล้ว +2

      Glad it helped!

  • @WaleedAbd-tb9rj
    @WaleedAbd-tb9rj 6 หลายเดือนก่อน

    Lookup for unstructured data was amazing.

  • @rameshks8449
    @rameshks8449 5 หลายเดือนก่อน

    Simple and Effective Explanation, Thanks a Lot

  • @wayneedmondson1065
    @wayneedmondson1065 3 ปีที่แล้ว +1

    Awesome Chandoo! Thanks for the INDEX + MATCH examples.. great practice! Thumbs up!!

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      Thanks Wayne...

  • @FrancisDCosta
    @FrancisDCosta 2 ปีที่แล้ว

    Excellent tutorial Chandoo, as usual. Thanks.

  • @VIPULDJHAVERI
    @VIPULDJHAVERI 3 ปีที่แล้ว

    SIMPLY POWERFUL OR POWERFULLY SIMPLE - DEAR CHANDOO THANKS FOR SUCH AMZING LEARING SHARED

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      THANK YOU :)

  • @prakashsampath1532
    @prakashsampath1532 ปีที่แล้ว

    Really good one for the beginners to learn about Index and match functions. Thanks Bro!!

  • @PriyankaLiyanage
    @PriyankaLiyanage วันที่ผ่านมา

    Thank you very much Mr. Chandoo.

  • @krishnateja7728
    @krishnateja7728 2 ปีที่แล้ว

    You rock Chandoo! Thanks a lot for making this video.

  • @talhaiftikhar6195
    @talhaiftikhar6195 ปีที่แล้ว

    Thanks for keeping the Not found formula hidden, and i found it ultimately.

  • @chaitanyajuttiga9427
    @chaitanyajuttiga9427 2 ปีที่แล้ว

    godbless you dear bro you are doing an amazing job by servcing the students and other needies who dont have the access to all education content .. more from you

    • @chandoo_
      @chandoo_  2 ปีที่แล้ว

      Thank you Chaitanya...

  • @canirmalchoudhary8173
    @canirmalchoudhary8173 3 ปีที่แล้ว

    Still using Index Match, I used this-INDEX(W5:W12,XMATCH(E5:E20&D5:D20,U5:U12&V5:V12)) in two column matching

  • @shreyanshdangi4366
    @shreyanshdangi4366 5 หลายเดือนก่อน

    Very good video and i learned all the concepts

  • @shelleyfranklin2884
    @shelleyfranklin2884 ปีที่แล้ว

    Excellent tutorial!

  • @nitinkakria7936
    @nitinkakria7936 ปีที่แล้ว

    Amazing video! very easily explained. Thanks a lot!

  • @lokeshagarwal6373
    @lokeshagarwal6373 ปีที่แล้ว

    Very very lovely... looking this at 2 am 🎉 in Assam

  • @saadsalim1036
    @saadsalim1036 9 หลายเดือนก่อน

    Boss You are Next
    Boss you are endless
    Boss You are Limitless

  • @lavelclaytorsProjectswPurpose
    @lavelclaytorsProjectswPurpose 2 ปีที่แล้ว +1

    Amazing 🤩, thank you so much!

  • @akoredeadebayo4269
    @akoredeadebayo4269 3 ปีที่แล้ว

    Excellent teaching, BRAVO!

  • @ajaysuresh8856
    @ajaysuresh8856 ปีที่แล้ว

    Amazing Explanation !!! Awesome.

  • @feroskhan9529
    @feroskhan9529 ปีที่แล้ว

    You are awesome and making others awesome.

  • @PayelSaha-ix7on
    @PayelSaha-ix7on 5 หลายเดือนก่อน

    Excellent explanation .

  • @RyanJackson2
    @RyanJackson2 ปีที่แล้ว

    This guy is too good

  • @manasamvs8300
    @manasamvs8300 2 ปีที่แล้ว

    Thank you for making this so simple to understand :)

  • @usmanchaudhri148
    @usmanchaudhri148 2 ปีที่แล้ว

    God dammmmn man yuuu are doing really great work i am learning a lot from here thanks for all yurr efforts yuu are doing better for us..............🙏🙏

  • @AnkitGupta-n6e
    @AnkitGupta-n6e 6 หลายเดือนก่อน

    Very informative video...🙏

  • @christiandavid1996
    @christiandavid1996 5 หลายเดือนก่อน

    the first example is also posible to do it with the "filter" formula like =Filter(Table[name],Table[date joined]=20-11-18)

  • @saikatdhar6381
    @saikatdhar6381 3 ปีที่แล้ว +1

    Dear sir, I am a follower of your excellent videos of Excel & it is really helpful for me. Want to know why you use 1 as "Look up Value" in the video (7:15-7:18). Thanks for ur splendid effort to teach us Excel.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +2

      Thanks Saikat for following my channel and appreciating my work. :)
      You can refer to this pages for more information on why 1 is used.
      chandoo.org/wp/excel-sumproduct-formula/
      chandoo.org/wp/advanced-sumproduct-queries/

  • @EBINESANA-tj5lr
    @EBINESANA-tj5lr ปีที่แล้ว

    Very goood explanation! keep it up.

  • @usmaniqbal1836
    @usmaniqbal1836 3 ปีที่แล้ว +1

    Great Chandoo❤️

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      Thanks Usman :)

  • @ksbharat3806
    @ksbharat3806 3 ปีที่แล้ว +1

    great one to see

  • @anupammishra1013
    @anupammishra1013 2 ปีที่แล้ว

    Chandoo bhai you r just awesome

    • @chandoo_
      @chandoo_  2 ปีที่แล้ว

      Thanks Anupam bhai... 😀

  • @JunaidAkhtarKhan-b9p
    @JunaidAkhtarKhan-b9p 2 หลายเดือนก่อน +1

    THQ cHANdoo JI

  • @daverajesh71
    @daverajesh71 ปีที่แล้ว +1

    Hi Chandoo. You have been doing a great job in explaining the concepts based on real life requirements. Keep it up. On the Index & Match Function explained by you if the joining date of two employees are the same how does Index & Match Function work and how do you overcome this

  • @डकुमेन्टमेरा
    @डकुमेन्टमेरा 3 ปีที่แล้ว

    Your teaching us unique 🤠 amazing.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thank you :)

  • @AbhishekYadav-tn2lj
    @AbhishekYadav-tn2lj 3 ปีที่แล้ว

    Sir you are pro !! Thanks for this

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You are welcome

  • @GeorgeAJululian
    @GeorgeAJululian 2 ปีที่แล้ว

    Thank you sir very useful and helpful
    Your videos are awesome

    • @chandoo_
      @chandoo_  2 ปีที่แล้ว

      You are most welcome

  • @fujaielxrahman7352
    @fujaielxrahman7352 2 ปีที่แล้ว

    Thank you man!

  • @eliastafese3790
    @eliastafese3790 2 ปีที่แล้ว

    Very useful

  • @rajanverma-wb6nm
    @rajanverma-wb6nm 8 หลายเดือนก่อน

    Definitely it's best.... !!!!!!!!!!!1

  • @sandipansarkar9211
    @sandipansarkar9211 ปีที่แล้ว

    finished watching

  • @srikanthpillamari6598
    @srikanthpillamari6598 6 หลายเดือนก่อน

    Hi Chandoo. Thank you very much for the invaluable content. Could you please be kind enough to address my question as I could not figure out why we used "1" as lookup value in Match function.... Thank you, again... 🙂

  • @avinashpatil4819
    @avinashpatil4819 3 ปีที่แล้ว

    Chandoo Sir u r awesome.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Avinash... You are awesome too :)

  • @invenew
    @invenew 2 ปีที่แล้ว +5

    Good.
    But this works with only unique list ( Date or Salary). It would be very good to show us how these function work with duplicate lists. Thankyou

    • @rakeshsinghprs
      @rakeshsinghprs ปีที่แล้ว

      For duplicates, you either need another criteria to search table for your lookup or it will result the first searched lookup as answer

  • @Zackker1467
    @Zackker1467 3 ปีที่แล้ว

    Lots of love chandoo

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Nirmal 😍

  • @yashagrawal1644
    @yashagrawal1644 2 ปีที่แล้ว +1

    6:46:Multi Condition
    9:01:Row, column lookup

  • @SantiagoMolero
    @SantiagoMolero 3 ปีที่แล้ว

    Nice. Thank you!!

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You are welcome...

  • @shivendrapatel443
    @shivendrapatel443 2 ปีที่แล้ว

    Thanks Chandoo for this video.
    I have two queries.
    1. What if two people have the same salary in 2nd example, which name it would give?
    2. You didn't explain why we looked for 1 in multi condition example??
    Thanks

    • @chandoo_
      @chandoo_  2 ปีที่แล้ว

      All lookup functions in Excel (vlookup, match, xlookup, hlookup, lookup) give the first matching result only.
      Please refer to my other comments or the links in description for details about the 1.

  • @easyofficetutorials7234
    @easyofficetutorials7234 2 ปีที่แล้ว

    thank you.

  • @Spectre.gamiing
    @Spectre.gamiing ปีที่แล้ว

    @4:14 we can do the same thing using Filter function

  • @thenotidevil
    @thenotidevil 3 ปีที่แล้ว

    Thank you sir for sharing

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You are welcome Amit...

  • @Mega764
    @Mega764 ปีที่แล้ว

    thanks dear

  • @taizoondean689
    @taizoondean689 3 ปีที่แล้ว

    Thanks 👍

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You are welcome :)

  • @dhksmt
    @dhksmt 2 ปีที่แล้ว +5

    In the multiple conditions section, I didn't understand why the number "1" has been used in the Match Formula.

    • @ahmedhelal920
      @ahmedhelal920 2 ปีที่แล้ว

      The MATCH function with a lookup value of 1 finds the first "1" in the array and passes its position to INDEX, which returns a value in this row from the specified column.01‏/05‏/2022

  • @ebinezarpaulraj7517
    @ebinezarpaulraj7517 2 ปีที่แล้ว

    Awesome

  • @vishal80
    @vishal80 3 ปีที่แล้ว

    Totally O. P.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      😍😀

  • @kishorpanara4191
    @kishorpanara4191 3 ปีที่แล้ว

    You are great dear chandoo you know hindi

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Kishor. Yes I know

  • @dilipshedge8503
    @dilipshedge8503 3 ปีที่แล้ว

    Thank you ❤️

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You’re welcome 😊

  • @NOMIZYGOUS
    @NOMIZYGOUS ปีที่แล้ว

    Multimatch ❤❤❤

  • @kuunami
    @kuunami ปีที่แล้ว

    xlookup is so much better but my class requires me to use index/match or vlookup. I guess it makes sense to know how to use it anyway.

  • @neelamkushwaha1703
    @neelamkushwaha1703 3 ปีที่แล้ว

    hi Chandoo really your videos are amazing..just have one query what if it has duplicate values..l

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Neelam... In that case, Excel will just return the first match. If you want all matching results, see this video th-cam.com/video/ma7u0sUIM-A/w-d-xo.html

  • @movetofit5908
    @movetofit5908 ปีที่แล้ว +1

    Hi Chandoo, I want to switch my career into Data Analytics, currently i am into LaTeX template development, this is the language which is industry specific and i am stucked into it, there is no growth. Please guide me how could I come into data analytics field. My age is 43 right now, is it okay if i will give myself 3 months to learn the data analytics skill and switch into it at this age. Please guide me the path or any course which I can opt for this. I am aware about excel and SQL though. TIA.

  • @mohammedshameelv74
    @mohammedshameelv74 3 ปีที่แล้ว

    Thank you sir

  • @Amank3107
    @Amank3107 3 ปีที่แล้ว

    Thank you this is really awesome and simple

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You're welcome 😊

  • @yogeshindulkar320
    @yogeshindulkar320 3 ปีที่แล้ว

    Hello sir, please make a video on your journey from India to newziland

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      May be some other time? I will try to answer these kind of questions in a live stream too.

  • @sriramamurty1298
    @sriramamurty1298 3 ปีที่แล้ว

    Good....Can you please cover on 'Analytic Solver' in Excel

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      Thanks for the suggestion Srirama. I have a video on solver here - th-cam.com/video/hbEn_CeYr6U/w-d-xo.html

  • @AP-eb8hd
    @AP-eb8hd 3 ปีที่แล้ว +3

    After indroduction of XLOOKUP, I hardly have gone back to Index Matches. It's still good and has it's advantages in some cases. Performance wise, not sure which fares better.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +3

      You are right. But learning this combination is essential. I have shown in the video a few situations where INDEX+MATCH really shines. If you know the usage of these functions, that will take you very far.

  • @nikhilsoni1177
    @nikhilsoni1177 3 ปีที่แล้ว +1

    Hi chandu, can you help me in undrtstandsing what is the use of 1 in lookup value ????

    • @excellover7172
      @excellover7172 3 ปีที่แล้ว

      I was also wondering why 1 in the beginning of that formula ?

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      Please read chandoo.org/wp/advanced-sumproduct-queries/ to understand the Boolean multiplication trick.

    • @nikhilsoni1177
      @nikhilsoni1177 3 ปีที่แล้ว

      @@chandoo_ thanks dada

  • @mindhealer.4138
    @mindhealer.4138 ปีที่แล้ว

    But why 1 was used in match i dont get

  • @cahimanshugupta1475
    @cahimanshugupta1475 ปีที่แล้ว

    How to use match/find function to get the cell address of a particular word from a worksheet?

  • @gashyasai9232
    @gashyasai9232 2 หลายเดือนก่อน

    @chandoo bro i need a suggestion and guidence while using index match function i have a data like in horizontal rows different names of persons in vertical colunm i have series of dates in month in those dates daily wage amount of workers is provided? how should i use there index match function

  • @subhadrat.s.4511
    @subhadrat.s.4511 ปีที่แล้ว

    Sir
    If the data is in different worksheet (for example of TB OF DIFFERENT branch/unit) need to take a particular income or expense how to get the output in a,single worksheet
    Please guide