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 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
Correction in ur Formula.xls
Description: MS-Excel spreadsheet
My Solution.xls
Description: MS-Excel spreadsheet
