# List Skipped Numbers in Sequence – Excel & Google Sheets

Written by

Reviewed by

Download the example workbook

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