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

*Article updated on November 26, 2013*

There are text values in column A and column B. The question is how do you compare the values in these two columns? More specifically, filtering values existing only in column A (or column B)?

The array formula in cell D2 extracts values existing only in column A, compared to column B:

The array formula in cell E2 extracts values existing only in column B, compared to column A:

**How to enter an array formula**

- Copy array formula (Ctrl + c)
- Select cell D2
- Click in formula bar
- Paste array formula (Ctrl + v)
- Press and hold CTRL + Shift
- Press Enter

The formula is now an array formula. See the curly brackets, they tell you it is an array formula. Don´t enter the curly brackets yourself, they appear if you enter it correctly, like this:

**How to copy array formula**

- Select cell D2
- Copy (Ctrl + c)
- Select cell range D3:D8
- Paste (Ctrl + v)

### Explaining array formula in cell D2

**Step 1 - Count the number of values in cell range A2:A14 using the values in cell range B2:B10**

COUNTIF($B$2:$B$10,$A$2:$A$14)

**Step 2 - Check if they are equal to 0 (zero)**

COUNTIF($B$2:$B$10, $A$2:$A$14)=0

**Step 3 - If they are equal to zero, return the corresponding relative row number**

IF(COUNTIF($B$2:$B$10, $A$2:$A$14)=0, MATCH(ROW($A$2:$A$14), ROW($A$2:$A$14)), "")

**Step 4 - Return the k-th smallest row number**

SMALL(IF(COUNTIF($B$2:$B$10, $A$2:$A$14)=0, MATCH(ROW($A$2:$A$14), ROW($A$2:$A$14)), ""), ROW(A1))

**Step 5 - Return value**

=INDEX($A$2:$A$14, SMALL(IF(COUNTIF($B$2:$B$10, $A$2:$A$14)=0, MATCH(ROW($A$2:$A$14), ROW($A$2:$A$14)), ""), ROW(A1)))

### Interesting articles

If you are looking for **comparing two cell ranges**, read this article:

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

Do you want to **compare text values in two cell ranges**, read this article:

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

I have also written an article about **comparing records between two data tables**:

Compare two lists of data: Filter records existing in only one list

### Download excel *.xlsx file

Filter values existing in column 1 but not in column 2.xlsx

### Functions in this article

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

**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 rownumber 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

**COUNTIF(**range,criteria**)**

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

### 3 Responses to “Compare values between two columns and filter values existing in only one column”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

Hi Oscar,

I started with the solution provided here for obtaining values existing only in one of two lists. I know that, for an ordered done job, one should tend use excel in a 'database-like' fashion, with columns as field and rows for data, and so I do.

Anyway, it happened that I had the necessity to have two lists of data to compare,but they spread horizontally. I also read your solutions for filtering values existing in different ranges, but since I was in a hurry,I adapted the formulas provided here, and wanted to share my solution.Here is the two alternative formulas that do the job in a 'column fashion':

Let's say we have two list to compare in ranges G1:V1 and G2:V2 respectively. In the result's range, I put the formula:

={INDEX($G$1:$V$1;;SMALL(IF(COUNTIF($G$2:$V$2;$G$1:$V$1)=0;MATCH(COLUMN($G$1:$V$1);COLUMN($G$1:$V$1));"");COLUMN(A1)))}

or, alternatively (thanks to another solution found here):

={INDEX($G$1:$V$1; SMALL(IF(ISERROR(MATCH($G$1:$V$1; $G$2:$V$2; 0)); (COLUMN($G$1:$V$1)-MIN(COLUMN($G$1:$V$1))+1); ""); COLUMN(A$1:A$65536)))} . I noticed that, if I use the same size for all three ranges (lists and results), I end up with having some zeroes padding the 2nd result range (Missing data in List 1), whether I use vertical or horizontal lists.

as you can see from the image I provide here:

https://s12.postimg.org/jp0p9y6st/Filter_values_existing_in_column_1_but_not_in_co.jpg

I am wondering how those zeroes appear ?

I uploaded the example excel file.

Bruno,

You are comparing 4 blank cells ($G$2:$V$2) with the values in cell range $G$1:$V$1. Since there are no blank cells the formula returns the blank cells. The INDEX function then returns 0.

Try this formula in cell G2:

=INDEX($G$2:$O$2, , SMALL(IF(COUNTIF($G$1:$S$1, $G$2:$O$2)=0, MATCH(COLUMN($G$2:$O$2), COLUMN($G$2:$O$2)), ""), COLUMN(A1)))

Hi.

I have excel file:

code bookname language bookcode id

1 book1 en 100

2 book2 fa 101

3 book1 ar 102

4 book3 en 103

5 book2 fa 104

6 book4 az 105

...

i have want to filter by book & language columns and when two columns are exist, value of id column equal is last row value of code column. for example:

book2 is true but book1 is not true. so id book1 = 104

thanks