compare two columns in excel for differences

COMPARE TWO COLUMNS IN EXCEL FOR DIFFERENCES HOW TO

Here, the ISERROR function checks whether a value is an error, and returns TRUE or FALSE. We want to know whether any value is present in a range or not. Unfortunately, this is not the ultimate result we want from this method. The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify. If we want to find data in Cell C5 in Column Name 1, then the formula will be:.the VLOOKUP function, in combination with IF & ISERROR function, finds a particular value in a range of data and returns the differences/similarity as output. VLOOKUP can also be used to find differences between cells. Fill Handle (+) is used to copy the formula for the rest of the cells. After entering the formula, you will get the matched name in a 3rd column.As a result, when we will look for C6 in the above-mentioned range, the formula returns: In our example, we have put “No Match” as an argument. The IFERROR function returns value_if_error if the expression is an error and the value of the expression itself otherwise. So, the function will look for C5 in the range B5:B11 and return:Ĭonversely, when the function will find C6 in range B5:B11, it will return a #N/A error because C6 is not present in the prescribed range.

Here, the VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.

To match first 3 characters of Cell B5 and Cell C5, here is the formula using LEFT Function:.

In our example, we will match 3 Leftmost/Rightmost characters. And similarly, the RIGHT function returns the characters from the right. The LEFT function returns the specified number of characters from the start of a text string. In those situations, LEFT or RIGHT functions can be used. For example, you may need to compare only the first or last 3 characters of the cell. Sometimes, you might need to match two cells partially. Use LEFT & RIGHT Functions to Compare Two Cells Partially

COMPARE TWO COLUMNS IN EXCEL FOR DIFFERENCES HOW TO

Read More: How to Compare Two Cells and Change Color in Excel (2 Ways)ħ.

You can also choose the color of the highlight from the drop-down by using the Custom Format option.Īnd, the final output is, all the unique values between cells are highlighted.Choose the Unique option from the drop-down.

Subsequently, the Duplicate Values dialogue box will show up.

Press the Duplicate Values option from Highlight Cell Rules.

Go to Home > Conditional Formatting from Styles group.

For example, you can find unique values between two datasets. Similar to the previous method, by using Conditional Formatting, you can compare two cells in various ways. Compare and Highlight Two Cells with Unique Data in Excel Read More: Compare Two Cells Using Conditional Formatting in Excel (3 Methods)Ħ. Conversely, differently named rows would not be highlighted. Then, click OK.įinally, If you follow the above steps correctly, all matched cells in the two columns will be highlighted.

Click the Format button, go to the Fill tab and choose the color.

Compare two columns in excel for differences