List Skipped Numbers in Sequence – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on August 17, 2023
Download Example Workbook

Download the example workbook

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

List Skipped Numbers in Sequence Main Function

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

list skipped numbers in sequence 01

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 If Error

List Skipped Numbers in Sequence in Google Sheets

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

list skipped numbers in sequence Google Function

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List