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

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