Previous Topic Next Topic

Vlookup for mutiple shel

Closed
Karthikeyan1207 Posts 16 Registration date Wednesday November 26, 2014 Status Member Last seen August 3, 2020 - Jan 6, 2015 at 03:03 AM
Karthikeyan1207 Posts 16 Registration date Wednesday November 26, 2014 Status Member Last seen August 3, 2020 - Jan 8, 2015 at 12:34 AM
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 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 7, 2015 at 12:29 AM
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???
0
RayH
Jan 7, 2015 at 11:16 AM
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.
0
Karthikeyan1207 Posts 16 Registration date Wednesday November 26, 2014 Status Member Last seen August 3, 2020 1 > RayH
Jan 8, 2015 at 12:34 AM
Hi Ray , My problem is solved with Venkat answer , thanks for your effort and support .

Regards
Karthikeyan
0
Karthikeyan1207 Posts 16 Registration date Wednesday November 26, 2014 Status Member Last seen August 3, 2020 1
Jan 8, 2015 at 12:33 AM
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
0

Similar discussions

Match partial text string in one sheet to another text string in another sheet
Santos -
Santos -

2 responses
Using Vlookup to return insted of 00/01/1900
Tom9o -
tom9o -

2 responses
Comparing columns and returning value
ll -
kittu -

35 responses
Lookup partial text contained in cell range
Tom -
Tom -

4 responses
Concanating text using vlookup forumula
gannesh0604 -
gannesh0604 -

0 responses