How to find the range of rows with matching sequence of values

Question

Say I have a sheet with 1 column of random numbers, like

5, 3, 7, 1, 2, 6, 9, 8, 3, 0.

Actual data is longer than this. I want to find the range of rows that has a specific sub sequence. Like 2, 6, 9, 8. Output can be to highlight the rows, or at least return the start index of that matched sequence (index 4 in this case).

5, 3, 7, 1, 2, 6, 9, 8, 3, 0.

I thought a simple Find command in top menu would have an option to search like this, but it doesn't. Is there a way to do this?

Answer 1

try:

=(A1=2)*(A2=6)*(A3=9)*(A4=8)

---

or:

=ARRAYFORMULA(MATCH(2698, 1*(A1:A100&A2:A101&A3:A102&A4:A103), 0))

---

or jump link if you want:

=HYPERLINK("#gid=0&range=A"&
 ARRAYFORMULA(MATCH(2698, 1*(A1:A100&A2:A101&A3:A102&A4:A103), 0)), "jump")