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

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the two columns.

#### Table of Contents

## 1. Extract values that exist only in one of two nonadjacent columns

**Question:** How to filter out data from List 1 that is missing in list 2?

**Answer:** This formula is useful when comparing two lists to find out what cell values are missing.For instance, inventory comparison.

**How to create an array formula**

- Select cell c2
- Press with left mouse button on in formula bar
- Paste above array formula to formula bar

- Press and hold Ctrl + Shift
- Press Enter
- Release all keys

You know you have entered an array formula when the formula in the formula bar is surrounded by curly brackets {=*array_formula*}

### Explaining formula

**Step 1 - Compare values between A2:A17 and B2:B5**

The MATCH function looks for a specific value in a cell range or array and returns it's position in that cell range or array.

If the value does not exist in the cell range or array the MATCH function returns #N/A (error value).

MATCH($A$2:$A$17, $B$2:$B$5, 0)

checks if there are any matches. If there are none, an error will occur.

{#N/A,#N/A,#N/A, 3,#N/A,#N/A,#N/A,#N/A,#N/A, 3,#N/A,#N/A, 3, 3,#N/A,#N/A,}

The image to the right shows the array and the corresponding value in column B. It is quite obvious already now which values are missing in the List 2 and which ones thatÂ exist.

**Step 2 - Identify error values in array**

If a value in array is an error the ISERROR function returns TRUE else FALSE.

ISERROR(MATCH($A$2:$A$17, $B$2:$B$5, 0))

becomes

ISERROR({#N/A,#N/A,#N/A, 3,#N/A,#N/A,#N/A,#N/A,#N/A, 3,#N/A,#N/A, 3, 3,#N/A,#N/A,})

and returns

{TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, }

The array now contains boolean values, TRUE or FALSE.

The IF function in the next step can't handle error values so this step is necessary.

**Step 3 - Convert boolean value TRUE to the corresponding row number**

The IF function allows you to specify a logical expression and if it evaluates to TRUE one thing happens and if FALSE another thing happens.

IF(ISERROR(MATCH($A$2:$A$17, $B$2:$B$5, 0)), (ROW($A$2:$A$17)-MIN(ROW($A$2:$A$17))+1), "")

becomes

IF({TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, }, {1;2; 3;4;5;6; 7;8;9; 10;11;12; 13;14;15; 16}, "")

and returns {1,2,3,4,"",6,7,8,9,"",11,12,"","",15,16}

If there is an error in the array, replace that error with the row number.

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

In this example one cell will display one value and in order to do that the small function returns a single row number for each cell allowing you to get a single value in each cell.

SMALL(IF(ISERROR(MATCH($A$2:$A$17, $B$2:$B$5, 0)), (ROW($A$2:$A$17)-MIN(ROW($A$2:$A$17))+1), ""), ROW(1:1))

becomes

SMALL({1,2,3,4,"",6,7,8,9,"",11,12,"","",15,16}, ROW(1:1))

becomes

SMALL({1,2,3,4,"",6,7,8,9,"",11,12,"","",15,16}, 1)

and returns 1.

**Step 5 - Use the row number to get the correct value**

The INDEX function allows you to get a value based on a row number and column number.

INDEX($A$2:$A$17, SMALL(IF(ISERROR(MATCH($A$2:$A$17, $B$2:$B$5, 0)), (ROW($A$2:$A$17)-MIN(ROW($A$2:$A$17))+1), ""), ROW(1:1)))

becomes

INDEX($A$2:$A$17, 1)

and returns II in cell C2.

## 2. Extract values that exist only in one of two nonadjacent columns - Excel 365

The image above demonstrates an Excel 365 formula much smaller than the above, it also extracts missing values between two nonadjacent columns.

Formula in cell F3:

### 2.1 Explaining formula

#### Step 1 - Count cells equal to any of the conditions

The COUNTIF function calculates the number of cells that meet a given condition.

COUNTIF(range, criteria)

COUNTIF(D3:D6, B3:B18)

becomes

COUNTIF({"DD"; "GG"; "EE"; "MM"}, {"II"; "KK"; "HH"; "EE"; "FF"; "JJ"; "BB"; "BB"; "NN"; "EE"; "NN"; "FF"; "EE"; "EE"; "JJ"; "JJ"})

and returns

{0; 0; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0; 1; 1; 0; 0}

#### Step 2 - Check if value in the array is equal to 0 (zero)

The equal sign lets you check if a value is equal to another value. The result is a boolean value, TRUE or FALSE.

COUNTIF(D3:D6,B3:B18)=0

becomes

{0; 0; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0; 1; 1; 0; 0}=0

and returns

{TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE}

#### Step 3 - Filter values based on boolean values

FILTER(B3:B18,COUNTIF(D3:D6,B3:B18)=0)

becomes

FILTER({"II"; "KK"; "HH"; "EE"; "FF"; "JJ"; "BB"; "BB"; "NN"; "EE"; "NN"; "FF"; "EE"; "EE"; "JJ"; "JJ"}, {TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE})

and returns

{"II"; "KK"; "HH"; "FF"; "JJ"; "BB"; "BB"; "NN"; "NN"; "FF"; "JJ"; "JJ"}.

### Compare category

This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

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

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

This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]

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

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

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

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

This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]

In this example we are going to use two lists with identical columns, shown in the image above. It is […]

This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]

### Missing values category

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

HughMark asks:Â I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 8 Responses to “What values are missing in List 1 that exists i List 2?”

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

How can I expand the range? my list goes til 49

Thank You

Karla,

there are two cell ranges in this formula (bolded):

$A$2:$A$17, SMALL(IF(ISERROR(MATCH($A$2:$A$17,$B$2:$B$5, 0)), (ROW($A$2:$A$17)-MIN(ROW($A$2:$A$17))+1), ""), ROW(1:1)))Change these two cell ranges:

$A$2:$A$17

$B$2:$B$5

Why is it that the formula posted on this web page, is not the same as the formula embedded in the excel document? The formula in the excel document is longer (it has multiple relative cell references, whereas the formula in the excel spreadhseet refers to absolute cell references). If I copy/paste the formula on this page, it does not return any results; it is simply a long formula. Also, when I follow steps on how to create an array formula, it says that a bracket will appear aroun dthe formula, but that did not happen in my case. Any clues appreciated!

Lisa,

Why is it that the formula posted on this web page, is not the same as the formula embedded in the excel document?I changed the array formula in the post but forgot to change the attached file. Thanks for reminding me.

The formula in the excel document is longer (it has multiple relative cell references, whereas the formula in the excel spreadhseet refers to absolute cell references)The array formula shown in this post is easier to use, you simply change the absolute cell references in the array formula.

If I copy/paste the formula on this page, it does not return any results; it is simply a long formula. Also, when I follow steps on how to create an array formula, it says that a bracket will appear around the formula, but that did not happen in my case. Any clues appreciated!Did you paste the formula to the formula bar?

Good support and help to improve my excel knowledge.

thanks of lot

Thank you!

How do I combine this function with TEXTJOIN to return all missing values in a single cell, delimited? The current formula I have, which is returning only the first missing value, is:

=arrayformula(Textjoin(" &

", true, (INDEX(Emails!$A$2:$A$36,(MATCH(TRUE,(ISNA(MATCH(Emails!$A$2:$A$36,B$5:B$203,0))),0))))))

This is in google sheets, hence the "arrayformula".

How do I combine this formula with the TEXTJOIN function to return all missing values in a single cell? The formula I currently have, which is returning only the first missing value, is:

`=arrayformula(Textjoin(" &`

", true, (INDEX(Emails!$A$2:$A$36,(MATCH(TRUE,(ISNA(MATCH(Emails!$A$2:$A$36,B$5:B$203,0))),0))))))

This is in google sheets, not excel.