I am stuck for comparison of two columns in two different sheets of Excel. I used different ways and formula but they don't work.
In Sheet1 in column L I have Date by format of e.g 1/19/2012 and column M is "Time" by format of (e.g 19:00).
In Sheet2 column B is Date with the same format of Sheet1, and column C is "Time" with the same format of Sheet1.
I want to compare date and time of two sheets and check if they are matched then bring the appropriate value from Sheet2 column D to a new column O in Sheet1 .
I used the formula =L2 & "-" & M2 in Sheet 1 in column N to have a merged column and used =B2 & "-" & C2 in Sheet2 in column A to have a merged column.
Now Im trying to compare Sheet1 Column N and Sheet2 Column A and see if they are matched then bring related value from Sheet2 Column D to Sheet1 Column O but it doesn't work.
Note: Sheet1 has 1876 rows but Sheet2 has 8785 rows.
Best Answer
I have re-created your spreadsheet, with the exception of the "combined" column because it isn't necessary if you were only using it to be able to match.
From what I understood, you have 2 columns on sheet1 that you want to match against 2 columns on sheet2. If they do match, you want to copy a column from sheet2 back to sheet1. This can be done using 2 IF() statements within Excel. Note that this will only work for sequential rows. You mentioned sheet1 has 1876 rows but sheet2 has 8785 rows; this will only match on those first 1876 rows.
Here are the two worksheets I have setup. They are close to yours.
As you can see in the pictures, I have made rows 2 and 3 the same in each sheet, and then I made the date and time not match in row 4, and only the time not match in row 5.
If both items match, it takes the information from column C on sheet 2 and shows it in column C on sheet 1, which I believe is what you're asking for.
The IF formula in Excel looks like this: "IF(Test,[Value if True],[Value if False])". So what we do is first check if your dates match. If they do, then we use a second test to see if your times match. If either one fails, then we know they don't match.
Here is the formula in C2:
To break down the formula, it says: IF A2 from sheet 1 equals A2 on sheet 2 [IF(A2=Sheet2!A2], then also check IF B2 on sheet one equals B2 on sheet 2 [IF(B2=Sheet2!B2]. If they do match then put the contents of C2 from sheet 2 in to B2 [Sheet2!C2]. If they don't match at this point then put "Time doesn't match" in B2. If the initial date test hadn't matched then put "Date doesn't match" in B2.