# List Missing Numbers in a Sequence Using New Formulas?

#### VBE313

##### Well-known Member
Is there a way to extract numbers in a sequence? For example, in the Sequence 1-10 in column A, 7 and 9 are missing. That is what my answer would be.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Dossfm0q

##### Banned User
Option

you can Expand Your Range ROW(INDIRECT("1:10")) 'ROW(INDIRECT("1:250"))
Book1
AB
1NumMis
212
356
448
5310
67
7
8
99
10
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:10")))/(COUNTIFS(\$A\$2:\$A\$751,(ROW(INDIRECT("1:10"))))=0),ROWS(\$A\$2:B2)),"")

#### Peter_SSs

##### MrExcel MVP, Moderator
Hi Stephen
One issue I notice with your suggestion in the other thread is that the spill range will always be N rows which could be a negative in some circumstances I would have thought.

Another option with minimal spill range here would be

21 02 10.xlsm
AB
117
229
33
44
55
66
78
810
9
10
Missing
Cell Formulas
RangeFormula
B1:B2B1=FILTER(SEQUENCE(10),ISNA(MATCH(SEQUENCE(10),A1:A10,0)),"")
Dynamic array formulas.

Replies
5
Views
97
Replies
2
Views
41
Replies
6
Views
65
Replies
8
Views
139
Replies
4
Views
154

1,147,482
Messages
5,741,406
Members
423,657
Latest member
Medrok2021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back