compare two columns in excel for differences

Compare Two Columns In Excel For Differences Download Working File

Excel IF Function. Here is the syntax of ISNA function: ISNA(value) And this is how ISNA function works.

In this article, I will show how you can use VLOOKUP formula to compare two columns in different sheets.

In the SSA worksheet, you will find the 100 most popular given names for male babies born during the last 100 years, from the year 1918 to 2017 ( source ).

And in the Mongabay worksheet, you will find the 100 most common male names in the U.S.

We shall compare these two worksheets using the Excel VLOOKUP formula.

Lets compare the SSA worksheet names with the names of Mongabay worksheet.

Compare Two Columns In Excel For Differences Download Working File

Table of Contents Using VLOOKUP Formula to Compare Two Columns in Different Worksheets IFERROR and VLOOKUP combo to treat the NA error How does this formula work IF, ISNA, and VLOOKUP combo to Handle the NA error How does this formula work Download Working File Related Readings Using VLOOKUP Formula to Compare Two Columns in Different Worksheets 1) Add a new column ( Comparing with Mongabay ) after the Rank column in the SSA worksheet.

And then input this formula in cell D2: VLOOKUP(A2, mongabaydata, 1, FALSE) mongabaydata MONGABAYA2:C101 Input this formula in D2: VLOOKUP(A2, mongabaydata, 1, FALSE) 2) Press Enter.

James is showing in the cell because James name is found in the Mongabay worksheet.

Now double-click on the Fill Handle icon on the bottom left corner of the cell D2.

Double click on the Fill Handle to fill all the cells with the formula of cell D2.

The formula of cell D2 will be copied down to all the cells in the column.

So, these names (Alexander, Tyler, Nathan, Zachary, Kyle) are losing their zeal in recent times.

This is a very straightforward VLOOKUP formula.

If you need a revision of this function, pass some time on the following image.

IFERROR and VLOOKUP combo to treat the NA error This time, lets use this formula in cell D2 and then copy it to other cells of the column.

D2IFERROR(VLOOKUP(A2,mongabaydata,1,FALSE),--Not Found) Use this formula in cell D2: IFERROR(VLOOKUP(A2,mongabaydata,1,FALSE),--Not Found) You see, this time cell D32 is not showing the error value.

How does this formula work To understand this formula, you have to be familiarized with the IFERROR excel function.

The syntax of IFERROR function: IFERROR(value, valueiferror) Decoding the formula.

Lets see how the above formula works As the value of IFERROR function, we have input our VLOOKUP formula.

So, if there is no error, the output of the VLOOKUP formula will be the output of the IFERROR function.

As the valueiferror argument, we have passed this value, - -Not Found.

So, if IFERROR function finds an error in the cell, it will output this text, - -Not Found.

IF, ISNA, and VLOOKUP combo to Handle the NA error We can also use IF, ISNA and VLOOKUP combo to handle that NA errors.

This time in cell D2, build this formula: IF(ISNA(VLOOKUP(A2,mongabaydata,1,FALSE)),Not Matched, Matched) And then use this formula for other cells in the column.

Use this formula in cell D2: IF(ISNA(VLOOKUP(A2,mongabaydata,1,FALSE)),Not Matched, Matched) How does this formula work To understand this formula, you have to know how IF and ISNA functions work.

Here is the syntax of IF function: IF(logicaltest, valueiftrue, valueiffalse) Take a look at the following image to see how the IF function works.

Excel IF Function.

Here is the syntax of ISNA function: ISNA(value) And this is how ISNA function works.