We help IT Professionals succeed at work.

compare data in 2 different excel 2010 worksheets

contrain asked
In XL 2010, I have 2 worksheets, one has thousands of rows of data (Sheet A) and the other has hundreds of rows of data (Sheet B). Some of the data in sheet B is in Sheet A. How can I find out which data in sheet B is also in Sheet A?

Watch Question

I suggest VLOOKUP.  Say what you're looking for is in column A of both SheetA and SheetB.

In column B of SheetB, enter =IFERROR(VLOOKUP(A1,'SheetA'!$A$1:$A10000$,1,FALSE),"")

Drag down to all rows in Sheet B.  This will reveal the data if it's a duplicate, and blanks if the data is not found in SheetA.


Hi Katie,
When I paste that function in an xl sheet on sheet b cell B1, it just puts in the text of the function, it doesn't return any results. I also noticed that in your range referring to Sheet A, it says $A10000$, should there be a $ at the end of the range reference?

Sorry, that should read, "$A$10000" (or whatever range you want).

To correct what appears in the cell, check the formatting and make sure it's not Text formatting.


Except for the tupo which the Expert corrected in a later email, this solution worked perfectly. I was able to paste the correct function in a cell and it returned what I was looking for.