replacing value between sheets!

[via135]

hi all!

i am having two worksheets. sheet1COL"A" having some defined values &
sheet1COL"B" having some related defined names.

sheet2 COL"A" having the same defined values as in sheet1 but randomly
repetitive and COL"B" having names but entirely different with
sheet1COL"B".

what i want is to replace the names in sheet2COL"B" with the related
difined names as in sheet1COL"B"!

example

sheet1
COL"A" COL"B"
10 aaa
20 bbb
30 ccc
40 ddd
50 eee

sheet2
COL"A" COL"B"
10 abc
20 bca
10 cab
30 cba
40 xyz
50 yzx
20 mno


result expected
sheet2
COL"A" COL"B"
10 aaa
20 bbb
10 aaa
30 ccc
40 ddd
50 eee
20 bbb



help pl?

-via135

 

[Guest]

in col b sheet 2
=vlookup(a1,sheet1!$A$1:$B$5,2,false),and copy down.Adjust the table a1:b5
to reflect the actual size of your tble but you must use the $ symbols to
make an absolute reference to the table

 

[via135]

sorry Paul!

i am getting error #REF!
what's wrong with me?

-via135

 

[daddylonglegs]

What formula are you using?

#REF! possibly means that the sheet name is not recognised. Check you
are referencing the exact sheet name. If sheet name contains spaces use
single quotes around sheet name as below

=vlookup(a1,'sheet 1'!$A$1:$B$5,2,false)

 

[via135]

hi!

i am using the exact sheet name! and my formula is

=(VLOOKUP(A1,Sheet7!$A$1:$A$6,2,FALSE))

can u help me pl?

-via135

 

[Peo Sjoblom]

That formula can never return anything but an error, however that error
should be
#N/A You are trying to return a value in the second column from a table that
is only one column
If you want the 2nd column you need to use

=VLOOKUP(A1,Sheet7!$A$1:$B$6,2,FALSE)

now you can get ref errors if you delete a row where a formula points to, if
you use INDIRECT incorrectly
or if you already have a ref error in a range you are using. You might want
to check that, the version of your formula
that I posted works


--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Pete]

Your table is only one column wide ($A$1:$A$6), yet you are trying to
get data from the second column. I suggest you change the formula to:

=(VLOOKUP(A1,Sheet7!$A$1:$B$6,2,FALSE))

Hope this helps.

Pete

 

[via135]

thank you all for pinpointing my stupid mistake!

after adjusting my array ref to 2 col ($a$1:$b$6) the formula works
nicely!

thks again!

-via135