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))) +Ctrl + Shift + Enter copied down as far as needed.

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

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

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}

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}

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 rownumbers 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 sample file for this article.
values-missing-in-list-1-that-exists-i-list-2
(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.

Related posts:

1. Identify missing values in a column using excel formula
2. Identify missing values in two columns using excel formula
3. How to calculate missing months in a given date range in excel
4. Identify missing numbers in a range in excel
5. Validate unique distinct list in excel
6. Validate unique list in excel
7. Identify missing three character alpha code numbers in excel