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

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

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Filter common values from three columns

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, […]

Compare two tables: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

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 […]

Sean asks: How would you figure out an unique list where the sum of in one column doesn't match the […]

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 […]

Compare two columns and show differences

Array Formula in B11: =INDEX($B$3:$B$7, SMALL(IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))) To enter an array formula press and hold CTRL […]

Filter values in common between two cell ranges [UDF]

I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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