Vlookup for mutiple shel [Closed]

Report
Posts
16
Registration date
Wednesday November 26, 2014
Status
Member
Last seen
April 7, 2016
-
Karthikeyan1207
Posts
16
Registration date
Wednesday November 26, 2014
Status
Member
Last seen
April 7, 2016
-
Hi ,
I have requirement as below
There is excel sheet 1 with column A with some text say xxx, yyy , xyz and coresponding numeric values in column B, C, D , E for each row in column A.
Now I have to vlookup the Text of column A from sheet 1 and bring all the column numeric values in Sheet 2 . ( sheet 2 will have the text in different orders in Column A , hence copy paste will not workout) .

Sheet example as below .
Colm A B C D E
Part no Wk1 Wk 2 W 3 Wk 4
XXX 1 0 4 5
XYZ 3 2 0 2
YYY 2 4 0 3
ZZZ 5 5 3 1

I have tried with below formula , =VLOOKUP($A2,[Book1]Sheet1!$A$2:$E$5,2,0)

whereas i need to change the shell formula in each column row which is taking lot time , also leading to errors
Formula as below for each shell
B2 =VLOOKUP($A2,[Book1]Sheet1!$A$2:$E$5,2,0)
C2=VLOOKUP($A2,[Book1]Sheet1!$A$2:$E$5,3,0)
D2=VLOOKUP($A2,[Book1]Sheet1!$A$2:$E$5,4,0)
E2=VLOOKUP($A2,[Book1]Sheet1!$A$2:$E$5,5,0)

i.e the column is changed from 2 to 5 in each column , once i did this forumla change in Row 1, then i can copy whole row formulas and paste it in Row 2 ,3 ,4 .

But still its a hectic work to change the formula in each column of row 1 , Is there any formula to simplify this action , instead of doing it in one bye one . Pls help


Regards
Karthikeyan

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
try this in B2 of secosnd sheet

=VLOOKUP($A2,Sheet1!$A$2:$E$5,COLUMN(B2),0)
drag formula to right

is this what you want???
I don't think so as this is what they do not want to do and already have in place now.

They want to have a single VLOOKUP and return 5 columns from the match.

I don't think a function can alter more than the cell it is located in so that cannot work.

Perhaps a VBA macro can be created that does what they want but it would have to be called manually or via a command button.
Karthikeyan1207
Posts
16
Registration date
Wednesday November 26, 2014
Status
Member
Last seen
April 7, 2016
1 > RayH
Hi Ray , My problem is solved with Venkat answer , thanks for your effort and support .

Regards
Karthikeyan
Karthikeyan1207
Posts
16
Registration date
Wednesday November 26, 2014
Status
Member
Last seen
April 7, 2016
1
Hi Venkat , Yes this is what I want . It works exactly , a lot of manual work is minmized now , Thanks a lot for your valuable input

Regards
Karthikeyan