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.

Improve this question
4
  • 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
    Commented Aug 9, 2016 at 14:36
  • here you can see a screenshot of two sheets. filedropper.com/newmicrosoftworddocument
    – Fort Knox
    Commented Aug 9, 2016 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
    Commented Aug 9, 2016 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
    Commented Aug 9, 2016 at 19:14

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • Thank you so much for your answer and consideration. I was useful.
    – Fort Knox
    Commented Aug 10, 2016 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)
    – Joseph
    Commented Jan 5, 2019 at 20:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .