We have data from two teams that have some common members in two different worksheets named TeamA and TeamB. We’ll find the common names and the different names of the two teams.
Method 1 – Compare Two Columns in Different Excel Sheets and Return Common or Matched Values
- We have created a new worksheet with data of Team B.
- Create a new column E to find the common names.
- Insert the following formula into cell E5:
=VLOOKUP(B5,TeamA!B5:B20,1,FALSE)
- Drag the Fill Handle icon down.
- You will get the common names inserted in the column Team A. For the mismatched rows, you’ll get an #N/A Error.
Using IFERROR with VLOOKUP Function to Treat the #N/A Error
To avoid the showing of ‘#N/A Error” in the column, use the following formula instead:
=IFERROR(VLOOKUP(B5,TeamA!B5:B20,1,FALSE),"Not Found")Formula Breakdown:
The syntax of IFERROR function: =IFERROR(value, value_if_error)
- 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”. If the IFERROR function finds an error in the cell, it will output this text, “Not Found”.
- Here’s the updated result.
Using IF and ISNA with VLOOKUP Function to Handle the #N/A Error:
=IF(ISNA(VLOOKUP(B5,TeamA!B5:D20,1,FALSE)),"Not Matched", "Matched")
Formula Breakdown:
- ISNA returns a TRUE value if its argument is a N/A error, which is then used for the IF function.
- Here’s the result of the new formula.
- Go back to the formula that outputs the names as results.
- Click on any cell of the dataset.
- Go to the Home tab on the top ribbon.
- Click on the Sort & Filter option and select Filter.
- You will filter drop-down arrows in each header of the dataset.
- Click on the Filter arrow in the Column Team A.
- Unmark the checkbox “Not Found” and press OK.
- You will see only the common or matched names of the two teams. Mismatched names are hidden by the Filter Feature.
Method 2 – Compare Two Columns in Different Worksheets and Find Missing Values
Case 2.1 – Using the Filter Feature
- Use the VLOOKUP formula with the IFERROR function from Method 1.
- Go to the Filter option by clicking the Filter arrow in the column header of Team A.
- Unmark all the checkboxes except that saying “Not Found”.
- Press OK.
- You will see that only the mismatched names of Team B compared to Team A are shown in the dataset.
Case 2.2 – Using FILTER with VLOOKUP Function
- Insert the following formula into cell F5.
=FILTER(B5:B20, ISNA(VLOOKUP(B5:B20, TeamA!B5:B20, 1, FALSE)))Formula Breakdown:
- 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.
- The ISNA function will take only the cells that are assigned #N/A by VLOOKUP functions which means they are mismatched.
- The Filter function will insert only the cells from range B5:B20 which are mismatched and assigned #N/A.
Method 3 – Compare Two Lists in Different Worksheets and Return a Value from a Third Column+
- Change the column index number in the VLOOKUP. We 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)))
- We get the ages for the names that match the list in TeamA. For the mismatched names, we get an #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
We’re considering a situation with Item ID and Product Name of some products in a worksheet named W1 and Product Name and Price in another worksheet named W2. We’ll find the price of the product based on its ID.
- Make a new sheet and list item IDs you want to find in column B starting with B5.
- Insert this formula into cell C5 of that sheet:
=VLOOKUP(VLOOKUP(B6,'W1'!B6:C19,2,FALSE),'W2'!B6:C19,2,FALSE)- 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)
- Drag the Fill Handle icon to apply the formula to other cells of the column.
Download the Practice Workbook
<< 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.