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:



It does not work .

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


Can someone please let me know why Its not working?

1 Reply


Use $M$4: