## 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 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
- Click in formula bar
- Paste above array formula in 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**

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,}

**Step 2**

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

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

**Step 3**

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

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

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

**Step 4**

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)) Sort those rownumbers and remove blanks from the array.

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

**Step 5**

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

Use the row numbers to get the cell values of those numbers that are missing in the list 2

{II,KK,HH,FF,JJ,BB,BB,NN,NN,FF,JJ,JJ}

### Download excel *.xls file

values-missing-in-list-1-that-exists-i-list-2.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

**MATCH(**lookup_value,lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**SMALL(**array, k**) **returns the k-th smallest row number in this data set.

### Category: Compare

How to find common values from two lists

Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), […]Comments(13) Filed in category: Compare, Excel

Compare pricelists in excel 2007

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]Comments(7) Filed in category: Compare, Excel

Compare two lists of data: Filter common row records in excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]Comments(7) Filed in category: Compare, Excel, Records

Compare two tables in excel: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]Comments(5) Filed in category: Compare, Records

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 […]Comments(5) Filed in category: Compare, Excel

### Category: Missing values

Identify missing values in a column using excel formula

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]Comments(25) Filed in category: Excel, Missing values

Identify missing numbers in a range in excel

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]Comments(5) Filed in category: Excel, Missing values

Identify missing values in two columns using excel formula

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]Comments(4) Filed in category: Excel, Missing values

Insert rows for missing values

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]Comments(3) Filed in category: Excel, Missing values

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

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

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?