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
Get FREE Advanced Excel Exercises with Solutions!
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 Sheet1 like below:
2. Now, go to the Sheet2 and insert the following formula in cell C5:
=IFERROR(VLOOKUP(Sheet1!$B$5,B5:B12,1,FALSE),"Not Found")3. Now, simply copy this formula down using Fill Handle and this should tell you whether the product in cell B5 inside Sheet1 exists in the Product List column or not.