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.
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 🙏
Good morning, Sir. Thank you for your hard work. I personally have learned a lot from your videos on RUclips 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.
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
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....
@@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?
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")
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.
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. ruclips.net/video/ONaS7IMKJPM/видео.html
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
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
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
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... 🙂
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.
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/
@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
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
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
I have .xltm worksheet to count distant between two cities in Sri Lanka. Column a is starting city, Column B is end city. Column C with mileage. This table 218 rows. I made a another table to calculate distance between two cities. G2 Starting city, H2 End city, Need to find Distance in to I2. Please help me with correct formula
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
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.
Thanks for your this IndexMatch session I have one query regarding which is related to vlookup In vlookup we are looking some specific value but what if the same value exist in the same column two or three times or more time repeating because we lookup always present us the first value which it can find Did you got my point?
Excel lookup formulas (vlookup, xlookup, index+match) all return just the first matching value. If you need all of them, you can use the new FILTER function. See this video - ruclips.net/video/ONaS7IMKJPM/видео.html
I need to match numeric values of column 1 to column 2 and if values match I want to have a number say 1 in column 3. Can you please guide how i can achieve this
i have working sheet in excel,,row have dates & and columns have activities of whole month,,, some activities on selected dated and other have on different dates,,, i want to write any formula if I enter date in some tab and it shows which activities in that date,,, how can i use in excel
Thanks Neelam... In that case, Excel will just return the first match. If you want all matching results, see this video ruclips.net/video/ma7u0sUIM-A/видео.html
Hello Master, I want to ask how to expressed the formula to show unique data with index and show custom coloumn with choose number of coloumn on table was created. So can show all data in new sheet with custom index data formula. Thanks
While returning the values for 1st bonus...you wrote @department...please explain that? I wrote as you did but excel wasn't running the formula... Then i simply select department as match value and it worked
You can use [@department] to refer to the department value in the current row. See my formula references video to learn more about this - ruclips.net/video/4MVNjzsGokc/видео.html
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.
Helloo another great video thank you for sharing.I send thousands of mails over outlook. However many fail to deliver. Reason may be the recipient left the org etc. In order to correct my master list on excel, I want to extract subject and recipient to filter my master list find the match and update it. I am able to fetch mail subject but not able to fetch the recipient. Since Recipient is not available in To/CC/BCC list but in mail body, I have to read mail body and will subsequently manually cross check email-id in my master list on excel. Is there an easier way to do this? Appreciate your help and time. Thanks!
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! 😊👍
Thanks Vijay for such lovely words :)
@@chandoo_ pp
The way you presented the data is great. You mentioned it in a very clean, clear, and concise manner.
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.
Wow.. thanks Harshit... Always a pleasure to hear from long time fans such as yourself. 😀
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 🙏
Glad to hear that!
True
Good morning, Sir. Thank you for your hard work. I personally have learned a lot from your videos on RUclips 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.
AWESOME Chandoo....Every day im becoming better in Excel.....Thanks a ton.
Very well explained. I was too much worried but you made it too easy. Thanks!!
The best explanation so far that I probably (hopefully) will remember for the rest of my life!!!!!!! 😸😸
Chandoo, many thanks for your practical "hands on" straightforward examples.
Simple and Effective Explanation, Thanks a Lot
U have given one of the best index and match examples
I'm going to watch and work along with this video until I have this 100%. Chandoo is the BEST!!
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.
Thank you, I will
it is free now, at least online
Great video and thanks for your patience in explaining in detail.
Great job. This video solved my query under 1 minute...
Thank You Chandoo! Learned a lot from this video. God Bless You and your kids.
Awesome Chandoo! Thanks for the INDEX + MATCH examples.. great practice! Thumbs up!!
Thanks Wayne...
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
Thanks Vishal... :)
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....
Why indians are sooooooooogood on explaining formulas and codes. You are making hard codes so easy. Thank you I ve learn a lot.
It's my pleasure
@@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?
In such cases, INDEX MATCH won't work. You need FILTER. See this - ruclips.net/video/JuTdj2j-9Kg/видео.html
Lookup for unstructured data was amazing.
THAT WAS SO CLEAR AND CRISP! THANK YOU SO MUCHHHH.
Glad it helped!
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")
Thanks Hussain. Great tip on using the not found parameter of XLOOKUP.
Chandoo has cut, cooked and pasted Index-Match!!
🔪👨🍳🍛😋
Really good one for the beginners to learn about Index and match functions. Thanks Bro!!
first time hearing "this video is sponsored by me!" awesome Chandoo keep it up!
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.
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.
ruclips.net/video/ONaS7IMKJPM/видео.html
@@chandoo_ Thanks for the solution Please make similar in Power Query referring to DAX i know I am asking too much Kindly consider
Excellent tutorial Chandoo, as usual. Thanks.
You rock Chandoo! Thanks a lot for making this video.
SIMPLY POWERFUL OR POWERFULLY SIMPLE - DEAR CHANDOO THANKS FOR SUCH AMZING LEARING SHARED
THANK YOU :)
Amazing video! very easily explained. Thanks a lot!
In the multiple conditions section, I didn't understand why the number "1" has been used in the Match Formula.
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
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
Thank you Chaitanya...
Excellent teaching, BRAVO!
Chandu me videos chusi excel ni easy ga nerchuko vachu. U r doing amazing work.
Thank you so much 🙂
Very good video and i learned all the concepts
Amazing 🤩, thank you so much!
Thank you for making this so simple to understand :)
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
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... 🙂
Thanks for keeping the Not found formula hidden, and i found it ultimately.
Very informative video...🙏
Very very lovely... looking this at 2 am 🎉 in Assam
the first example is also posible to do it with the "filter" formula like =Filter(Table[name],Table[date joined]=20-11-18)
THQ cHANdoo JI
Excellent explanation .
Excellent tutorial!
Amazing Explanation !!! Awesome.
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.
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/
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..............🙏🙏
You are awesome and making others awesome.
@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
great one to see
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
For duplicates, you either need another criteria to search table for your lookup or it will result the first searched lookup as answer
Still using Index Match, I used this-INDEX(W5:W12,XMATCH(E5:E20&D5:D20,U5:U12&V5:V12)) in two column matching
Boss You are Next
Boss you are endless
Boss You are Limitless
Great Chandoo❤️
Thanks Usman :)
This guy is too good
Your teaching us unique 🤠 amazing.
Thank you :)
Very goood explanation! keep it up.
plz elaborate why did you used 1 in MATCH
Very useful
Thank you Sir
finished watching
@4:14 we can do the same thing using Filter function
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
thank you.
very informative
If I know only first name but name exist in like Vicky Rajput, how can I fetch the complete name.
how have you got @ Department while referencing the cell in the department column !!
Definitely it's best.... !!!!!!!!!!!1
6:56 I couldn’t get the formula and why 1 was added when calculating for Multiple conditions
Thank you sir very useful and helpful
Your videos are awesome
You are most welcome
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
Sir you are pro !! Thanks for this
You are welcome
Chandoo bhai you r just awesome
Thanks Anupam bhai... 😀
How to use match/find function to get the cell address of a particular word from a worksheet?
Hi chandu, can you help me in undrtstandsing what is the use of 1 in lookup value ????
I was also wondering why 1 in the beginning of that formula ?
Please read chandoo.org/wp/advanced-sumproduct-queries/ to understand the Boolean multiplication trick.
@@chandoo_ thanks dada
Can anyone please help me, on how to get the sample excel data files he is using, I went to the link provided but they are not available????? Plsssss
I have .xltm worksheet to count distant between two cities in Sri Lanka. Column a is starting city, Column B is end city. Column C with mileage. This table 218 rows. I made a another table to calculate distance between two cities. G2 Starting city, H2 End city, Need to find Distance in to I2. Please help me with correct formula
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
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.
thanks dear
Thanks for your this IndexMatch session
I have one query regarding which is related to vlookup
In vlookup we are looking some specific value but what if the same value exist in the same column two or three times or more time repeating because we lookup always present us the first value which it can find
Did you got my point?
Excel lookup formulas (vlookup, xlookup, index+match) all return just the first matching value. If you need all of them, you can use the new FILTER function. See this video - ruclips.net/video/ONaS7IMKJPM/видео.html
I need to match numeric values of column 1 to column 2 and if values match I want to have a number say 1 in column 3. Can you please guide how i can achieve this
5:13 this portion of the video was exactly for me...thanks a lot
Sir Why you write 1 in beginning of match in multiple conditions index match
i have working sheet in excel,,row have dates & and columns have activities of whole month,,, some activities on selected dated and other have on different dates,,, i want to write any formula if I enter date in some tab and it shows which activities in that date,,, how can i use in excel
hi Chandoo really your videos are amazing..just have one query what if it has duplicate values..l
Thanks Neelam... In that case, Excel will just return the first match. If you want all matching results, see this video ruclips.net/video/ma7u0sUIM-A/видео.html
Hello Master,
I want to ask how to expressed the formula to show unique data with index and show custom coloumn with choose number of coloumn on table was created.
So can show all data in new sheet with custom index data formula.
Thanks
how to utilize to index and match for data if it is in "TABLE" format
6:46:Multi Condition
9:01:Row, column lookup
What if there is more thn one match value and we want to find all the math values?
im getting an error while trying the match function at the point where to put @ department .can anyone explain how to get it correct
Nice. Thank you!!
You are welcome...
Hi Chandoo, why did you use +1 when you did the index match for the email id..
While returning the values for 1st bonus...you wrote @department...please explain that?
I wrote as you did but excel wasn't running the formula...
Then i simply select department as match value and it worked
You can use [@department] to refer to the department value in the current row. See my formula references video to learn more about this - ruclips.net/video/4MVNjzsGokc/видео.html
Thank you sir for sharing
You are welcome Amit...
Chandoo Sir u r awesome.
Thanks Avinash... You are awesome too :)
@2:40 what if two same date are available ???
since we have sql , then why we use complicated formula instead simple sql query
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.
Helloo another great video thank you for sharing.I send thousands of mails over outlook. However many fail to deliver. Reason may be the recipient left the org etc.
In order to correct my master list on excel, I want to extract subject and recipient to filter my master list find the match and update it.
I am able to fetch mail subject but not able to fetch the recipient.
Since Recipient is not available in To/CC/BCC list but in mail body, I have to read mail body and will subsequently manually cross check email-id in my master list on excel.
Is there an easier way to do this?
Appreciate your help and time. Thanks!