Compare values on sheet 1 to values on sheet2
 

I would like to compare data in Column B (sheet1) with data that is in
ColumnC (sheet2). The vlookup needs to reference the Primary Key in Column
A (sheet2) to the Primary Key in Column A (sheet2) and then compare the in
the above referenced columns in the same row. I'd like to know if the value
is the same or what the difference in the value is.

Sheet 2 doesn't have all the Keys from sheet 1 and they may be in a
different order, so I know I need a lookup to find key and then compare
value, but I don't know how to. Here's an example of the data I have.

Sheet1
NAME COST Difference(desired new column)
Project1 129
Project2 155
Project3 300
Project4 240
Sheet2
NAME DATE COST
Project2 12/20 155
Project4 12/30 310
Project3 12/18 320

I'd like to see a -20 in the difference column for Project3.

Thanks,

Colin

Compare values on sheet 1 to values on sheet2
 

Colin

one way:

=B2-IF(ISNA(VLOOKUP(A2,Sheet2!A:C,3,FALSE)),0,VLOOKUP( A2,Sheet2!A:C,3,FALSE))

You need to decide what value you want to show if the Project code isn't
found ... but I'll leave that for you to think through.

Regards

Trevor


"Colin" wrote in message
...
I would like to compare data in Column B (sheet1) with data that is in
ColumnC (sheet2). The vlookup needs to reference the Primary Key in Column
A (sheet2) to the Primary Key in Column A (sheet2) and then compare the in
the above referenced columns in the same row. I'd like to know if the
value is the same or what the difference in the value is.

Sheet 2 doesn't have all the Keys from sheet 1 and they may be in a
different order, so I know I need a lookup to find key and then compare
value, but I don't know how to. Here's an example of the data I have.

Sheet1
NAME COST Difference(desired new column)
Project1 129
Project2 155
Project3 300
Project4 240
Sheet2
NAME DATE COST
Project2 12/20 155
Project4 12/30 310
Project3 12/18 320

I'd like to see a -20 in the difference column for Project3.

Thanks,

Colin

Compare values on sheet 1 to values on sheet2
 

In Column C (row 1) of Sheet1

=IF(ISNA(VLOOKUP(A1,Sheet2!A:C,3,0)),"No match",B1-VLOOKUP(A1,Sheet2!A:C,3,0))

Change starting row to suit and copy down until end of Column A data reached.

HTH

"Colin" wrote:

I would like to compare data in Column B (sheet1) with data that is in
ColumnC (sheet2). The vlookup needs to reference the Primary Key in Column
A (sheet2) to the Primary Key in Column A (sheet2) and then compare the in
the above referenced columns in the same row. I'd like to know if the value
is the same or what the difference in the value is.

Sheet 2 doesn't have all the Keys from sheet 1 and they may be in a
different order, so I know I need a lookup to find key and then compare
value, but I don't know how to. Here's an example of the data I have.

Sheet1
NAME COST Difference(desired new column)
Project1 129
Project2 155
Project3 300
Project4 240
Sheet2
NAME DATE COST
Project2 12/20 155
Project4 12/30 310
Project3 12/18 320

I'd like to see a -20 in the difference column for Project3.

Thanks,

Colin