I have an array formula that outputs all values in a range that are not 0 or text.
This range is mainly numbers and one cell of text which is the descriptive header.
However, I need the array to start outputting only when it detects the text that is present in the column (This could be in cell AA4 or could be AA40, it is different each time) and only output the numbers AFTER this point.
For example, given the following in a column:
525
0
22
464
Type
233
0
453
665
Only the numbers 233, 453, 665 should be output by the array.
At the moment it just looks at the range and outputs anything that isn't a 0 or text, regardless of it being above or below the header text. So 525,22,464,233,453,665 would all be output at the moment.
=IFERROR(INDEX($AA$4:$AA$978, SMALL(IF($AA$4:$AA$978<>0, ROW($AA$4:$AA$978)-MIN(ROW($AA$4:$AA$978))+1, ""), ROW(A1))),"")
What do I need to alter/add to perform this task?
A value_if_false clause within the IF statement is not strictly necessary. Also, ROWS is more rigorous than ROW for SMALL's k parameter (see here for an explanation as to why).
You need:
=IFERROR(INDEX($AA$4:$AA$978,SMALL(IF($AA$4:$AA$978<>0,IF(ROW($AA$4:$AA$978)-MIN(ROW($AA$4:$AA$978))+1>MATCH("Type",$AA$4:$AA$978,0),ROW($AA$4:$AA$978)-MIN(ROW($AA$4:$AA$978))+1)),ROWS(A$1:A1))),"")
Unless you're actually using an Excel Table, I'd probably amend some of the references in the above so as to make it more efficient, viz:
=IFERROR(INDEX($AA:$AA,SMALL(IF($AA$4:$AA$978<>0,IF(ROW($AA$4:$AA$978)>MATCH("Type",$AA:$AA,0),ROW($AA$4:$AA$978))),ROWS(A$1:A1))),"")
With a range of just under a thousand cells, you're probably fine to continue to use an IFERROR set-up, though note that, for larger ranges, this can be extremely inefficient - an explanation and alternative set-up is given here.
Regards
Try this Array Formula which requires a special key stroke Ctrl+Shift+Enter instead of Enter alone.
=IFERROR(INDEX(INDEX($AA$4:$AA$978,MATCH(TRUE,ISTEXT($AA$4:$AA$978),0)+1):$AA$978,SMALL(IF(INDEX($AA$4:$AA$978,MATCH(TRUE,ISTEXT($AA$4:$AA$978),0)+1):$AA$978<>0,ROW(INDEX($AA$4:$AA$978,MATCH(TRUE,ISTEXT($AA$4:$AA$978),0)+1):$AA$978)-ROW(INDEX($AA$4:$AA$978,MATCH(TRUE,ISTEXT($AA$4:$AA$978),0)+1))+1),ROWS(A$1:A1))),"")
and copy it down.
