# Help limiting length results of results from Filter Formula without #REF!

Occasional Contributor

# Help limiting length results of results from Filter Formula without #REF!

Hey, I am building a new Excel (365) app to track our operations.

On the job specific pages, I am trying to return (via Filter Formula) just the first 'up to 10 rows" of data from another range.  Jobs will differ from 1 row to 50 rows, thus I am limiting this summary to the first 10 only.  And I have allowed space for a 'Spill' of up to the first 10 relevant rows.

The following formula works well except in the cases that there are less than 10 rows of data in the filtered results.  In those cases it returns a #REF!

So if for a given job, there are only 7 rows of data returned to this summary, then rows 8,9 & 10 will show #REF!  The #REF! then causes other errors as well.

Any solution ideas?

Here is my filter formula currently;

=INDEX(FILTER('WO Labor Index'!\$A\$3:\$A\$10000,'WO Labor Index'!\$B\$3:\$B\$10000=A2),SEQUENCE(10))

6 Replies

# Re: Help limiting length results of results from Filter Formula without #REF!

I don't have the newest functions to test this, but could you replace 10 with MIN(10, countif()) where countif returns the number of items matching your condition?

Or, could you wrap the index function with iferror and return either blank or zero?

# Re: Help limiting length results of results from Filter Formula without #REF!

If FILTER() returns less than 10 rows, INDEX returns REF since you try to reference not existing rows. Variant could be

``````=LET(
filt, FILTER('WO Labor Index'!\$A\$3:\$A\$10000,
'WO Labor Index'!\$B\$3:\$B\$10000=A2),
n, ROWS(filt), INDEX(filt,SEQUENCE(n))
)``````

# Re: Help limiting length results of results from Filter Formula without #REF!

Thank You!!! I got this resolved.

# Re: Help limiting length results of results from Filter Formula without #REF!

Thank You Sergei!!! I got this resolved.