INDEX SMALL problem

Hi.

Not sure I understand your formulas.

Apart from the issue pointed out by daddylonglegs re the construction you are using for the k parameter of the SMALL function, there is also the fact that:

Surveys!P6586:P7345

is a range comprising 760 rows.

whereas:

ROW($E$4:$E$203)-ROW($E$4)+1

generates an array comprising just 200 rows.

Hence any non-blank entries beyond P6785 will simply not be considered. In fact, every formula will return a blank since every formula will result in an error by virtue of your array being passed to SMALL being filled with #N/A errors as a result of this issue.

These two ranges need to be of the same dimension, e.g.:

=IFERROR(INDEX(INDIRECT("Surveys!P6586:P7345"),SMALL(IF(INDIRECT("Surveys!P6586:P7345")<>"",ROW(INDIRECT("Surveys!P6586:P7345"))-ROW(INDIRECT("Surveys!P6586"))+1,""),ROWS($1:1))),"")

though can I also ask why you are using INDIRECT in this expression, especially since you are simply hard-coding the sheet name in the formula, and not e.g. referencing it dynamically from some cell?

Regards