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
