## 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 3

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

- Copy above array formula
- Select cell B8
- Press with left mouse button on in formula bar
- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- 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)

becomes

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

and 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

becomes

{1;0;2;1;1;1;1;2;0}=0

and returns

{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}.

#### Step 3 - Replace TRUE with corresponding number

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

becomes

IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}, ROW($1:$9), "")

becomes

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

and 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}, ROWS(B8:$B$8)

becomes

SMALL({"";2;"";"";"";"";"";"";9}, 1)

and returns 2 in cell B8.

**Get Excel *.xls** file

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

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

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

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

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

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

### 5 Responses to “Identify missing numbers in a 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

I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0" These alternate across 1800 colunms of data.

My question: how do I count the number of groupings of each? In other words, across those 1800 columns, how many arrays of value 1 and how many arrays of value 0 do I have?

Thanks.

Joe

Joe,

see this post:

Count the number of groupings of each value

thanks

Is there any difference to apply this formula in only one column (F2:F1093)? I´m trying to identify missing numbers in a list but I´m making something wrong.

Felipe,

Did you enter it as an array formula?