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

Extract shared values between two columns

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 separate columns

Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to [โฆ]

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 [โฆ]

Automate Excel: Update list with new values

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price [โฆ]

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in [โฆ]

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 return differences

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) [โฆ]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going [โฆ]

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

Paste image link to your comment.

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