compare data in 2 different excel 2010 worksheets

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?

Thanks,
contrainAsked:
Who is Participating?
 
Katie PierceCommented:
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.
0
 
contrainAuthor Commented:
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?

Thanks,
0
 
Katie PierceCommented:
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.
0
 
contrainAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.