Find and list the missing value in a column using a reference

New Contributor

I am trying to find the missing numbers in a sequence and have been using this formula, which works fine

 

=IFERROR(SMALL(IF(COUNTIF(K:K,ROW($K$2:$K$7))=0,ROW($K$2:$K$7),""),ROW(K1)), "")

 

When I try reference a value from a column using INDIRECT like this instead:

=IFERROR(SMALL(IF(COUNTIF(K:K,ROW(INDIRECT("$K$2:$K$"&M4))=0,ROW(INDIRECT("$K$2:$K$"&M4)),""),ROW(K1)), "")

 

It does not work .

M4 contains a single number in this case it is 7 and K contains my list of values e.g. 

user12344_0-1646755764392.png

Can someone please let me know why Its not working?

1 Reply

@user12344 

Use $M$4:

 

=IFERROR(SMALL(IF(COUNTIF(K:K, ROW(INDIRECT("K2:K"&$M$4)))=0, ROW(INDIRECT("K2:K"&$M$4)), ""), ROW(K1)), "")