compare two columns in excel to find missing data

#Compare two columns in excel to find missing data plus#

Compare Columns using IF FunctionĪnother way we can compare two columns row by row is by using the IF function.

Also, the address in cell A5 does not match the address in cell B5 – the last character is different in cell B5. The billing address in cell A2 matches the delivery address in cell B2, as a result, we get TRUE. Then it returns TRUE if the condition is met or else returns FALSE. The formula looks for the correct match between the two columns and will not miss even a single space character. Let us see how the formula compares columns with text values. We have seen how the above formula handles numbers, but it can also compare dates, times, and text strings equally well. For example, the value from cell B4 does not match its adjacent cell C4, hence the value in cell D4 is FALSE.

#Compare two columns in excel to find missing data plus#

When you drag the fill handle from cell D2 to D12, the cursor will change into a black plus sign.Īs you apply the formula through cells D2 to D12, it will compare values row by row and you will see some rows matches while others don’t. The fill handle is a small green square in the bottom right corner of the selected cell. First, enter the formula in cell D2 and then copy it down to other cells by dragging the fill handle to compare columns B and C row by row. The value in B2 matches the value in C2, so the formula returns TRUE. To do that, we will use the below simple formula: =B2=C2 With the ‘Equals to’ (=), you can compare cells in two columns for a match and get the result as either true or false.įor example, we will compare two columns (Bill Due and Bill Paid in the screenshot below) to see if they match. The easiest way to compare two columns’ data row by row for finding the match is using the comparison operator. There are different types of formulas you can use to compare two columns row by row – using a simple comparison operator, IF function, and EXACT function.

It will only compare values in the same row, not the entire dataset. The simplest way to compare two columns in Excel is a simple row-by-row, line-by-line comparison. This method checks whether the value in one column matches the value in another column in the same row. Comparing Two Columns Row by Row for Matches or Differences In this article, we will discuss different methods to compare columns in Excel for matches and differences. You can use logical operators – VLOOKUP, MATCH, AND, INDEX, IF, COUNTIF, ISERROR, IFERROR – or Conditional Formatting rules to compare and match data. Manually comparing the data from two columns can be a time-consuming and exhausting task, so you can use various Excel formulas to match columns.Įxcel has several methods and functions for comparing columns and finding matching and mismatching data. Comparing columns is very useful for organizing and analyzing data. When you’re working with data in Excel, you may need to compare columns to find similarities and differences between data.