Vlookup

[Bobby80]

Hi,

I have two different worksheet. Suppose sheet A and Sheet B. Sheet A have all the data (Suppose 100 records). sheet B have the data (10 records for vlookup) which I need to put the vlookup on Sheet A. Sheet A have colums from A to Z. I have done the vlookup on sheet B as =VLOOKUP($A1,SheetA!$A:$Z,2,FALSE). I am getting the value for one column corresponding to sheet A data.

My question is that, When I have done the vlookup, I want all the rows from SheetA:Z corresponding to the vlookup value cell.

e.g. Sheet A

a 32 43 54 65 76 87 897 34 23 56 87 87 23 67 87 and soon upto Column Z
b 5 54 54 43 34 65 78 98 90 65 54 6 0 56 45 78 56 .......

Sheet B : lookk up on sheetA, a
i am getting 'a' from vlookup.

but I need the answer like

a 32 43 54 65 76 87 897 34 23 56 87 87 23 67 87 which return me the rows values connect to value a.

please let me know how do i do the vlookup in simpler way to get the values.

 

[Bobby80]

<TABLE class=tborder id=post2691551 cellSpacing=0 cellPadding=6 width="100%" align=center border=0><TBODY><TR vAlign=top><TD class=alt1 id=td_post_2691551 style="BORDER-RIGHT: #ffffff 1px solid">Hi,

I have two different worksheet. Suppose sheet A and Sheet B. Sheet A have all the data (Suppose 100 records). sheet B have the data (10 records for vlookup) which I need to put the vlookup on Sheet A. Sheet A have colums from A to Z. I have done the vlookup on sheet B as =VLOOKUP($A1,SheetA!$A:$Z,2,FALSE). I am getting the value for one column corresponding to sheet A data.

My question is that, When I have done the vlookup, I want all the rows from SheetA:Z corresponding to the vlookup value cell.

e.g. Sheet A

a 32 43 54 65 76 87 897 34 23 56 87 87 23 67 87 and soon upto Column Z
b 5 54 54 43 34 65 78 98 90 65 54 6 0 56 45 78 56 .......

Sheet B : lookk up on sheetA, a
i am getting 'a' from vlookup.

but I need the answer like

a 32 43 54 65 76 87 897 34 23 56 87 87 23 67 87 which return me the rows values connect to value a.

please let me know how do i do the vlookup in simpler way to get the values.
<!-- / message --></TD></TR><TR><TD class=alt2 style="BORDER-RIGHT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-LEFT: #ffffff 1px solid; BORDER-BOTTOM: #ffffff 1px solid">

</TD><TD class=alt1 style="BORDER-RIGHT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-LEFT: #ffffff 0px solid; BORDER-BOTTOM: #ffffff 1px solid" align=right><!-- controls -->

</TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

Hi,

I have two different worksheet. Suppose sheet A and Sheet B. Sheet A have all the data (Suppose 100 records). sheet B have the data (10 records for vlookup) which I need to put the vlookup on Sheet A. Sheet A have colums from A to Z. I have done the vlookup on sheet B as =VLOOKUP($A1,SheetA!$A:$Z,2,FALSE). I am getting the value for one column corresponding to sheet A data.

My question is that, When I have done the vlookup, I want all the rows from SheetA:Z corresponding to the vlookup value cell.

e.g. Sheet A

a 32 43 54 65 76 87 897 34 23 56 87 87 23 67 87 and soon upto Column Z
b 5 54 54 43 34 65 78 98 90 65 54 6 0 56 45 78 56 .......

Sheet B : lookk up on sheetA, a
i am getting 'a' from vlookup.

but I need the answer like

a 32 43 54 65 76 87 897 34 23 56 87 87 23 67 87 which return me the rows values connect to value a.

please let me know how do i do the vlookup in simpler way to get the values.

SheetB

A1: a (which is a look up value)

B1, copy down:

=MATCH(A1,SheetA!$A$1:$A$100,0)

C1, copy across as far as needed and then down:

=IF(ISNUMBER($B1),INDEX(SheetA!B$1:B$100,$B1),"")

 

[Bobby80]

Thanks Aladin but I am not getting the values I need.

I think I didnt explained properly,

Sheet A
a pet dog cat etc.......upto column Z
b fruits apple grapes etc .........upto coumn Z
c
d
e
f
g
.
.
.

so on upto column 100

-------------------------------------------
Sheet B -- Vlookup on value b:

Once I have done the vlookup, I want my answer in below way:

I got the value as b:

which function I need to place to get the remaining connected values of b: I need my answer in below way:

b fruits apple grapes etc .........upto coumn Z

 

[Aladin Akyurek]

Thanks Aladin but I am not getting the values I need.

I think I didnt explained properly,

Sheet A
a pet dog cat etc.......upto column Z
b fruits apple grapes etc .........upto coumn Z
c
d
e
f
g
.
.
.

so on upto column 100

-------------------------------------------
Sheet B -- Vlookup on value b:

Once I have done the vlookup, I want my answer in below way:

I got the value as b:

which function I need to place to get the remaining connected values of b: I need my answer in below way:

b fruits apple grapes etc .........upto coumn Z

I'd like to urge to try the suggestion I made. Is there anything about it that needs explanation? By the way, Index/Match does the same as Vlookup, here in a better way.