compare two columns in excel 2010 and find matches

You can also display the unique values in the second column.

Note: take a look at the second picture on this page to see that we swapped the last 2 arguments of the IF function.Ģ. In the Name box, type CustomerReport to name. Get & Transform Data feature consists of tools previously known as. For two of our formulae, we have entered an inexact track name (‘Whole lot of love’). NAME THE RANGE On the Customer Report worksheet, click on a cell in the data. We can join these two tables to compare them by using Get & Transform Data. The COUNTIF function counts values based on a condition or criteria. Explaining formula in cell B11 Step 1 - Count values in List 1 based on values in List 1. Display the unique values in the first column (these values do not occur in the second column). Here, we have re-sorted our table and used two approximate matches, and two exact matches, all referring to columns 1 and 2. Copy cell D9 and paste it down as far as needed. For example, if the two columns you want to compare start on A2 and B2, highlight C2. Make sure you are starting on the same row as the two columns youre comparing. Compare two columns for matches or differences in the same row To compare two columns in Excel row-by-row, write a usual IF formula that compares the first two cells. When comparing two columns in a worksheet, youll be outputting your results onto a blank column. Unique Values in Each Columnĭo you want to compare two columns by displaying the unique values in each column? Simply swap the last 2 arguments of the IF function.ġ. Highlight the first cell of a blank column. At step 2, we match each value in the second column with the range in the first column. Note: at step 1, we matched each value in the first column with the range in the second column.

You can also display the duplicates in the second column. As a result, the ISERROR function returns TRUE and the IF function returns an empty string.Ģ. The MATCH function in cell C4 returns a #N/A error (no letter D in the range B1:B7). VLOOKUP is one of the lookup and reference functions in Excel and Google Sheets used to find values in a specified range by row. As a result, the ISERROR function returns FALSE and the IF function returns the value in cell A1. Display the duplicates in the first column (these values also occur in the second column).Įxplanation: the MATCH function in cell C1 returns the number 5 (letter A found at position 5 in the range B1:B7). Let's start by comparing two columns and displaying the duplicates.ġ.

Compare two columns in excel 2010 and find matches