## Identify missing numbers in two columns based on a numerical range

**Question:**

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

**Answer:**

### Array formula in cell B5:

**How to create an array formula**

- Select cell B5
- Click in formula bar

- Copy and paste array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys

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

**Named ranges**

List1 (A2:A6)

List2 (E4:E6)

missing_list_start (B5)

What is named ranges?

### Explaining array formula in cell B5

=SMALL(IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))

**Step 1 - Create dynamic array with numbers from start to end**

=SMALL(IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, **ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))**))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))

ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))

becomes

ROW(INDEX($A:$A, 1):INDEX($A:$A, 10))

becomes

ROW($A$1:INDEX($A:$A, 10))

becomes

ROW($A$1:$A$10)

and returns this array:

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

* Step *2 - Find missing numbers

=SMALL(IF(**(COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))**=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))

(COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))

becomes

(COUNTIF(List1, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10})+COUNTIF(List2, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}))

becomes

(COUNTIF({1;3;4;7;8}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10})+COUNTIF({1;2;5}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}))

{0;1;1;0;0;1;1;0;0} + {1;0;0;1;0;0;0;0;0}

and returns

{1;1;1;1;0;1;1;0;0}

* Step *3 - Convert boolean array into missing numbers

=SMALL(**IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), "")**, ROW(A1)))

IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), "")

becomes

IF(({1;1;1;1;0;1;1;0;0}, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), "")

becomes

IF(({1;1;1;1;0;1;1;0;0}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, "")

and returns

{"";"";"";"";6;"";"";9;10}

* Step *4 - Return the k-th smallest number

=SMALL(IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))

becomes

=SMALL({"";"";"";"";6;"";"";9;10}, ROW(A1))

becomes

=SMALL({"";"";"";"";6;"";"";9;10}, 1)

returns the number 6 in cell B5.

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

Identify missing numbers in a column

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

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

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Find missing dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the date ranges, in other words, dates that […]

Insert blank rows for missing values

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]

Identify missing three character alpha code numbers

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

Highlight missing values between to columns

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

### 4 Responses to “Identify missing numbers in two columns based on a numerical range”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

This is great. I need to do exactly the same thing, but with three character alpha codes instead of numbers. Can anyone help?

I tried to answer your question in this blog post: https://www.get-digital-help.com/2009/09/04/identify-missing-three-character-alpha-code-numbers-in-excel/

Please help me out in Basic of Excel and VB formuals

i need to learn that including Array,which u have done in the top, how to start it and basic

rakesh,

I have added better array formula instructions to this post.