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

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