get an INDEX/SORT/SEQUENCE to only display rows with actual non-zero numbers and no errors

Does anyone know how I can get these kinds of formulas to not return any error values (like #VALUE! or #DIV/0!) or any zero values and instead only return rows that have actual non-zero numbers in the 7th column?

=INDEX(SORT('Sheet1'!A3:P356,7,-1),SEQUENCE(10),SEQUENCE(1,9))
=INDEX(SORT('Sheet1'!A3:P356,7,1),SEQUENCE(10),SEQUENCE(1,9))

Thank you very much in advance!

You're sorting on the 7th column in ascending and descending order, and you seem to want 10-row by 9-column arrays of the top-left-most non-error cells in those sorted blocks.

The trick would be shifting non-error values from outside the 10x9 grid into the 10x9 grid to replace error values.

Presumably you want to do this by rows. That is, if your data were

101	#VALUE!	103
#DIV/0!	202	203
301	302	303

and you wanted a 2-row by 2-column table, your expected result should be

101	103
202	203

rather than

101	202
301	302

Also, do you always want the 7th column AS the 7th column? Could it ever contain error values? If, say, in some row columns A-E and G-P were non-error values, you'd want that row in the result to be from columns A, B, C, D, E, H, G, I, J in that order?

Would you want to skip rows with fewer than 9 non-error values?

If that's all that were needed, why not

=IFERROR(INDEX(SORT(Sheet1!A3:I356,7,-1),SEQUENCE(10),0),"") ?

IOW, why include columns J to P? Note: not screening out 0 values. To do that, if OP has the LET function,

=LET(x,INDEX(SORT(Sheet1!A3:I356,7,-1),SEQUENCE(10),0),IFERROR(IF(AND(ISNUMBER(x),x=0),"",x),""))

I guess what I'm looking to do is pull only some rows from a large array, and pull those rows based on the top 10 and bottom 10 values from a certain column. And there are plenty of rows where there is no value in the column I'm looking at, as well as plenty of rows where that cell is one of the errors I described above. More or less just need a tab on a sheet that pulls only some of the array so I don't need to do the manual sorting. Does that make sense?

Doesn't help. It was already clear you wanted the top and bottom 10 rows based on the 7th column. It remains unclear what you want for each column in the result.

Could the 7th/sort column ever have error values? This doesn't require a paragraph response, or even a sentence. One word, yes or no, would suffice. Indeed, any response other than one or the other of those 2 words wouldn't help at all. I ask because if that column could also contain error or 0 values, you'd need a FILTER call inside the SORT call. If there could be errors or 0s in the 7th column, presumably those rows should be excluded.

Of the remaining rows, do you just want error and zero values to be replaced by empty strings, "", or do you want the 9 leftmost non-error, nonzero values from columns A to P on each row?

If you just want the top and bottom 10 rows from Sheet1!A3:P356, that's easy enough to accomplish by selecting a 10-row by 16-column range and entering the ARRAY formula ([Ctrl]+[Shift]+[Enter])

Formula:

Please Login or Register  to view this content.

where x is either 1 or -1. Entering as a traditional array formula would constrain the result to the size of the range in which you enter the array formula. No need for INDEX and SEQUENCE.

If you only want 9 columns, then no need to go out to column P. You should get that result selecting a 10-row by 9 column range and using the ARRAY formula

Formula:

Please Login or Register  to view this content.

again where x is either 1 or -1.

If you want to use spill references like X99# to refer to the result ranges, then you would need spilled formulas. In which case

Formula:

Please Login or Register  to view this content.

again where x is either 1 or -1, and Y is a placeholder for whichever the rightmost column returned should be.