Comparing lists in Excel 2007

[robheeley]

I have two lists of numbers in separate Excel worksheets. How can I compare them so that I can identify those numbers in one list that are missing from the other and vice versa?

 

[xlbo]

use VLOOKUP or a combination of INDEX/MATCH against both lists e.g.

Against data items in list 1

=if(ISNA(vlookup(A2,List2,1,false)),"Not In List 2","")

Against data items in list 2

=if(ISNA(vlookup(A2,List1,1,false)),"Not In List 1","")

Where "List1" & "List2" are valid range references in your workbooks


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

I often do this simply using the MATCH() function without an IF(). If MATCH() returns #N/A then no match.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

I usually use CountIf. It has lower overhead than VLookup. I'm not sure how it compares to Match.

But there are definitely a lot of ways to skin this cat.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.