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