Hi Dheeraj, Thanks for asking. Here is the explanation.
=SUMPRODUCT((Sheet1!$B$2:$B$25=Sheet2!$A2)*(Sheet1!$A$2:$A$25=(MIN(IF(Sheet1!$B$2:$B$25=Sheet2!A2,Sheet1! $A$2:$A$25,"")))),Sheet1!$C$2:$C$25) SumProduct formula acts as a array formula and it is very powerful function which Excel has in its kitty. Sheet1!$B$2:$B$25=Sheet2!$A2) -> this parts take care of the range which is equal to Sheet2!$A2 MIN(IF(Sheet1!$B$2:$B$25=Sheet2!A2,Sheet1!$A$2:$A$25,""))) -> this parts finds the minimum date in the data using IF conditions. Sheet1!$A$2:$A$25=(MIN(IF(Sheet1!$B$2:$B$25=Sheet2!A2,Sheet1!$A$2:$A$25,""))) -> this parts then matches minimum date in the data and identifies those particular rows. Sheet1!$C$2:$C$25 _> Based on above identifications, this parts picks up the value from the data. Let me know if you need further clarifications. -- Thanks & Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India On Thu, May 6, 2010 at 8:50 AM, <dheerajbank...@punjlloyd.com> wrote: > > Dear Dilip, > Thanks for solve the query. Can u please explain about the logic. So, I can > understand & do solve myself. > > Thanking once again. > > > Regards > > > > *Dilip Pandey <dilipan...@gmail.com>* > > 05/05/2010 02:17 PM > To > excel-macros@googlegroups.com > cc > dbank...@gmail.com > Subject > Re: $$Excel-Macros$$ > > > > > Hi Dheeraj, > > Attached is the solution you require. > Further to this, I have also considered the fact that if the data is NOT > SORTED, then still it gives right answer. > > Get back to me in case of any challenge(s). > > -- > Thanks & Regards, > > DILIP KUMAR PANDEY > MBA-HR,B.Com(Hons),BCA > Mobile: +91 9810929744* > **dilipan...@gmail.com* <dilipan...@gmail.com>* > **dilipan...@yahoo.com* <dilipan...@yahoo.com> > New Delhi - 62, India > > > On Wed, May 5, 2010 at 11:57 AM, Dheeraj Bankoti > <*dbank...@gmail.com*<dbank...@gmail.com>> > wrote: > hey guys here I attached a file, if u have any solution please help me . > > Thanking you, > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : * > http://twitter.com/exceldailytip* <http://twitter.com/exceldailytip> > 2. Join our LinkedIN group @ > *http://www.linkedin.com/groups?gid=1871310*<http://www.linkedin.com/groups?gid=1871310> > 3. Excel tutorials at > *http://www.excel-macros.blogspot.com*<http://www.excel-macros.blogspot.com/> > 4. Learn VBA Macros at > *http://www.quickvba.blogspot.com*<http://www.quickvba.blogspot.com/> > 5. Excel Tips and Tricks at > *http://exceldailytip.blogspot.com*<http://exceldailytip.blogspot.com/> > > To post to this group, send email to > *excel-mac...@googlegroups.com*<excel-macros@googlegroups.com> > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at * > http://groups.google.com/group/excel-macros/subscribe*<http://groups.google.com/group/excel-macros/subscribe> > > > > > > > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > > This e-mail and any files transmitted with it are for the sole use of the > intended recipient(s) and may contain confidential and privileged > information. If you are not the intended recipient of this communication, we > request you to notify the sender immediately by return email and destroy all > copies and the original message. Any unauthorized review, use, disclosure, > dissemination, forwarding, printing or copying of this email is strictly > prohibited. The recipient acknowledges that Punj Lloyd Limited or its > subsidiaries and associated companies (collectively "Punj Lloyd Group") are > unable to exercise control or ensure or guarantee the integrity of/over the > contents of the information contained in e-mail transmissions and further > acknowledges that any view, opinion or advice expressed in this message are > those of the individual sender and no binding nature of the message shall be > implied or assumed unless the sender does so expressly with due authority of > Punj Lloyd Group. Although we have taken all reasonable precautions to > ensure that this communication is free from viruses. We do not assume > responsibility for any loss or damage arising from the use of this email. > -- ---------------------------------------------------------------------------------- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
