Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning Values | Excel Discussion (Misc queries) | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
How to find a row in a sheet and compare the complete row /w origi | Excel Worksheet Functions | |||
how to get values from different sheet when info. matches? | Excel Worksheet Functions | |||
Copy values from Sheet1 to Sheet2 | Excel Discussion (Misc queries) |