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
--
----------------------------------------------------------------------------------
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
