List Skipped Numbers in Sequence – Excel & Google Sheets

This tutorial will demonstrate how to list missing numbers in a range.

List Skipped Numbers in Sequence

To find missing numbers in a list, we can use the following formula:

=SMALL(IF(COUNTIF($B$3:$B$8,ROW($1:$6))=0,ROW($1:$6),""),ROW(B1))

Notes:

• This is an array formula! When using Excel 2019 and earlier, you must enter the formula by pressing CTRL + SHIFT + ENTER. You’ll know the formula was entered properly when you see curly brackets around the formula.
• This formula only works with positive whole numbers (integers).

How does the formula work?

First, the ROW Function is used to generate an array of numbers:

{1;2;3;4;5;6}

In fact, you could even enter in this array of numbers manually:

=SMALL(IF(COUNTIF($B$3:$B$8,{1;2;3;4;5;6})=0,{1;2;3;4;5;6},""),ROW(B1))

Next, The COUNTIF Function will count the values “if” they match a value found in the array.

To visualize this, first we will convert the range $B$3:$B$8 to it’s array of values {3;2;0;6;0;5}:

=SMALL(IF(COUNTIF({3;2;0;6;0;5},{1;2;3;4;5;6})=0,{1;2;3;4;5;6},""),ROW(B1))

Next we will perform the COUNTIF operation:

=SMALL(IF({0;1;1;0;1;1}=0,{1;2;3;4;5;6},""),ROW(B1))

If a value is not found 0 is outputted. The IF Function checks if the value is NOT Found and if so, returns the value from the array, otherwise it outputs blank:

=SMALL({1;"";"";4;"";""},ROW(B1))

Last, the SMALL Function returns the smallest missing value in the selected cell.

=SMALL({1;"";"";4;"";""},{1})

={1}

Tip:

To avoid the #NUM! error, wrap your formula with the IFERROR Function:

=IFERROR(SMALL(IF(COUNTIF(B3:B8,ROW(1:6))=0,ROW(1:6),""),ROW(B1)),"")

List Skipped Numbers in Sequence in Google Sheets

All the examples explained above work the same in Google sheets as they do in Excel.