Shorter one... =IF(ROW($A1)>COUNTIF(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9"),"*"&$C$3&"*"),"",INDEX(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$A$2:$A$9"),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9"))),ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9"))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9")))+1,""),ROW(A1))))
Sam On Sat, Oct 1, 2011 at 10:28 PM, Sam Mathai Chacko <samde...@gmail.com>wrote: > Interesting!!! > > Here you go... > > > =IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9"))),ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9"))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9")))+1,""),ROW(A1))),"",INDEX(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$A$2:$A$9"),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9"))),ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9"))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$2:$B$9")))+1,""),ROW($A1)))) > > Sam > > > On Sat, Oct 1, 2011 at 10:21 PM, Sourabh Salgotra <rhtdmja...@gmail.com>wrote: > >> I HAVE CHECKED ITS WORKING BUT PROBLEM IS THAT THIS FORMULA IS ON MY TIPS >> . SO THAT'S WHY I WANT ERROR HANDLER ON THIS >> >> >> On Sat, Oct 1, 2011 at 10:18 PM, Sam Mathai Chacko <samde...@gmail.com>wrote: >> >>> Well, I haven't used error handler in the first post, but if you use one >>> of the formulas from my second post, you will not get that #NUM error. Check >>> the attached file in my previous post. >>> >>> Sam >>> >>> >>> On Sat, Oct 1, 2011 at 10:12 PM, Sourabh Salgotra >>> <rhtdmja...@gmail.com>wrote: >>> >>>> VERY VERY THANKS SIR FOR HELPING ME I HAVE ONE QUERY ALSO PENDING >>>> >>>> I WANT THAT WHICH CELLS HAVE NOT ANY RESULT THEY CAN DISPLAYED AS >>>> BLANK >>>> NOT AS #NUM IN THE FORMULA THAT I HAVE USED IN ATTACHED FILE. >>>> >>>> >>>> >>>> >>>> >>>> On Sat, Oct 1, 2011 at 4:15 PM, Sam Mathai Chacko >>>> <samde...@gmail.com>wrote: >>>> >>>>> By the way, replace "SINGH" with $C$3 >>>>> >>>>> >>>>> On Sat, Oct 1, 2011 at 4:14 PM, Sam Mathai Chacko >>>>> <samde...@gmail.com>wrote: >>>>> >>>>>> Don't seem like you tried my suggestion. >>>>>> >>>>>> Anyway, since there were posts sharing two examples with HLOOKUP and >>>>>> OFFSET, I have included that also in my suggestion. >>>>>> >>>>>> HLOOKUP and OFFSET will work, but not the way it was formulated it the >>>>>> previous post. >>>>>> >>>>>> Here's the working version of it in your sample file. I have left it >>>>>> in reverse order, and will leave you to figure out how to sort it in >>>>>> ascending order as I didn't think it was critical to your original query. >>>>>> >>>>>> For the mobile users, here's the array formulas used >>>>>> >>>>>> INDEX VERSION >>>>>> >>>>>> >>>>>> =IF(LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))<>0, >>>>>> *INDEX* >>>>>> (INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$A$1:$A$100"),LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))),"") >>>>>> >>>>>> HLOOKUP VERSION >>>>>> >>>>>> =IF(LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))<>0, >>>>>> *HLOOKUP* >>>>>> (C$7,INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$A$1:$E$100"),LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1)),0),"") >>>>>> >>>>>> OFFSET VERSION >>>>>> >>>>>> =IF(LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))<>0, >>>>>> *OFFSET* >>>>>> (INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$C$1"),LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))-1,0),"") >>>>>> >>>>>> Regards, >>>>>> >>>>>> Sam Mathai Chacko (GL) >>>>>> >>>>>> >>>>>> On Sat, Oct 1, 2011 at 1:33 PM, Sourabh Salgotra < >>>>>> rhtdmja...@gmail.com> wrote: >>>>>> >>>>>>> not working >>>>>>> >>>>>>> >>>>>>> On Sat, Oct 1, 2011 at 10:40 AM, Sam Mathai Chacko < >>>>>>> samde...@gmail.com> wrote: >>>>>>> >>>>>>>> USE >>>>>>>> >>>>>>>> >>>>>>>> =INDEX(INDIRECT(TEXT(D1,"MMMYY")&"!$A$2:$A$65536"),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(D1,"MMMYY")&"!$B$2:$B$65536"))),ROW(INDIRECT(TEXT(D1,"MMMYY")&"!$B$2:$B$65536"))-MIN(ROW(INDIRECT(TEXT(D1,"MMMYY")&"!$B$2:$B$65536")))+1,""),ROW(A1))) >>>>>>>> >>>>>>>> in B8 >>>>>>>> >>>>>>>> Regards >>>>>>>> >>>>>>>> Sam Mathai Chacko (GL) >>>>>>>> >>>>>>>> >>>>>>>> On Sat, Oct 1, 2011 at 8:03 AM, Saurabh Salgotra < >>>>>>>> rhtdmja...@gmail.com> wrote: >>>>>>>> >>>>>>>>> IN THIS SHEET SEARCHING IS WORKING BUT IT IS LIMITING FOR >>>>>>>>> SEARCHING THE DATA ONLY FROM ONE SHEET(WHICH IS ADDREDSSED IN >>>>>>>>> FORMULA). >>>>>>>>> >>>>>>>>> I WANT TO SEARCH DATA FROM THE SHEET WHICH I HAVE SELECTED IN THE >>>>>>>>> DROP DOWN LIST >>>>>>>>> >>>>>>>>> I MEAN THAT WHICH SHEET IS SELECTED IN DROPDOWN LIST I GOT THE >>>>>>>>> RESULT FROM THAT SHEET. >>>>>>>>> >>>>>>>>> >>>>>>>>> FORMULA IS: >>>>>>>>> >>>>>>>>> >>>>>>>>> =INDEX(JUN12!$A$2:$A$65536,**SMALL(IF(ISNUMBER(SEARCH($C$3,** >>>>>>>>> JUN12!$B$2:$B$65536)),ROW(**JUN12!$B$2:$B$65536)-MIN(ROW(** >>>>>>>>> JUN12!$B$2:$B$65536))+1,""),**ROW(A1))) >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> IN THIS FORMULA I HAVE MENTIONED THE SHEET NAME. >>>>>>>>> >>>>>>>>> SEE DROPDOWNLIST IN D1 I HAVE SELECTED THE SHEET DEC-10, HOW I >>>>>>>>> CAN WRITE THE FORMULA FOR THIS >>>>>>>>> >>>>>>>>> -- >>>>>>>>> >>>>>>>>> ---------------------------------------------------------------------------------- >>>>>>>>> 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 >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> Sam Mathai Chacko >>>>>>>> >>>>>>>> -- >>>>>>>> >>>>>>>> ---------------------------------------------------------------------------------- >>>>>>>> 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 >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> mujhay dukh is baat ka nahin kay meri zaat ko >>>>>>> muntashir karny walay haath tairy thy >>>>>>> mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko >>>>>>> samaitnay walay haath tairy na thy >>>>>>> >>>>>>> >>>>>>> >>>>>>> -------------------------------------------------------------------------------- >>>>>>> >>>>>>> >>>>>>> Thanks & Regards >>>>>>> Sourabh >>>>>>> Contact Numbers: +91-94630-49202 >>>>>>> Website:http://adhurapyaar.co.cc >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> ---------------------------------------------------------------------------------- >>>>>>> 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 >>>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Sam Mathai Chacko >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> Sam Mathai Chacko >>>>> >>>>> -- >>>>> >>>>> ---------------------------------------------------------------------------------- >>>>> 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 >>>>> >>>> >>>> >>>> >>>> -- >>>> mujhay dukh is baat ka nahin kay meri zaat ko >>>> muntashir karny walay haath tairy thy >>>> mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko >>>> samaitnay walay haath tairy na thy >>>> >>>> >>>> >>>> -------------------------------------------------------------------------------- >>>> >>>> >>>> Thanks & Regards >>>> Sourabh >>>> Contact Numbers: +91-94630-49202 >>>> Website:http://adhurapyaar.co.cc >>>> >>>> >>>> -- >>>> >>>> ---------------------------------------------------------------------------------- >>>> 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 >>>> >>> >>> >>> >>> -- >>> Sam Mathai Chacko >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> 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 >>> >> >> >> >> -- >> mujhay dukh is baat ka nahin kay meri zaat ko >> muntashir karny walay haath tairy thy >> mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko >> samaitnay walay haath tairy na thy >> >> >> >> -------------------------------------------------------------------------------- >> >> >> Thanks & Regards >> Sourabh >> Contact Numbers: +91-94630-49202 >> Website:http://adhurapyaar.co.cc >> >> >> -- >> >> ---------------------------------------------------------------------------------- >> 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 >> > > > > -- > Sam Mathai Chacko > -- Sam Mathai Chacko -- ---------------------------------------------------------------------------------- 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