## Compare and filter values between two lists in excel

### Question:

Hi,

I have seen a lot of examples related to unique list ... mais not found what I need.

my problem is the following :

I have 2 lists of items in col A and B

not necessarely sorted

ex :

list1 list2

A D

E A

D G

Z Z

F I

C

I need to make a list of items that have dispareared when I compare List2 to list1 and, a list of items thta have appeared in List2 compared to list1

The result expected are :

1. disapearing (in sorted order if possible)

E

F

2. appearing (in sorted order if possible)

C

G

I

### Answer:

Values created in column D have no duplicates. They are sorted from A to Z.

### Array Formula in D2:

Copy cell D2 and paste it down as far as needed.

### Array Formula in D9:

Copy cell D9 and paste it down as far as needed.

### Blog posts you may find interesting:

How to find common values from two lists

How to remove common values between two columns

Filter common values between two ranges using array formula in excel

Filter values existing in range 1 but not in range 2 using array formula in excel

Highlight common values in two lists using conditional formatting in excel

**Download excel sample file for this tutorial.**

**Compare and filter values in two lists.xls
(Excel 97-2003 Workbook *.xls)**

**Functions in this article**

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SMALL(**array, k**)**

Returns the k-th smallest number in this data set.

**ROW(**reference**)**

Returns the row number of a reference

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**IF(**logical_test;[value_if:true];[value_if_false]**)**

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNTIF(**range;criteria**)**

Counts the number of cells within a range that meet the given condition

### Category: Compare

How to find common values from two lists

Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), […]Comments(13) Filed in category: Compare, Excel

Compare two lists of data: Filter common row records in excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]Comments(7) Filed in category: Compare, Excel, Records

Compare pricelists in excel 2007

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]Comments(7) Filed in category: Compare, Excel

Comments(5) Filed in category: Compare, Excel, Unique distinct values

Compare two tables in excel: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]Comments(5) Filed in category: Compare, Records

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]Comments(5) Filed in category: Compare, Excel

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]Comments(4) Filed in category: Compare, Excel, Missing values

Filter common values from three columns in excel

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]Comments(4) Filed in category: Compare, Excel

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]Comments(3) Filed in category: Compare, Excel

### 4 Responses to “Compare and filter values between two lists in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar here's another variation to this, a much smaller one I guess.

Values in List 1 and not in List 2

=INDEX(List_1, SMALL(IF(ISERROR(1/COUNTIF(List_2, List_1)), ROW(List_1)-MIN(ROW(List_1))+1), ROWS($A$2:A2)))

Chrisham,

Your array formula is great! My array formula creates unique distinct values, that is perhaps not what he asked for.

Thanks a lot for your contribution!!

Guys,

Realy appreciate your contribution.

I need the unique distinct value.

I'll keep both proposed solution. Always interresting to test and compare :-).

Again, thanks a lot.

I have been looking for something similar to this. I am trying to automate a billing process and need some help with it.