How to Find Matching Values in Two Worksheets in Excel

While working with a large dataset or multiple worksheets in Excel, there is a possibility that you are getting the same matching values in both of your worksheets. Sometimes we may need to find those matching values to get a clear concept about the worksheet. Excel provides some basic functions and formulas by which you can easily find matching values in two worksheets. Today, in this article, we will learn how to find matching values in two worksheets in Excel.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

5 Suitable Methods to Find Matching Values in Two Worksheets

In the following, I have described 5 Suitable methods to find matching values in two worksheets.  Follow the instructions below.

1. Use EXACT Function to Find Matching Values in Two Worksheets

The EXACT function goes through the rows and columns in two different worksheets and finds matching values in the Excel cells. Follow these steps below to learn!

In the following example, we are given two different datasets in two different worksheets. The dataset contains the columns named “Unique ID”, “Name”, and “Salary” of some sales reps. Now our job is to find those matching values that are present in those worksheet datasets. For the “Sales-Jan” worksheet the dataset is,

And the next dataset is,

In the “Matching ID” column, we will find out those matching values that are present in the worksheets.

Steps:

• In cell F5, apply the EXACT function. The general argument of the function is,

• Now insert the values into the function and the final form is,

Where,

• Text1 is $B$5:$B$16 as we want to find the matching IDs between two worksheets.
• Text2 is ‘Sales-Jan’!$B$5:$B$16 which is the Unique ID column in the Sales-Jan

• Now press ENTER to get the result.

So we can see that the EXACT function is returning FALSE when the value is not matched and TRUE for those values which are matched. That’s how you can find matching values in two worksheets.

2. Combine MATCH with ISNUMBER Function to Get Matching Values

The combo of the MATCH and ISNUMBER functions also gives you the matching values in two worksheets.

Steps:

In cell F5, apply the MATCH with the ISNUMBER formula. After inserting the values into the formula, the final form is,

Where,

• Lookup_values is B5
• Lookup_array is ‘Sales-Jan’!$B$5:$B$16. Click on the Sales-Janworksheet to go there and select the array.
• [match_type]is EXACT (0).

• Now press ENTER to apply the formula.

• The formula will give you “TRUE” if the values are matched. And will return “FALSE” if the values are not matched.
• Apply the same formula for the rest of the cells to get the final result.

3. Insert VLOOKUP Function to Find Matching Values in Two Worksheets

The VLOOKUP function takes the input value, searches it in the worksheets, and returns the value matching the input. Let’s follow these steps to learn!

Steps:

Apply the VLOOKUP function in the cell where you want to get the matching values. Insert the values into the function and the final formula is,

Where,

• Lookup_value is B5.
• Table_array is ‘Sales-Jan’!$B$5:$C$16. Go to the Sales-Jan worksheet and select the table array.

• Col_index_num is 2. We want to get the matching names with the matching IDs
• [range_lookup]value is FALSE (Exact)

• Press ENTER to get the result.

So we have found the first matching values. Apply the same function to the rest of the cells to get the final result. When the VLOOKUP won’t find the matching values, it will return the #N/A error.

4. Merge IF with ISNA to Obtain Matches from Two Worksheets

Another formula that can help you to compare the two datasets and identify if the values exist in both worksheets is the IF with the ISNA function.

Steps:

In the F5 cell, apply the combo IF with the ISNA function. After inputting the values the final form is,

Where,

• Lookup_value is B5
• Table_array is ‘Sales-Jan’!$B$5:$C$16.
• Col_index_num is 2.
• [range_lookup]value is FALSE (Exact)
• If the values are matched, the formula will return YES. Otherwise, it will return NO

• Apply the function by pressing ENTER.

• Now apply the same formula to the rest of the cells to get the final result.

5. Use Conditional Formatting to Find Matching Values in Two Worksheets

If you want you can also use the conditional formatting feature to find matching values in two worksheets. Follow the instructions below-

Steps:

• First, choose cells (B5:C16) and select “New Rule” from the “Conditional Formatting” feature.

• In the “New Formatting Rule” window choose “Use a formula to determine which cells to format”.
• Then, write the below formula down-

• Hence, click “Format”.

• Thereafter, choose a filling color and hit OK.

• Next, coming back to the previous window click OK to finish.

• Finally, we have successfully found our matching values in two worksheets using conditional formatting. Simple isn’t it?

Things to Remember

��� The EXACT function is case-sensitive. It won’t see Alexander and alexander as being a match.

��� The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function Never searches for the data on the left.

��� When you select your Table_Array you have to use the absolute cell references ($) to block the array.

Conclusion

Today we learned four different methods to find matching values in two worksheets in Excel. We hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box.

Similar Articles for You to Explore

• How to Highlight Duplicate Rows in Excel (3 Ways)
• Find, Highlight & Remove Duplicates in Excel
• How to Find & Remove Duplicate Rows in Excel
• Excel Formula to Find Duplicates in One Column
• Find Matches or Duplicate Values in Excel (8 Ways)
• Excel Top 10 List with Duplicates
• Excel Find Similar Text in Two Columns
• How to Compare Rows in Excel for Duplicates
• Find Duplicates in Two Columns in Excel (6 Suitable Approaches)
• Finding out the number of duplicate rows using COUNTIF formula