#### mzspazchick

##### New Member
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### colbymack

##### Active Member
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

#### mzspazchick

##### New Member
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

##### MrExcel MVP
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),"")

#### mzspazchick

##### New Member
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

##### MrExcel MVP
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

No, nothing left out.

#### mzspazchick

##### New Member
Wahoo......finally got that one to work. Thank You! Thank You! Thank You!
Michelle

Replies
13
Views
365
Replies
7
Views
120
Replies
3
Views
170
Replies
3
Views
293
Replies
1
Views
101

1,195,918
Messages
6,012,297
Members
441,690
Latest member
CyberWrek

### 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.

### Which adblocker are you using?

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

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