Returning data not found on list

mzspazchick

New Member
Joined
Nov 10, 2005
Messages
39
I know this should be easy but....
Ok, I have a list of names
Sue
Sally
Paul
John
I want to compare it to a list of other names (which is a named/defined list)
Bob
Bobo
Sally
Sam

I want to compare list one to list two and return only those names that do NOT match.
Michelle
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Using a vlookup statement, a #N/A will indicate which items do not appear on one list compared to the other.
i.e. =vlookup(a1,ListB,1,false)

The result is that the formula will look at the value in cell a1, compare it to the range of cells in ListB. If found, it will return the same value. IF not found, it will return #N/A.

do this for both lists and you will find the values in list A that are not in list B, and the values in list B that are not in list A

HTH
 
Upvote 0
Is there a way to list only those that don't appear on the list? So in my example my formula would return:
Sue
Paul
John
It would leave out Sally because she is on the second list.
Michelle
 
Upvote 0
mzspazchick said:
Is there a way to list only those that don't appear on the list? So in my example my formula would return:
Sue
Paul
John
It would leave out Sally because she is on the second list.
Michelle
Book1
ABCD
103
2List1List2IdxResult List
3SueBob1Sue
4SallyBobo Paul
5PaulSally2John
6JohnSam3 
Sheet1


Formulas...

C1 must house a 0.

C3, copied down:

=IF(A3<>"",IF(ISNA(MATCH(A3,$B$3:$B$6,0)),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,""),"")

D1:

=LOOKUP(9.99999999999999E+307,C1:C6)

D3, copied down:

=IF(ROW()-ROW($D$3)+1<=$D$1,LOOKUP(ROW()-ROW($D$3)+1,$C$3:$C$6,$A$3:$A$6),"")
 
Upvote 0
For some strange reason, I only got the last guy on the list. I see it works on your sheet. Let me explore mine again, anything on your end possibly left out???
Michelle
To only have your knowledge......Amazing!!!
:biggrin:
 
Upvote 0
mzspazchick said:
For some strange reason, I only got the last guy on the list. I see it works on your sheet. Let me explore mine again, anything on your end possibly left out???
Michelle
To only have your knowledge......Amazing!!!
:biggrin:

No, nothing left out.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top