If you are searching for some special tricks to use the **VLOOKUP **Formula to compare two columns in different sheets then you have landed in the right place. There are some easy ways to use the **VLOOKUP **formula to compare two columns in different Sheets. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.

## VLOOKUP Formula to Compare Two Columns in Different Excel Sheets: 3 Suitable Examples

In this section, I will show you 3 quick and easy methods to use the **VLOOKUP **formula to compare two columns in different sheets on the Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.

Here, I have data from two teams that have some common members in two different worksheets named “TeamA” and “TeamB”. And, I will show you how you can find the common names and the different names of the two teams.

### 1. Compare Two Columns in Different Excel Sheets and Return Common/ Matched Values

First, I will show you how to use **the VLOOKUP function** to find common names or the matched values of two different lists of names in different worksheets. Follow the steps below for this:

- Here, I will try to get the common names of Team A and Team B. For this, I have created a new worksheet that already contains the data of Team B.
- Then, I created a new column to find the common names. Then, insert the following formula into cell
**E5**:

`=VLOOKUP(B5,TeamA!B5:B20,1,FALSE)`

- Now, drag the
**Fill Handle**icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts**Ctrl+C**and**Ctrl+V**to copy and paste.

- As a result, you will get the common names inserted in the column Team A and for the mismatched rows, there are showing the
**“#N/A Error”**. Here, I have highlighted the mismatched rows.

**Using IFERROR with VLOOKUP Function to Treat the #N/A Error:**

To avoid the showing of ‘**#N/A Error**” in the column, you can use **the IFERROR function** with the **VLOOKUP **function.

- For this, insert the following formula into cell
**E5:**

`=IFERROR(VLOOKUP(B5,TeamA!B5:B20,1,FALSE),"Not Found")`

**🔎 Formula Breakdown:**

To understand this formula, you must be familiar 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**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”**.

- As a result, you have got the output shown in the screenshot below. Here, you will see that, in the rows of mismatched names, there have been placed “
**Not Found**”.

**Using IF and ISNA with VLOOKUP Function to Handle the #N/A Error:**

There is another way to avoid the** #N/A Error** and that is using **IF** and **ISNA** functions with **VLOOKUP **functions.

- For this, paste the following formula into cell
**E5:**

`=IF(ISNA(VLOOKUP(B5,TeamA!B5:D20,1,FALSE)),"Not Matched", "Matched")`

**🔎 Formula Breakdown:**

Let’s now see how the following formula works.

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

- Thus, you will get the column filled with “
**Matched**” and “**Not****Matched**” values. Now you can easily identify the common names between the name lists of separate worksheets.

- You can easily, use the
**Filter Feature**to separate or show only the common names of two teams. - Now, to enable the
**Filter feature**for the dataset, click on any cell of the dataset. - Then, go to the
**Home**tab on the top ribbon. **Click**on the**Sort & Filter**option and select the**Filter**

- As a result, you will
**filter drop-down arrows**in each header of the dataset. - Now, click on the
**Filter**arrow in the Column of “Team A”. - Then, unmark the checkbox saying “
**Not Found**” and press**OK**.

- Here, you will see only the common or matched names of the two teams. And, the mismatched names are hidden by the
**Filter**Feature.

### 2. Compare Two Columns in Different Worksheets and Find Missing Values

In the previous example, you have learned how to find the common names of two different lists in different worksheets, Now, I will show you how you can find the **missing values of a list compared to another list**.

#### 2.1 Using Filter Feature

Similarly, before, you can use the Filter feature to find the missing values. After using the **VLOOKUP **with the **IFERROR **function, you have already a column that is showing “**Not** **Found**” values for the mismatched names.

- Now, go to the Filter option again by clicking the
**Filter arrow**in the column header of “Team A”. - Then, unmark all the checkboxes except that saying “
**Not Found**”. - Then, press
**OK**.

- As a result, you will see that only the mismatched names of Team B compared to Team A is shown in the dataset.

#### 2.2 Using FILTER with VLOOKUP Function

You can also use **the FILTER function** to find the mismatched values of two different lists of different worksheets. For this, insert the following formula into cell **F5.**

`=FILTER(B5:B20, ISNA(VLOOKUP(B5:B20, TeamA!B5:B20, 1, FALSE)))`

**🔎 Formula Breakdown:**

- First, the
**VLOOKUP**function will find the common names between the range**B5:B20**of the**active**worksheet and range**B5:B20**of the worksheet**TeamA**and assign**#N/A**for the mismatched**.** - Then, the
**ISNA**function will take only the cells that are assigned**#N/A**by**VLOOKUP**functions which means they are mismatched**.** - Then, the
**Filter**function will insert only the cells from range**B5:B20**which are mismatched and assigned**#N/A**.

- Thus, you will get the mismatched names of the selected two lists in a click.

### 3. Compare Two Lists in Different Worksheets and Return a Value from a Third Column

You can also get the other column values for matching cells of two lists in different worksheets.

- For this, you have to change the column index number in the
**VLOOKUP**Like here, I want to get the age of the name “James” and the age values are contained in the 4th column of the selected**VLOOKUP**range in the**TeamB**worksheet. - Insert the following formula into the cell
**E5:**

`=FILTER(B5:B20, ISNA(VLOOKUP(B5:B20, TeamA!B5:B20, 1, FALSE)))`

- Thus, you have got the ages for the names that match the list in
**TeamA,**and, for the mismatched names, there showing**#N/A error.**

**Read More: **How to Compare Three Columns and Return a Value in Excel

## VLOOKUP for Multiple Columns in Different Sheets in Excel with One Return Only

Now, I will show you how you can use the **VLOOKUP **function for multiple columns in different worksheets and get one value as a return. This is an example of Nested **VLOOKUP **function use.

Here, I’m considering a situation where you have “Item ID”, and “Product Name” of some products in a worksheet named “**W1**” and “Product Name” and “Price” in another worksheet named “**W2**”. Now you need to find out the “Price” of a specific “Product” with a specific “ID”. Let’s complete this task.

- Now, in another worksheet, you have only the Item ID and you want to get the price. For this, you have to use the nested
**VLOOKUP**functions. - Insert this formula into cell
**C5:**

`=VLOOKUP(VLOOKUP(B6,'W1'!B6:C19,2,FALSE),'W2'!B6:C19,2,FALSE)`

Where,

**Lookup_value**is**VLOOKUP(B6,’W1′!B6:C19,2,FALSE)**. This second “**VLOOKUP**” will pull the Item ID from the “**W1**”**table_array**: is ‘**W2′!B6:C19**.**Col_index_num**is**2****[range_lookup]**: we want the exact match**(FALSE**)

- Now, drag the
**Fill Handle**icon to apply this similar formula to other cells of the column. - And, you have got one return by using
**VLOOKUP**for multiple columns of different worksheets.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

In this article, you have found how to use the **VLOOKUP** Formula to compare two columns in different sheets. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

**<< Go Back to Columns | Compare | Learn Excel**

Good one.

Thanks for your feedback!

Hii

In sheet 1, column C (Owner Names) and Column D (Tenant names) are provided along with “Comments” in Column E for a city. And in sheet 2, owner names are provided in column F, and tenant names are in Column H, but comments are not provided.

Now I want, if in sheet1, any of the rows, the Owner name and tenant name exactly match with the owner’s name and tenant name in Sheet2 . then the comments should be copied from sheet 1 to sheet 2 in column O. In other words, if Column C & Column D (Sheet1) = Column F & Column H (Sheet 2), then from sheet 1 column E (Comments) should be pasted in Column O in the sheet 2.

Hope I have described my problem clearly, please help me to generate the formula for this.

Hello AVINASH! I have made a dataset as per your description and solved your problem. You can paste the following formula into the column O and the 3rd row to get the comment from sheet 1 when the criteria are met:

Try this for your dataset and let us know the outcome. Thank You!

I have selected a product in Column E of row 4 Sheet1 and I want the selected product to be searched in Column B of Product List and get the result in Column M of Sheet2

Hi Brijesh,

I have created a simplified solution below. Please follow them:

1. I am assuming that you have a

Sheet1like below:2. Now, go to the

Sheet2and insert the following formula in cellC5:`=IFERROR(VLOOKUP(Sheet1!$B$5,B5:B12,1,FALSE),"Not Found")`

3. Now, simply copy this formula down using

Fill Handleand this should tell you whether the product in cellB5insideSheet1exists in theProduct Listcolumn or not.