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
