Find & replace

[koobelek]

Here is my little problem:


1st spreadsheet
===============


Column A | Column B
---------+----------
AAA(Tab) | 80001
BBB(Tab) | 80002
CCC(Tab) | 80003
XXX(Tab) | 80004
YYY(Tab) | 80005
ZZZ(Tab) | 80006


2nd spreadsheet
===============


Column A
--------
AAA
AAA
AAA
AAA
BBB
BBB
BBB
BBB
CCC
CCC
YYY
YYY
ZZZ
ZZZ
ZZZ
XXX
XXX


Expected result:
----------------


2nd spreadsheet
===============


Column A
--------
80001
80001
80001
80001
80002
80002
80002
80002
80003
80003
80005
80005
80006
80006
80006
80004
80004


I need to translate column A in the 2nd spreadsheet to numbers from the

1st spreadsheet (column B). I would appreciate any suggestion.


Regards,
koobel

 

[Steve Grosz]

I would try a Vlookup function, that should do what you want

Steve

 

[Earl Kiosterud]

koobel,

=VLOOKUP(A2, 'Sheet1'!$A$2:$B$7, 2, False)

A2 is the first entry in column A of 2nd sheet. 'Sheet1'!$A$2:$B$7 is the
table in A and B of first wheet. Copy down with fill handle.

 

[koobelek]

=VLOOKUP(A2, 'Sheet1'!$A$2:$B$7, 2, False)

A2 is the first entry in column A of 2nd sheet. 'Sheet1'!$A$2:$B$7 is the
table in A and B of first wheet. Copy down with fill handle.

Oh, the formula works perfectly fine, but I'd like to copy that formula
without changing its paramaters. How do I do that?

these two parameters chenge when
I drag the cell down
| |
| |
1st row =VLOOKUP(A1,GSheetID!A1:B585,2,FALSE)
2nd row =VLOOKUP(A2,GSheetID!A2:B586,2,FALSE)
3rd row =VLOOKUP(A3,GSheetID!A2:B583,2,FALSE)
.....
7031 rows

 

[Earl Kiosterud]

koob,

Make them absolute.

=VLOOKUP($A$1,GSheetID!$A$1:$B$585,2,FALSE)

Are you sure you want the A1 to remain the same? You'll just get the same
result from all the copied formulas as it is.

 

[koobelek]

koob,

Make them absolute.

=VLOOKUP($A$1,GSheetID!$A$1:$B$585,2,FALSE)

That's what I was looking for.

Are you sure you want the A1 to remain the same? You'll just get the same

Sure, you're right. I don't want A1 to remain the same.

result from all the copied formulas as it is.

Thanks again for your help. I appreciate it.

Regards,
Koobel