1

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).

sheet1

In Sheet2 column B is Date with the same format of Sheet1, and column C is "Time" with the same format of Sheet1.

sheet2

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.

  • Can you provide a screenshot or more data. To compare 2 columns on one sheet with two columns on another sheet =if(and(sheet1!l1=sheet2!b1,sheet1!m1=sheet2!c1),"Matched","Did not Match") is a prototype for a solution – bvaughn Aug 9 '16 at 14:36
  • here you can see a screenshot of two sheets. filedropper.com/newmicrosoftworddocument – Fort Knox Aug 9 '16 at 14:50
  • I dont want to say matched or did not matched, i want to say if matched then bring the value from column D of sheet2 to column O in sheet1. – Fort Knox Aug 9 '16 at 15:22
  • " but it doesn't work." - what you've tried, and how exactly it doesn't work (error message, wrong results. ..)? – Máté Juhász Aug 9 '16 at 19:14
0

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.

Excel sheet 1 Excel Sheet 2

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:

=IF(A2=Sheet2!A2,IF(B2=Sheet2!B2,Sheet2!C2,"Time doesn't match"),"Date doesn't match")

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.

  • Thank you so much for your answer and consideration. I was useful. – Fort Knox Aug 10 '16 at 21:32
  • If this answer was your solution, please mark it as the solution to help others know. (You and the answerer will also get rep points) – juniorRubyist Jan 5 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.