How to Compare Two Columns in Excel on Different Sheets

Comparing data across multiple sheets in Excel is a common task. This article provides a step-by-step guide on How To Compare Two Columns In Excel On Different Sheets and copy corresponding data based on matches. We’ll use a nested IF formula to achieve this.

Using Nested IF Formulas for Comparison

Let’s say you have two Excel sheets: “Sheet1” and “Sheet2.” Both sheets contain date and time columns. The goal is to check if the date and time in Sheet1 match the date and time in Sheet2. If both criteria are met, we want to copy a corresponding value from Sheet2 to Sheet1.

As shown in the examples, rows 2 and 3 have matching date and time values in both sheets. Row 4 has mismatched dates, and row 5 has mismatched times.

The formula we’ll use leverages Excel’s IF function. The basic structure of an IF formula is: IF(logical_test, value_if_true, value_if_false). We’ll nest a second IF within the first to check both conditions.

The formula to enter in cell C2 of Sheet1 is:

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

Let’s break down this formula:

• IF(A2=Sheet2!A2,...: This is the first IF statement. It checks if the date in cell A2 of Sheet1 matches the date in cell A2 of Sheet2.
• IF(B2=Sheet2!B2,Sheet2!C2,"Time doesn't match"): This is the nested IF statement, executed only if the first condition (dates matching) is true. It checks if the time in cell B2 of Sheet1 matches the time in cell B2 of Sheet2.
  • Sheet2!C2: If both date and time match, this part of the formula copies the value from cell C2 of Sheet2 into cell C2 of Sheet1.
  • "Time doesn't match": If the dates match but the times don’t, this message is displayed in cell C2 of Sheet1.
• "Date doesn't match": If the initial date comparison fails, this message is displayed in cell C2 of Sheet1.

Applying the Formula

You can then drag the fill handle (the small square at the bottom right of the cell) down to apply this formula to all the rows in Sheet1. This will automatically compare corresponding rows and copy data based on the matching criteria. Keep in mind that if Sheet1 has more rows than Sheet2, the formula will return an error for rows beyond the last row in Sheet2.

This nested IF formula provides a simple yet effective way to compare two columns in Excel across different sheets and extract corresponding information based on matching criteria. Remember to adjust sheet names and cell references to match your specific spreadsheet layout.