How to Find Matching Values in Two Worksheets in Excel (4 Methods)

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.

4 Suitable Methods to Find Matching Values in Two Worksheets

1. Use the EXACT Function to Find Matching Values in Two Excel 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!

Step 1:

In the following example, we are given two different datasets in two different worksheets. The dataset containing 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.

Step 2:

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

=EXACT(text1,text2)

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

Where,

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

Now press Enter to get the result.

Step 3:

Move your mouse cursor to the bottom right corner of the formula cell until you get the fill handle icon (+). Now click and drag the icon to apply the same formula for the rest of the cells.

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 in Two Worksheets

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

Step 1:

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

Where,

• Lookup_values is B4
• Lookup_array is ‘Sales-Jan’!$B$4:$B$15. Click on the Sales-Jan worksheet to go there and select the array.

• [match_type] is EXACT (0).

Now press Enter to apply the formula.

Step 2:

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 the 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!

Step 1:

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 B4
• Table_array is ‘Sales-Jan’!$B$4:$C$15. 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.

Step 2:

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 Formula to Obtain Matches from Two Worksheets in Excel

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

Step 1:

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

Where,

• Lookup_value is B4
• Table_array is ‘Sales-Jan’!$B$4:$C$15.
• 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.

Step 2:

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

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