Question: I want to find missing numbers in two ranges combined? They are not adjacent.

Answer:

Array formula in B5:B15:

=SMALL(IF(COUNTIF(List1, ROW(INDIRECT(C1&":"&C2)))+COUNTIF(List2, ROW(INDIRECT(C1&":"&C2)))=0, ROW(INDIRECT(C1&":"&C2)), ""), ROW()-ROW(missing_list_start)+1) + CTRL + SHIFT + ENTER

To remove #NUM errors, Excel 2007 users can use this formula:

=IFERROR(SMALL(IF(COUNTIF(List1, ROW(INDIRECT(C1&":"&C2)))+COUNTIF(List2, ROW(INDIRECT(C1&":"&C2)))=0, ROW(INDIRECT(C1&":"&C2)), ""), ROW()-ROW(missing_list_start)+1), "") + CTRL + SHIFT + ENTER

Named ranges
List1 (A2:A6)
List2 (E4:E6)
missing_list_start (B5)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named ranges.

Download excel sample file for this tutorial.
missing-values-in-two-columns.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

ROW(reference) returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

Related posts:

1. Identify missing three character alpha code numbers in excel
2. Merge two columns with possible blank cells in excel (formula)