How to Compare Two Columns in Excel

How to Compare Two Column in one Row;

If we want to compare the content of the cells of two columns in the same row, we have different ways,

Here we are going to explain two of them.

write this formula in the cell and specify it to other cells of the column.

Here we want to compare column B and C with each other and these columns start from row number three so we should write this formula and specify it to other cells of the column.

=B3=C3

you can see, if the data in the columns were not equal we would have word “False”, and if the content were equal we have word “True” in the new column.

You can also write a different message in the cells, by using this formula like MATCH, NO MATCH;

IF(B3=C3,“MATCH”,“NO MATCH”)

Specify this formula to other cells of the column.

How to Find Duplicate Cells in the Columns;

How to Find Duplicate Cells in the Columns by “Conditional Formatting” Command;

You can also find duplicate cells in different columns by using “Conditional Formatting”.

1- Select the columns.

2- Go to the home tab and click on “Conditional Formatting”.

3- Click on “Highlight Cells Rules”.

4- Click on “Duplicate Values”.

5- In this dialogue box you can choose “Duplicate” or “Unique” items and then click on Ok.

Here because we want to highlight duplicate items we have chosen “duplicate” item.

Now you can see all duplicate items were selected.

How to Find Duplicate Cells in the Columns by Vlookup command;

You can use “Vlookup” command to find duplicate cells in two different columns.

For that you can use this formula;

=VLOOKUP(E3,$F$3:$F$11,1,0)

• E3 is the address of the first cell of the column which we want to compare it to another column.

• $F$3:$F$11 is the address of the table or column cells which we want to compare that to the first column.

We fixed this address because we do not want to change the cell’s address when specifying this formula to other cells.

• Because our table has only one column we should use number one.

When you specify this formula to other cells, you see this message “#N/A”, if you want to change it to another message Like “NOT FOUND”, you can use this formula.

=IFERROR(VLOOKUP(E3,$F$3:$F$11,1,0),“NOT FOUND”)

How to Lookup and Fetch Matching Data Points from One Column to Other Column

Sometimes you want to find some data from one table and fill the other table with those data,

For that you can use this formula;

=VLOOKUP(G3,$B$3:$D$11,3,0)

• In this formula, G3 is the first cell of the column, in the second table which we want to fill that according to the first table data.

• “B3:D11” is the range that the program should search on it and because we do not want to change this range when we specify this formula to other cells of the column, we fixed this range.

you can fix the range by selecting it in the formula and press “F4” key in the keyboard or write the range in this way in the formula manually.

$B$3:$D$11

• In the third part, you should specify the location of the column in the table

In this formula for Column “Number of download” we should use number two and for the column “How many people like it” we should use number 3.

How to Compare and Fetch Partial Matching Value from One Column to Other Column

Sometimes in the second table which we want to fill that some names are not completely matched with the first table names,

For example in this example in the second table at the first and second row you can see “#N/A” because the names do not completely match together, for instance in the first table we have “Facebook app” but in the second table, we have “Facebook” but we know that these items are same.

In this situation you can change the previous formula a little and write it like this;

VLOOKUP(G3&"*",$B$3:$D$11,2,0)

• In the first part of this formula, we have G3&"*" phrase.

It means that each phrase which starts with the content of G3 cells are acceptable.

• The second part specifies the range of searching and we fixed them too because we did not want the range changed when we specify this formula to other cells of the column.

• The third part shows that the column number in the table and in this formula

For the Column “Number of download” we should use number 2.

For the Column “How many people like it” we should use number 3.

Now you can see in the second table, the values in front of the Facebook and Apple name were changed from “#N/A” to the same value of “Apple co” and “Facebook app” in the first table.