Find Matching Values in Two Worksheets in Excel with 5 Methods

To find matching values in two worksheets in Excel, follow the steps below:

1. Select cell E2.
2. Type this formula: =EXACT(D2:D10,’List 2′!D2:D10)
3. Press ENTER to see the comparison result.
4. Copy down the formula using Fill Handle.

This formula will compare the range D2:D10 of sheet “List 1” with that of sheet “List 2”. It returns TRUE for duplicate value and FALSE for unique value.

Throughout this article, I will be using two sheets (List 1 and List 2) with slight differences in their cell values. There is a list of passenger names, their boarding counters, flight numbers, and their cellphone numbers.

Find Matching Values in Two Worksheets in Excel Using EXACT Function

You can find the matching values of two separate worksheets by using the EXACT function in Microsoft Excel. It will return either TRUE or FALSE. I like to remind you that the EXACT function works case-sensitively.

Syntax

=EXACT(Text1, Text2)

Step-by-Step Guide

1. First, create a separate column beside the data table in one of the sheets.

I’ve inserted a column in Column E, named ‘Results’.

2. Then insert the formula in the first empty cell of the new column:

=EXACT(D2:D10,'List 2'!D2:D10)

Formula Breakdown

• The first range, D2:D10 is from the List 1 sheet.
• ‘List 2’! is the second sheet.
• D2:D10 is the range from the sheet, “List 2”.

3. Then, press ENTER to apply the formula.

4. Now double-click on the Fill Handle, the ‘+’ sign on the bottom-right corner of cell E2.

Final Result

The Fill Handle will automatically copy the formula through the whole datasheet and show the results this way:

• Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]
• How to Find Duplicates in Two Columns in Excel (7 Methods)

Find Matching Values in Two Excel Worksheets Using Conditional Formatting

Conditional Formatting is a great tool to mark data with different types of highlights. I will be using it to highlight the matches. But before using the Conditional Formatting command, I will apply the COUNTIF function to detect the identical values from two sheets.

Syntax

=COUNTIF(CELL RANGE, CRITERIA)

Step-by-Step Guide

1. Select your target cell range.

I have selected the column, Boarding Counter (Column B), and Flight No. (Column C) from the List 1 sheet. 

2. Then go to the Home tab.
3. Now hit on the Conditional Formatting drop-down list from the Styles group.
4. Select the New Rules command from the menu.

The New Formatting Rules dialog box will pop up on the sheet.

5. From the Rule Type section, choose the Use a Formula to Determine Which Cells to Format option.

6. Then write down the following formula in the Format Values Where This Formula is True bar:

=COUNTIF('List 2'!$B$2:$C$10,B2)

Formula Breakdown

• ‘List 2’! is the second sheet.
• $B$2:$C$10 is the absolute cell reference of the cell range B2:C10 of List 2.
• Absolute cell reference keeps the cells locked. Add the dollar ($) sign before each character to apply absolute cell reference.
• B2 is the first cell that is working as the criteria. Make sure you do not put a dollar ($) sign when writing the criteria.

7. Hit the FORMAT button on the right-bottom side of the box.

The Format Cells dialog box will show up.

8. Now from the Fill section, choose a Background Color.

I have chosen a light orange color.

9. Hit OK to see the changes.

Final Result

The similar cells between List 1 and List 2 are now highlighted with orange color.

Find Matching Values in Two Worksheets in Excel by Combining MATCH & ISNUMBER Functions

This method is another way to find the matching values in a TRUE-FALSE manner. But this time we will use the MATCH function and ISNUMBER function.

Syntax

=ISNUMBER(MATCH(lookup_value, lookup_array, [match_type])

The MATCH function detects the location of the identical values. The ISNUMBER function gives an output of TRUE (for matches) and FALSE (for mismatches) according to the results.

I am going to find similar texts in Column C from both sheets.

Step-by-Step Guide

1. Insert a new column beside the data table in one of the sheets.

I’ve inserted a column in Column E, named ‘Findings’.

2. Then inside the E2 cell, write down this formula:

=ISNUMBER(MATCH(C2,'List 2'!C2:C10,0))

Formula Breakdown

• C2 is the lookup_value. It is the value of the first cell of my targeted column.
• ‘List 2’! C2:C10 is the lookup_array that selects the C2 to C10 range from the List 2 sheet.
• 0 defines the difference between lookup_value and lookup_array.

3. Press ENTER to see the results.

4. Now bring the cursor to the bottom-right corner of cell E2 and double-click on the Fill Handle icon.

Final Result

The TRUE-FALSE result approaches the presence and absence of similarity in these two sheets. TRUE means, the values of the two sheets match, and FALSE means the values are unique. 

• How to Find Similar Text in Two Columns in Excel (5 Ways)
• 5+ Formulas to Find Duplicates in One Column in Excel
• How to Compare Rows in Excel for Duplicates (7 Ways)

Applying VLOOKUP Function to Find Matching Values in Two Worksheets in Excel

The VLOOKUP function can find matching data from two sheets and extract them out of any row or column. In this method, I will bring out similar values in a new column.

You can also check out this: VLOOKUP for Duplicate Values and Return the Matches [8 Cases].

Syntax

=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

Step-by-Step Guide

1. Create a new column beside the existing columns. 

I inserted a column named ‘Matching Values’ in Column E.

2. Then copy the following formula in the top cell (E2) of that column:

=VLOOKUP(D2,'List 2'!$D$2:$D$10,1,FALSE)

Formula Breakdown

• D2 is the lookup value which is the top cell of the column Cell Number.
• $D$2:$D$10 is the table array which is the range of the column Cell Number from the List 2 worksheet.
• 1 is the column index number.
• FALSE finds the exact matches from the two worksheets.

3. Press ENTER to apply the formula.

4. Now bring the cursor to the bottom-right corner of cell E2 and double-click on the Fill Handle icon.

Final Result

The Matching Values column now exhibits similar values from Column D of both sheets. Some of the results came #N/A, which means the values of those cells didn’t match with the other sheet.

• Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]
• 4+ Methods to Filter Duplicate Values in Excel

Find Matching Values in Two Worksheets in Excel Using IF and ISNA Functions

I will be using the IF function, which compares the selected cell range. Another function, the ISNA function shows ‘TRUE’ if it finds #N/A. Otherwise, it leaves ‘FALSE’ if it finds other values.

Step-by-Step Guide

1. Create a new column beside the existing columns. 

I inserted a column named ‘Duplicate Values’ in Column E.

2. Then copy the following formula in the top cell (E2) of that column:

=IF(ISNA(VLOOKUP(B2,'List 2'!$B$2:$D$10,1,FALSE)),"NO","YES")

Formula Breakdown

• B2 is the lookup value.
• $B$2:$D$10 is the table array which is the range of the 3 columns (Boarding Counter, Flight No, and Cell Number) from the List 2 worksheet.
• 1 is the column index number.
• FALSE finds similar characters from the two worksheets.
• “NO” and “YES” are the outputs.

3. Press ENTER to apply the formula.

4. Now bring the cursor to the bottom-right corner of cell E2 and double-click on the Fill Handle icon.

Final Result

The duplicate values resulted in YES, and the different values resulted in NO.

• How to Remove Duplicates in Excel [13 + Different Methods]
• 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel

Conclusion 

From now on, I hope you won’t have a problem finding similar information in your Excel worksheet. If you have any questions or have any ideas for us, please feel free to leave a comment below. Also, don’t hesitate to reach out to us if there are any easier techniques you would like to share.

Frequently Asked Questions

How do you find matching values in two columns in different sheets in Excel?

To find matching values in two columns in different sheets in Excel:

1. In the destination sheet, select the cell where you want the result.
2. Enter =VLOOKUP(Sheet1!A1, Sheet2!A:B, 2, FALSE) to match values between Sheet1 and Sheet2.
   It assumes you want to find a value from column A in Sheet1 and return a matching value from column B in Sheet2.
3. Press ENTER to display the matched value.

How to match data in Excel from 2 worksheets using INDEX-MATCH?

To match data in Excel from two worksheets using INDEX and MATCH:

1. In Worksheet A, enter the values you want to match.
2. In an adjacent column, use this formula: =INDEX(‘Worksheet B’!$B:$B, MATCH(A2, ‘Worksheet B’!$A:$A, 0))
3. Replace ‘Worksheet B’ with the actual worksheet name and drag the formula down to find matching data.

This method helps you compare and retrieve data from two different worksheets in Excel.

How do you check if a value exists in multiple sheets in Excel?

To check if a value exists in multiple sheets in Excel:

1. Enter the value in a cell.
2. Use this formula in an adjacent cell: =IF(SUM(COUNTIF(Sheet1!A1, A1), COUNTIF(Sheet2!A1, A1), COUNTIF(Sheet3!A1, A1)) > 0, “Exists”, “Doesn’t Exist”)
3. The formula will display “Exists” if the value is found in any of the sheets; otherwise, it will show “Doesn’t Exist.” Adjust the sheet names and cell references as needed.