You can use the following formula in Excel to find matching values in two worksheets:
=EXACT($A$2:$A$12,Sheet2!$A$2:$A$12)
This particular formula will find matching values between cells in the range A2:A12 of the current sheet and the range A2:A12 of the sheet called Sheet2.
The following example shows how to use this formula in practice.
Example: Find Matching Values in Two Worksheets in Excel
Suppose we have the following dataset in Sheet1 of Excel that contains information about sales made by various employees at some company:
And suppose we have another dataset in Sheet2 that also contains information about sales made by various employees:
Suppose we would like to find matching employee ID values between the two sheets.
We can type the following formula into cell C2 of Sheet1 to do so:
=EXACT($A$2:$A$12,Sheet2!$A$2:$A$12)
The following screenshot shows how to use this formula in practice:
The formula returns TRUE if the employee ID values match between the corresponding cells in the two worksheets, or FALSE otherwise.
For example, we can see:
- The Employee ID values match in cells A2 in the two worksheets so the formula returns TRUE.
- The Employee ID values do not match in cells A3 in the two worksheets so the formula returns FALSE.
And so on.
Note that we can also wrap an IF function around the EXACT function to return values other than TRUE and FALSE.
For example, we can type the following formula into cell C2 to instead return “Match Found” or a blank if no match is found:
=IF(EXACT($A$2:$A$12,Sheet2!$A$2:$A$12), "Match Found", "")
The following screenshot shows how to use this formula in practice:
The formula now returns “Match Found” if matching employee ID values are found in the two worksheets or a blank otherwise.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Compare Two Excel Sheets for Differences
How to Compare Two Lists in Excel Using VLOOKUP
How to Sort by Multiple Columns in Excel
