Hi Bhushan, Sumproduct can be used when the result is Numeric Value.
Have a look in the attached excel for retrieving the text output. ----------------------- Ms.Exl.Learner ----------------------- On Sat, Feb 5, 2011 at 12:09 PM, Manish <pansari.man...@gmail.com> wrote: > Also Sumproduct is useful for numaric value. > Is there any other formula, that will work as SUMPRODUCT for text?? > > Pls suggest. > > On Feb 5, 11:05 am, Manish <pansari.man...@gmail.com> wrote: > > Is it possible to hide the complete row based on lookup value?? > > For example in your solution, if i lookup value x than i want to hide > > row 14 because the lookup result is 0 /Blank. And if i change the > > value and look the value y i want to hide both row 13 and 14. > > > > Simply, I want automtion for hide blank rows. > > pls suggest the code OR upload the example file for reference. > > > > Thanks !! > > > > - > > Manish > > > > On Feb 2, 6:41 pm, ashish koul <koul.ash...@gmail.com> wrote: > > > > > > > > > @bushan > > > > > mail your file to id excel-macros@googlegroups.com and attach the > file > > > > > On Wed, Feb 2, 2011 at 4:42 PM, Bhushan <bsabban...@gmail.com> wrote: > > > > Hi, > > > > > > I am not finding any link from where i can upload my sample file. > > > > > > Regards. > > > > Bhushan > > > > > > On Feb 2, 2:11 pm, Manish <pansari.man...@gmail.com> wrote: > > > > > Thanks for valuable post. > > > > > Is it possible to hide the complete row based on lookup value?? > > > > > For example in your solution, if i lookup value x than i want to > hide > > > > > row 14 because the lookup result is 0 /Blank. And if i change the > > > > > value and look the value y i want to hide both row 13 and 14. > > > > > > > pls suggest the code OR upload the example file for reference. > > > > > > > Thanks !! > > > > > > > - > > > > > Manish > > > > > > > On Feb 1, 11:23 pm, "Ms-Exl-Learner ." <ms.exl.lear...@gmail.com> > > > > > wrote: > > > > > > > > Hi Bhushan, > > > > > > > > Have a look in the attached files. > > > > > > > > I never suggest array formula when the same can be done in normal > way. > > > > > > > > HTH :) > > > > > > > > ----------------------- > > > > > > Ms.Exl.Learner > > > > > > ----------------------- > > > > > > > > On Tue, Feb 1, 2011 at 6:22 PM, Bhushan <bsabban...@gmail.com> > wrote: > > > > > > > Dear Ayush, > > > > > > > > > Pls assist me for the below formula. I have a huge data from > that I > > > > > > > have given a sample below for your understanding. I have a data > with > > > > > > > lookup value with different corresponding values. When I am > using the > > > > > > > vlookup the vlookup is only taking the one correponding value > of that > > > > > > > lookup I am using the below formula for the diffrenet > corresponding > > > > > > > values the problem is when the lookup value changes the formula > gives > > > > > > > number error. I have manual change the row(4:4) in formula or > > > > row(3:3) > > > > > > > anything to (1:1) in formula to get the corresponding values of > > > > > > > another lookup. > > > > > > > > > Name Item > > > > > > > x 1 > > > > > > > y 2 > > > > > > > x 5 > > > > > > > y 8 > > > > > > > x 3 > > > > > > > > > OPF No CPO Item Qty Formula > > > > > > > x 1 > > > > =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$7)),ROW(1:1)), > > > > > > > 2) > > > > > > > x 5 > > > > =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$8)),ROW(2:2)), > > > > > > > 2) > > > > > > > x 3 > > > > =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$9)),ROW(3:3)), > > > > > > > 2) > > > > > > > y > > > > =INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=A13,ROW($A$1:$A$6)),ROW(4:4)),2) > > > > > > > there I have to change the row(4:4) to row(1:1) to get the > > > > > > > corresponding value of the y lookup manual. > > > > > > > > > Regards > > > > > > > Bhushan Sabbani > > > > > > > 98208 26012 > > > > > > > > > -- > > > > > > > ---------------------------------------------------------------------------------- > > > > > > > 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 athttp://www.excel-macros.blogspot.com > > > > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > > > > > 5. Excel Tips and Tricks athttp://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 > > > > > > > > -- > > > > > > > > Correction in ur Formula.xls > > > > > > 19KViewDownload > > > > > > > > My Solution.xls > > > > > > 18KViewDownload- Hide quoted text - > > > > > > > > - Show quoted text - > > > > > > -- > > > > > > > ---------------------------------------------------------------------------------- > > > > 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 athttp://www.excel-macros.blogspot.com > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > > 5. Excel Tips and Tricks athttp://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 > > > > > -- > > > *Regards* > > > * * > > > *Ashish Koul* > > > *akoul*.*blogspot*.com <http://akoul.blogspot.com/> > > > *akoul*.wordpress.com <http://akoul.wordpress.com/> > > > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830 > > > > > > > P Before printing, think about the environment.- Hide quoted text - > > > > > - Show quoted text -- Hide quoted text - > > > > - Show quoted text - > > -- > > ---------------------------------------------------------------------------------- > 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 > -- -- ---------------------------------------------------------------------------------- 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
My Solution-1- For Gettting Text Result.xls
Description: MS-Excel spreadsheet
