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.

**Table of Contents**hide

## 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,

`=EXACT($B$4:$B$15,'Sales-Jan'!$B$4:$B$15)`

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,

`=ISNUMBER(MATCH(B4,'Sales-Jan'!$B$4:$B$15,0))`

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,

`=VLOOKUP(B4,'Sales-Jan'!$B$4:$C$15,2,FALSE)`

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,

`=IF(ISNA(VLOOKUP(B4,'Sales-Jan'!$B$4:$C$15,2,FALSE)),"NO","YES")`

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