Find x consecutive numbers in an array

Question

I have an array of non-ordered integers, and I am trying to return a TRUE or FALSE based on if x consecutive numbers can be found within the array

Example array in A1:A8 [20,17,13,16,21,22,19,18]

In B1 is [5], requiring the function to find 5 consecutive numbers.

No VBAs please

Based on similar queries and answers on this website, the best I came up with was =AND(IF(SUM(--(A1:A8=TRANSPOSE(A1:A8)+1)),TRUE,FALSE),IF(SUM(--(A1:A8=TRANSPOSE(A1:A8)+2)),TRUE,FALSE),IF(SUM(--(A1:A8=TRANSPOSE(A1:A8)+3)),TRUE,FALSE),IF(SUM(--(A1:A8=TRANSPOSE(A1:A8)+4)),TRUE,FALSE))

I tried to mimic one of the answers from below How to determine if a range of cells contain consecutive numbers?

Answer 1

=LET(?,UNIQUE(A1:A8),ISNUMBER(FIND(REPT(1,B1-1),CONCAT(N(MMULT(LARGE(?,SEQUENCE(ROWS(?)-1)+{0,1}),{1;-1})=1)))))

If you're not using an English-language version of Excel then the separators within the two array constants, i.e.

{0,1} and {1;-1}

may require amendment.

This issue can be circumvented - albeit at the cost of formula brevity - with the following locale-independent version:

=LET(?,UNIQUE(A1:A8),ISNUMBER(FIND(REPT(1,B1-1),CONCAT(N(MMULT(LARGE(?,SEQUENCE(ROWS(?)-1)+SEQUENCE(,2,0)),SEQUENCE(2,,1,-2))=1)))))

As per some of the solutions in the link you posted, a more concise solution will almost certainly be available if we employ a pair of offset ranges (for example, A1:A7 and A2:A8), though personally I try to avoid such referencing (imagine replacing the range A1:A8 with a Structured Table column reference, for example - generating the equivalents of A1:A7 and A2:A8 would then be cumbersome).