Hi,

I'm trying to replace a value across two sheets based on a match in
another column between the two sheets. What I have is sheet1 with two
columns, the first is a vendor and the second an ID number.
A B
Hone 420
Cope 347
Fara 14
In sheet2, I have the same values in column A, but the value in column
B may or may not be different. Also the value in col A may be
repetitive.
A B
Hone 420
Hone 421
Hone 421
Cope 345
Cope 345
Fara 14

What I want to do is find all the values in sheet2 Col A that match
sheet1 Col A and then drop in the value in Col B from sheet1 into
sheet2. The masterlist contains 40000 lines and sheet 2 contains only
16000. Sheet2 may not have all values in col A that are in Col A of
sheet1. I'm thinking VLOOKUP, but I'm unsure how to write the formula.
Any help would be great. Thanks.


--
researcy
------------------------------------------------------------------------
researcy's Profile: http://www.excelforum.com/member.php...o&userid=31948
View this thread: http://www.excelforum.com/showthread...hreadid=516849

Researchy,

Here is a vlookup formula:

=VLOOKUP(Sheet2!A2:A2,Sheet1!$A$2:$B$40000,2,FALSE )

When you paste this formula into Sheet2 cell B2, it takes the value in
Sheet2 cell A2 and finds the row with a match on Sheet1, column A
(between rows 2 and 40000) and returns the column B value on that row to
the cell with the formula on sheet2.

Paste this into cell B2 of Sheet2 then copy it down. If your data
doesn't start in row 2 of Sheet2, then change the Sheet2!A2:A2 to
Sheet2!An:An where n is the first row of data. When you copy the
formula down, "n" will automatically increment to match the row number
for that cell.

It also assumes that the data on Sheet1 are in rows 2 to 40000. So you
need to modify the Sheet1!$A$2:$B$40000 to reflect the actual beginning
and ending rows on Sheet1. The "$" will prevent the row numbers from
changing when you copy the formula down.

After you have "updated" all of the values, you may want to decide
whether to leave the formulas in place or you can convert all of the
formulas to values by doing a copy, then Paste Special Values.

HTH,

Brian


researcy wrote:
Hi,

I'm trying to replace a value across two sheets based on a match in
another column between the two sheets. What I have is sheet1 with two
columns, the first is a vendor and the second an ID number.
A B
Hone 420
Cope 347
Fara 14
In sheet2, I have the same values in column A, but the value in column
B may or may not be different. Also the value in col A may be
repetitive.
A B
Hone 420
Hone 421
Hone 421
Cope 345
Cope 345
Fara 14

What I want to do is find all the values in sheet2 Col A that match
sheet1 Col A and then drop in the value in Col B from sheet1 into
sheet2. The masterlist contains 40000 lines and sheet 2 contains only
16000. Sheet2 may not have all values in col A that are in Col A of
sheet1. I'm thinking VLOOKUP, but I'm unsure how to write the formula.
Any help would be great. Thanks.

Thanks Brian. It works like a charm and now that I understand how the
formula is laid out, I know I can manipulate it in case the sheet name
changes or more rows are added. Thanks again.


--
researcy
------------------------------------------------------------------------
researcy's Profile: http://www.excelforum.com/member.php...o&userid=31948
View this thread: http://www.excelforum.com/showthread...hreadid=516849