If you have two big columns and you want to compare them, you can use Excel’s VLOOKUP function. In this article, I will show how you can use VLOOKUP formula to compare two columns in different sheets.

We have two worksheets (**SSA** and **Mongabay**) as below.

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. (source)

Let’s compare the **SSA** worksheet names with the names of **Mongabay** worksheet.

Table of Contents

## 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, mongabay_data, 1, FALSE)**

*mongabay_data = MONGABAY!$A$2:$C$101*

2) Press Enter. You will see **James** name is showing in the cell. **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**.

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

We get the first mismatch in the **D32** cell. The formula here is showing **#N/A** error. So, the formula did not find a match for the name **Jacob**.

4) More mismatches are here. So, these names (Alexander, Tyler, Nathan, Zachary, Kyle) are losing their zeal in recent time.

This is a very straightforward VLOOKUP formula. Here is a brief of Excel VLOOKUP function. If you need a revision of this function, pass some time on the following image.

## IFERROR and VLOOKUP combo to treat the #N/A error

This time, let’s use this formula in cell D2 and then copy it to other cells of the column.

**D2=IFERROR(VLOOKUP(A2,mongabay_data,1,FALSE),"--[Not Found]")**

`"`**- -[Not Found]"**

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, value_if_error)**

Let’s 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 value_if_error 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 #N/A error

We can also use IF, ISNA and VLOOKUP combo to handle that #N/A errors.

This time in cell D2, build this formula: **=IF(ISNA(VLOOKUP(A2,mongabay_data,1,FALSE)),"Not Matched", "Matched")**

And then use this formula for other cells in the column.

### 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(logical_test, value_if_true, value_if_false)**

Take a look at the following image to see how the IF function works.

Here is the syntax of ISNA function: **=ISNA(value)**

And this is how ISNA function works.

Let’s now see how the following formula works.

- As the
**logical_test**argument of IF function, we have passed the**ISNA**function and**ISNA**function holds our**VLOOKUP**If the**VLOOKUP formula**returns a**#N/A**error,**ISNA**function will return the**TRUE**value. When the**logical_test**is true IF function will return this value:**“Not Matched”**. - If the
**VLOOKUP formula**returns a value (no error),**ISNA**function will return**FALSE**So, IF function’s**logical_test**argument will be**False**. When**logical_test**is**False**IF function will return this value:**“Matched”**.

## Download Working File

## Related Readings

- How to Sum Multiple Rows and Columns in Excel
- Excel formula to compare two columns and return a value (5 examples)
- How to divide columns in Excel (Top 8 ways)
- How to Insert Formula in Excel for Entire Column (5 Smart Ways)
- How to Compare Two Columns in Excel Using VLOOKUP

So, these are the VLOOKUP formulas that you can use to compare two columns in different Excel worksheets. Hope this helps.

Happy Excelling!

Good one.

Thanks for your feedback!