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

1. Select cell c2
2. Press with left mouse button on in formula bar
3. Paste above array formula to formula bar
   
4. Press and hold Ctrl + Shift
5. Press Enter
6. 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.

Get Excel *.xlsx file

values-missing-in-list-1-that-exists-i-list-2.xlsx

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]

Create a Print button [VBA]

This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Copy Excel Table filter criteria programmatically

I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]

Reverse a list ignoring blanks

This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]

Extract numbers from a column

I this article I will show you how to get numerical values from a cell range manually and using an […]