What values are missing in List 1 that exists i List 2?

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the two columns.

1. Extract values that exist only in one of two nonadjacent columns

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}

Back to top

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

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

returns {TRUE, 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), "")

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

returns II in cell C2.

Back to top

2. Extract values that exist only in one of two nonadjacent columns - Excel 365

The image above demonstrates an Excel 365 formula much smaller than the above, it also extracts missing values between two nonadjacent columns.

Formula in cell F3:

2.1 Explaining formula

Step 1 - Count cells equal to any of the conditions

The COUNTIF function calculates the number of cells that meet a given condition.
COUNTIF(range, criteria)

COUNTIF(D3:D6, B3:B18)

returns {0; 0; ... ; 0}

Step 2 - Check if value in the array is equal to 0 (zero)

The equal sign lets you check if a value is equal to another value. The result is a boolean value, TRUE or FALSE.

COUNTIF(D3:D6,B3:B18)=0

returns {TRUE; TRUE; ... ; TRUE}

Step 3 - Filter values based on boolean values

FILTER(B3:B18,COUNTIF(D3:D6,B3:B18)=0)

returns {"II"; "KK"; ... ; "JJ"}.

Back to top

Get the Excel file

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

Back to top

3. Identify missing numbers in a range

Array formula in cell B8:

If you have numbers between 5 and 32, change ROW($1:$9) to ROW($5:$32)

The formula won´t work if you have numbers above 1048576 (excel 2007). See this post: Identify missing values in a column using excel formula

How to create an array formula

1. Copy above array formula
2. Select cell B8
3. Press with left mouse button on in formula bar
4. Paste array formula in formula bar
5. Press and hold Ctrl + Shift
6. Press Enter

Explaining formula in cell B8

Step 1 - Check if numbers in range equal numbers in list

The COUNTIF function counts values based on a condition, in this case, multiple conditions.  The first argument is the cell range containing the numbers, the second argument is a given list of numbers.

The ROW function returns the row number of a cell, in this case a cell range. The ROW function then returns an array of row numbers of all cells in the cell range.

COUNTIF($B$3:$D$5, ROW($1:$9)

returns {1;0;2;1;1;1;1;2;0}.

Step 2 - Which numbers are missing?

A 0 (zero) indicates a number is missing, compare the value with 0 (zero) and we get either TRUE or FALSE.

COUNTIF($B$3:$D$5,ROW($1:$9))=0

returns {FALSE; ... ; TRUE}.

Step 3 - Replace TRUE with corresponding number

IF(COUNTIF($B$3:$D$5, ROW($1:$9))=0, ROW($1:$9), "")

returns {"";2;"";"";"";"";"";"";9}.

Step 4 - Extract a new number in a cell each

The SMALL function extracts the k-th smallest number from a cell range or array. SMALL( cell_ref, k)

The second argument contains the ROWS function and an expanding cell range. When you copy the cell and paste to cells below the cell reference grows. This makes sure that a new numb er is extracted in each cell.

SMALL(IF(COUNTIF($B$3:$D$5, ROW($1:$9))=0, ROW($1:$9), ""), ROWS(B8:$B$8)

becomes SMALL({"";2;"";"";"";"";"";"";9}, 1) and returns 2 in cell B8.

Get Excel *.xls file

Missing numbers in a range.xls

Compare category

Extract shared values between two columns
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

How to highlight differences and common values in lists
This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]

Compare two columns and extract differences
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]

Missing values category

Find missing numbers
Table of Contents Find missing numbers in a column based on a given range Find missing three character alpha code […]

Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

Excel categories

Comments (13)