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
