I need a formula that will match cells in 2 different spreadsheets and then
change the value of one cell to another.

Exactly what I need is this.

I have 2 spreadsheets. one lets call A and the Other B

In each of these spreadsheets there are columns with a SKU number and one
with a Price. what I need to do is compare the cells in the SKU column from
sheet a and sheet B for a match then use the cell in the price column from
sheet B and change the price cell in sheet A to the value of whats in B
something like below:

Sheet A Sheet B
SKU Price SKU Price
123 1.00 123 1.10

after running the formula it should look like

Sheet A
SKU Price
123 1.10

I would also like a formula that would mark in Sheet B any SKU's that were
not found in Sheet A

any way to do this, anyone able to pint me in the right direction?

Thanks

Open both workbooks
Go to B1 in A
type
=VLOOKUP(A1,
then click on the other workbook, select column A & B
then type ,2,False) to complete the formula

You will get something like this
=VLOOKUP(A1,'[Book B.xls]Sheet1'!$A:$B,2,FALSE)

This will give you the price from Book B. You will get #N/A for those SKU's
not present in Book B

You can go to Book B and do the above in C1...



"TerryB" wrote:

I need a formula that will match cells in 2 different spreadsheets and then
change the value of one cell to another.

Exactly what I need is this.

I have 2 spreadsheets. one lets call A and the Other B

In each of these spreadsheets there are columns with a SKU number and one
with a Price. what I need to do is compare the cells in the SKU column from
sheet a and sheet B for a match then use the cell in the price column from
sheet B and change the price cell in sheet A to the value of whats in B
something like below:

Sheet A Sheet B
SKU Price SKU Price
123 1.00 123 1.10

after running the formula it should look like

Sheet A
SKU Price
123 1.10

I would also like a formula that would mark in Sheet B any SKU's that were
not found in Sheet A

any way to do this, anyone able to pint me in the right direction?

Thanks

Try this formula:

=IF(Sheet1!B1Sheet2!B1,VLOOKUP(Sheet3!A1,Sheet1!$ A$1:$B$3,2,FALSE),VLOOKUP(Sheet3!A1,Sheet2!$A$1:$B $3,2,FALSE))

Pretend that the price data were located in Sheet 1 and Sheet 2. You can
type this formula in Sheet3. Basically I do a logical test to see which
figure is larger so that I know which worksheet to use.

Hope this help.


"TerryB" wrote:

I need a formula that will match cells in 2 different spreadsheets and then
change the value of one cell to another.

Exactly what I need is this.

I have 2 spreadsheets. one lets call A and the Other B

In each of these spreadsheets there are columns with a SKU number and one
with a Price. what I need to do is compare the cells in the SKU column from
sheet a and sheet B for a match then use the cell in the price column from
sheet B and change the price cell in sheet A to the value of whats in B
something like below:

Sheet A Sheet B
SKU Price SKU Price
123 1.00 123 1.10

after running the formula it should look like

Sheet A
SKU Price
123 1.10

I would also like a formula that would mark in Sheet B any SKU's that were
not found in Sheet A

any way to do this, anyone able to pint me in the right direction?

Thanks

Hi,

If you really mean what you said, that there is a value in the price column
of sheet a which you want to "change" to match the price column in sheet b,
then whatever is in sheet a's price column should just be deleted altogether
and you should start from scratch.

In which case you should use VLOOKUP(sheetA.sku,table,2,False) where table
is the range in sheet b where the sku numbers are in the leftmost column and
the prices to their right one column over. sheeta.sku just means a sku on
sheet a that you want looked up on sheet b.

Cheers,
Shane Devenshire

"TerryB" wrote:

I need a formula that will match cells in 2 different spreadsheets and then
change the value of one cell to another.

Exactly what I need is this.

I have 2 spreadsheets. one lets call A and the Other B

In each of these spreadsheets there are columns with a SKU number and one
with a Price. what I need to do is compare the cells in the SKU column from
sheet a and sheet B for a match then use the cell in the price column from
sheet B and change the price cell in sheet A to the value of whats in B
something like below:

Sheet A Sheet B
SKU Price SKU Price
123 1.00 123 1.10

after running the formula it should look like

Sheet A
SKU Price
123 1.10

I would also like a formula that would mark in Sheet B any SKU's that were
not found in Sheet A

any way to do this, anyone able to pint me in the right direction?

Thanks