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.

## Download Practice Workbook

You can download the practice workbook from here:

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

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 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.

**Read More:** **How to Use VLOOKUP Function to Compare Two Lists in Excel**

**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 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**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 find 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,**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**.

**Read More:** **Excel Formula to Compare and Return Value from Two Columns**

**Similar Readings**

**How to Compare Text of Two Cells in Excel (10 Methods)****Excel Compare Text in Two Columns (7 Fruitful Ways)****How to Count Matches in Two Columns in Excel (5 Easy Ways)****Excel formula to compare two columns and return a value (5 examples)****How to Compare Two Columns for Finding Differences in Excel**

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

In the previous example, you have got 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 functions**will 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,
**ISNA function**will take only the cells which is assigned**#N/A**by**VLOOKUP**functions which means the**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.

**Read More:** **How to Compare Two Columns or Lists in Excel (4 Suitable Ways)**

### 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 which match the list in
**TeamA,**and, for the**mismatched**names, there showing**#N/A error.**

**Read More:** **How to Match Two Columns and Return a Third 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.

**Read More:** **How to Compare Multiple Columns Using VLOOKUP in Excel (5 Methods)**

## 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. You can visit our website** ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)****How to Match Multiple Columns in Excel (Easiest 5 ways)****Excel Compare Two Lists and Return Differences (4 Ways)****Match Two Columns and Output a Third in Excel (3 Quick Methods)****How to Compare Two Columns and Return Common Values in Excel****Macro to Compare Two Columns in Excel and Highlight Differences****How to Compare Two Columns in Excel for Match (8 ways)**

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.