Comparing two columns in excel is on of the most basic and important step of data analysis. There could be two cases while we are comparing the data points in a column, as you can see in table of content below –
To find the columns which have same value in a row
To check if both the columns have same value in a row, we can apply a simple “=” to check. If the values are same, it will return “TRUE” else it will return “FALSE”.
Suppose we are comparing column A and B in Row 1, the exact syntax would be –
=A1=B1
For example, view the video below –
To find the values of one column which are also present in the other column
If you want to compare two columns and check if values in one column exists in the another column and vice versa you can check with following ways:
- Using MATCH formula
MATCH formula is used to determine if a particular value is present in an array. It returns the row number in which the value is present. The exact syntax is as given below –
=MATCH(lookup value, lookup array, [match type])
- Lookup value- the value you want to look up
- Lookup array- The array where you want to find
- Match Type- Exact or Approximate (0 is used for exact match)
To compare the two columns using MATCH formula, we will select a particular value in Column 1 as ‘Lookup Value’ and find out whether it exists in Column 2, which will be selected as ‘Lookup Array’ in this case.
This would return us with the Row number in the Column 2 where the value exists. If it doesn’t exist, then it would return an error.
For example, view the video below –
- Using Conditional Formatting
You can also use conditional formatting to determine if the two columns have same values or not as demonstrated in the video below.
