Forum Discussion

user12344's avatar
user12344
Copper Contributor
Mar 08, 2022

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

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. 

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

Resources