VLOOKUP Formula to Compare Two Columns in Different Excel Sheets

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.

Sample dataset to use VLOOKUP Formula to Compare Two Columns in Different Excel Sheets


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)

Inserting VLOOKU formula to Compare Two Columns in Different Worksheets and Return Common / Matched Values

Draggig Fill handle

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

Showing N/A error while Comparing Two Columns in Different Worksheets and Return Common / Matched Values


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

Using IFERROR with VLOOKUP Formula to Compare Two Columns in Different Excel Sheets

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

Substituting N/A error with Not founf using IFERROR function


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")

Using IF and ISNA with VLOOKUP Formula to Compare Two Columns in Different Excel Sheets

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

VLOOKUP Formula to Compare Two Columns in Different Excel Sheets

  • 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

Applying Filter Feature

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

uncheck Not Found option to apply filter

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

Showing only the matched values of two different lists from different worksheets


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.

Mark only 'Not Found" in Filter Feature to show the mismatched values

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

showing only the mismatched values using filter feature and VLOOKUP function


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.

Using FILTER with VLOOKUP Function

  • 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)))

applying VLOOKUP formula

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

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


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 functions 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)

Applying VLOOKUP formula for Multiple Columns in Different Worksheets in Excel with Only One Return

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

VLOOKUP for Multiple Columns in Different Worksheets in Excel with Only One Return


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.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

4 Comments
  1. 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.

    • Reply
      Osman Goni Ridwan Aug 30, 2022 at 3:32 PM

      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:

      =IF(AND(F4=Sheet1!C4,Sheet2!H4=Sheet1!D4),Sheet1!E4,"")

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

Leave a reply

ExcelDemy
Logo