Hi Mr. Shrinivas, Correcting a mistake won't be treated as hurting and if you feel like that then it's your way of thought and I don't want to comment about it. Real experts never like to see wrong answers getting delivered to the questioners and that was I did. If I am providing a wrong solution, then anyone can criticize my solution and I am always welcome to accept and learn from them.
Debate on excel will bring new ideas / techniques and which will yield better results rather than redirecting and providing wrong answer to the questioner. At the same time I am not responsible for your feelings and I don't want to waste my time in discussing and giving reply for your own feelings which is not related to excel. Better you can find any mistakes in my excel solutions and criticize it here which will be good for us and others. If you want to criticize something not related to excel then please don't include me and my name in your post and this is not the place to discuss. Hope that make sense! ----------------------- Ms.Exl.Learner ----------------------- On Sat, Sep 3, 2011 at 1:03 PM, Shrinivas Shevde <shrinivas...@gmail.com>wrote: > Dear All > I am a member of this group for last more than 2 years. > I can proudly say that u will find the solutions(not solution) for all u r > problem. > By reading the above mail (from Noorani and Excel Learner) I got some > different feeling.So my suggestion is dont critise any one > Dont write any mail which will hurt someone > Shrinivas > > On Sat, Sep 3, 2011 at 6:07 AM, Ms-Exl-Learner . <ms.exl.lear...@gmail.com > > wrote: > >> I am tired... >> >> ----------------------- >> Ms.Exl.Learner >> ----------------------- >> >> >> On Fri, Sep 2, 2011 at 11:48 PM, NOORAIN ANSARI <noorain.ans...@gmail.com >> > wrote: >> >>> Dear MS.EXL.Learner, >>> >>> Please see attached sheet............................................... >>> ** >>> Dear i better understand work and Concept of below mentioned >>> functions............i never denied to differencition between each >>> functions. >>> but at a time we can use a function for multiple purpose. >>> * * >>> *[vlookup, Index-Match, Offset-Match]* and *[Sumproduct, Sumifs, Dsum]*. >>> >>> Thanks for your suggestion.. >>> >>> -- >>> *Thanks & regards,* >>> *Noorain Ansari* >>> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> >>> >>> On Fri, Sep 2, 2011 at 11:11 PM, Ms-Exl-Learner . < >>> ms.exl.lear...@gmail.com> wrote: >>> >>>> Hi Noorain Ansari, >>>> >>>> Case-1 >>>> I have attached the same file for your reference and I have not changed >>>> anything on it just created a duplicate data. >>>> >>>> Now look at the excel file and see what are all the results your formula >>>> is deriving. Whether all the results are same? Take my previous mail and >>>> read it once again which is elaborated clearly without the help of the >>>> above >>>> attachment about the differences *[vlookup, Index-Match, Offset-Match]* >>>> and *[Sumproduct, Sumifs, Dsum]*. >>>> >>>> Case-II >>>> I know sumproduct very well and the method how we are using the function >>>> will differentiate the sumproduct from other functions. But you have used >>>> the sumproduct for a single cell, which can be done using the simple IF >>>> function and there is no need to go for sumproduct. I just wanted to >>>> highlight it to you, and that was I did. >>>> >>>> Never create and suggest formula's based on the articles you read in >>>> internet or book, understand the concept how the functions are working and >>>> it's concept then only you can able to give exact solution. >>>> >>>> Let me know if you need any further clarification. >>>> >>>> ----------------------- >>>> Ms.Exl.Learner >>>> ----------------------- >>>> >>>> On Fri, Sep 2, 2011 at 10:38 PM, NOORAIN ANSARI < >>>> noorain.ans...@gmail.com> wrote: >>>> >>>>> Dear Ms-Exl-Learner, >>>>> Thanks for your valuable suggestion.. >>>>> >>>>> Case -I, In case of duplicay all formulas are successfull working >>>>> except vlookup(vlookup) example. >>>>> You can see fresh attachement.. >>>>> >>>>> Case -II, You can't compare Sumproduct with If function both are >>>>> different.. >>>>> Correct Syntex of Formula Should be.. >>>>> =SUMPRODUCT((C3:C8=G3)*(D3:D8=H3)*E3:E8) .instead of >>>>> =SUMPRODUCT((F3=J3)*(G3=K3)*H3) >>>>> >>>>> another way : >>>>> *{=LOOKUP(2,1/(C3:C8=G3)*(D3:D8=H3),E3:E8)}* >>>>> >>>>> Keep Enjoy, Cheers.. >>>>> -- >>>>> Thanks & regards, >>>>> Noorain Ansari >>>>> >>>>> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> >>>>> >>>>> On Fri, Sep 2, 2011 at 9:51 PM, Ms-Exl-Learner . < >>>>> ms.exl.lear...@gmail.com> wrote: >>>>> >>>>>> Hi Noorain Ansari, >>>>>> >>>>>> It might be better if you might have constructed your example data >>>>>> with some duplicates, since the questioner can able to understand the >>>>>> difference between the *[vlookup, Index-Match, Offset-Match]* and >>>>>> *[Sumproduct, >>>>>> Sumifs, Dsum]*. >>>>>> >>>>>> The *First set of formula's / Functions* just get the first match as >>>>>> result and never consider the next matches. But the *Second >>>>>> functions* consider all the matches and derive the result by adding >>>>>> the values of the all matches. >>>>>> >>>>>> Please clarify me why the below formula should not be written >>>>>> as =IF((F3=J3)*(G3=K3),H3,0) or =IF(AND((F3=J3),(G3=K3)),H3,0)??? >>>>>> >>>>>> =SUMPRODUCT((F3=J3)*(G3=K3)*H3) >>>>>> >>>>>> You have provided the below formula to avoid the K3 cell criteria to >>>>>> be automated by the formula >>>>>> =VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0) >>>>>> But, >>>>>> It won't work fine when the K3 cell is having the 31-Aug as criteria >>>>>> and the G5 cell have the 31-Aug. >>>>>> >>>>>> ----------------------- >>>>>> Ms.Exl.Learner >>>>>> ----------------------- >>>>>> >>>>>> On Fri, Sep 2, 2011 at 8:51 PM, NOORAIN ANSARI < >>>>>> noorain.ans...@gmail.com> wrote: >>>>>> >>>>>>> Dear Haytham, >>>>>>> >>>>>>> Please try below formula : >>>>>>> >>>>>>> *=VLOOKUP(J3&K3,$E$3:$H$3,4,0)* >>>>>>> *=VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)* >>>>>>> other Alternative....... >>>>>>> >>>>>>> *=SUMPRODUCT((F3=J3)*(G3=K3)*H3)* >>>>>>> *{=INDEX($H$3:$H$8,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0))}* >>>>>>> *{=OFFSET($H$2,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0),0)} >>>>>>> =SUMIFS(H3:H8,F3:F8,J3,G3:G8,K3)* >>>>>>> *=DSUM($F$2:$H$8,H2,J2:K3)* >>>>>>> >>>>>>> >>>>>>> See attached sheet.. >>>>>>> -- >>>>>>> Thanks & regards, >>>>>>> Noorain Ansari >>>>>>> *http://excelmacroworld.blogspot.com/* >>>>>>> >>>>>>> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> >>>>>>> >>>>>>> >>>>>>> On Fri, Sep 2, 2011 at 5:02 PM, Haytham Zoromba < >>>>>>> haythamzoro...@gmail.com> wrote: >>>>>>> >>>>>>>> Dear all, >>>>>>>> >>>>>>>> I have tried to search about using vlookup with more than one >>>>>>>> condition. >>>>>>>> >>>>>>>> Is their any one know a formula for that? >>>>>>>> >>>>>>>> >>>>>>>> BRegards, >>>>>>>> Haytham Zoromba >>>>>>>> >>>>>>>> -- >>>>>>>> >>>>>>>> ---------------------------------------------------------------------------------- >>>>>>>> Some important links for excel users: >>>>>>>> 1. Follow us on TWITTER for tips tricks and links : >>>>>>>> http://twitter.com/exceldailytip >>>>>>>> 2. Join our LinkedIN group @ >>>>>>>> http://www.linkedin.com/groups?gid=1871310 >>>>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>>>>>> >>>>>>>> To post to this group, send email to excel-macros@googlegroups.com >>>>>>>> >>>>>>>> <><><><><><><><><><><><><><><><><><><><><><> >>>>>>>> Like our page on facebook , Just follow below link >>>>>>>> http://www.facebook.com/discussexcel >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> ---------------------------------------------------------------------------------- >>>>>>> Some important links for excel users: >>>>>>> 1. Follow us on TWITTER for tips tricks and links : >>>>>>> http://twitter.com/exceldailytip >>>>>>> 2. Join our LinkedIN group @ >>>>>>> http://www.linkedin.com/groups?gid=1871310 >>>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>>>>> >>>>>>> To post to this group, send email to excel-macros@googlegroups.com >>>>>>> >>>>>>> <><><><><><><><><><><><><><><><><><><><><><> >>>>>>> Like our page on facebook , Just follow below link >>>>>>> http://www.facebook.com/discussexcel >>>>>>> >>>>>> -- >>>>>> >>>>>> ---------------------------------------------------------------------------------- >>>>>> Some important links for excel users: >>>>>> 1. Follow us on TWITTER for tips tricks and links : >>>>>> http://twitter.com/exceldailytip >>>>>> 2. Join our LinkedIN group @ >>>>>> http://www.linkedin.com/groups?gid=1871310 >>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>>>> >>>>>> To post to this group, send email to excel-macros@googlegroups.com >>>>>> >>>>>> <><><><><><><><><><><><><><><><><><><><><><> >>>>>> Like our page on facebook , Just follow below link >>>>>> http://www.facebook.com/discussexcel >>>>>> >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> >>>>> ---------------------------------------------------------------------------------- >>>>> Some important links for excel users: >>>>> 1. Follow us on TWITTER for tips tricks and links : >>>>> http://twitter.com/exceldailytip >>>>> 2. Join our LinkedIN group @ >>>>> http://www.linkedin.com/groups?gid=1871310 >>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>>> >>>>> To post to this group, send email to excel-macros@googlegroups.com >>>>> >>>>> <><><><><><><><><><><><><><><><><><><><><><> >>>>> Like our page on facebook , Just follow below link >>>>> http://www.facebook.com/discussexcel >>>>> >>>> -- >>>> >>>> ---------------------------------------------------------------------------------- >>>> Some important links for excel users: >>>> 1. Follow us on TWITTER for tips tricks and links : >>>> http://twitter.com/exceldailytip >>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>> >>>> To post to this group, send email to excel-macros@googlegroups.com >>>> >>>> <><><><><><><><><><><><><><><><><><><><><><> >>>> Like our page on facebook , Just follow below link >>>> http://www.facebook.com/discussexcel >>>> >>> >>> >>> >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > > > -- > Shrini > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
