How do I write a formula to display missing numbers from 1 to 10?

[arthurz11]

 

[ExcelToDAX]

How about:

=FILTER(ROW(INDIRECT("1:10")), ISNA(MATCH(ROW(INDIRECT("1:10")), $A$1:$A$5, 0)))

 

[bobsan42]

Book1
	C	D	E	F
3	2	1	3	5
4	3	5	10	7
5	4	8	2	8
6	6	9	6
7	10		9
8	7		1
9			4
Sheet1

Cell Formulas
Range	Formula
D3:D6,F3:F5	D3	=FILTER(SEQUENCE(10),COUNTIF(C:C,SEQUENCE(10))=0)
Dynamic array formulas.

 

[Fluff]

An non-volatile option

=LET(s,SEQUENCE(10),FILTER(s,ISNA(XMATCH(s,A2:A10))))

 

[bobsan42]

wow.
just pick one

 

[Scott Huish]

=UNIQUE(TOCOL(HSTACK(A1:A6,SEQUENCE(10))),,TRUE)

Sorry, I guess you need Excel 365 for this.

 

[Scott Huish]

I think this will work in Excel 2021

=UNIQUE(TOCOL(CHOOSE({1,2},A1:A6,SEQUENCE(10))),,TRUE)

 

[Eric W]

I think this will work in Excel 2021

=UNIQUE(TOCOL(CHOOSE({1,2},A1:A6,SEQUENCE(10))),,TRUE)

Sorry, no TOCOL in 2021 either.

 

[Scott Huish]

That's weird. Because when I looked it up in Bing:

 

[barry houdini]

A prehistoric type approach

In C2 copied down as far as required

=IFERROR(SMALL(IF(COUNTIF(A$2:A$6,{1,2,3,4,5,6,7,8,9,10})=0,{1,2,3,4,5,6,7,8,9,10}),ROWS(C$2:C2)),"")