I am working in Google Sheets and have the following formula:

=ArrayFormula(IF(ROWS(A$5:A5)>$C$2,"",INDEX('Annual Schedule'!$A$1:J$1852,SMALL(IF(('Annual Schedule'!$A$1:J$1852>=$A$2)*('Annual Schedule'!$A$1:J$1852<=$B$2),ROW('Annual Schedule'!$A$1:J$1852)-ROW('Annual Schedule'!A$1)+1),ROWS(A$5:A5)))))

It is designed to search the "Annual Schedule" and show me a whole row of results if the date in Column A on the Annual Schedule sheet sits between the dates in cells A2 and B2 on the current sheet.

It works perfectly but will not pull data beyond row 1000, which happens to be the date 20th June. I need to be able to find the whole year. Is there any reason for this? Or is there some kind of cap on the number of rows that I can remove?

  • 1
    Welcome to Web Applications Stack Exchange. Try filter(). Consider sharing a publicly editable sample spreadsheet with realistic-looking data. May 11 at 21:06
  • Hi, welcome to WebApps. It's difficult to imagine finding a solution to this problem without seeing your spreadsheet. and your actual data.
    – Tedinoz
    May 12 at 23:12


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.