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.

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

How to create an array formula

  1. Select cell c2
  2. Click in formula bar
  3. Paste above array formula in 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

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.