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 values in a column using excel formula
2. Extract largest values from two columns using array formula in excel
3. Identify missing three character alpha code numbers in excel
4. Comparing two columns and sum unique values using array formula in excel
5. Merge two columns into one list in excel
6. Extract a list of duplicates from two columns combined using array formula in excel
7. Merge three columns into one list in excel
8. Extract a unique distinct list from two columns using excel 2007 array formula
9. Extract a list of duplicates from three columns combined using array formula in excel
10. Filter duplicates from two columns combined and sort from A to Z using array formula in excel