query to find 3 texts in 3 consecutive rows in 1 column

hey guys and girls

I am wondering if I may pick your brains a bit

I can't seem to find a proper answer to this anywhere, and I am not sure if it's something that can be done

I will simplify it here:

# Rows: 500
Column 1: list of 10 names repeating in no order
Column 2: Descending order of a variable

I like to keep column 2 the way it is sorted now, but I like excel to find a certain sequence of names in column 1. For example, I'd like excel to highlight any instances where Jerry - Alex - Tom names are on top of each other in that order, like this:

Jeff
Alex
Mary
Jerry <--
Alex <--
Tom <--
Alison
Alan
Joe
Tom
Alex
Jerry
Peter
Josephine
Ronald
Jerry <--
Alex <--
Tom <--

is there anyway I can get excel to find these sequences? The find option only finds 1 name in 1 colum at a time, but I am interested in a sequence in 3 rows under 1 column

Thank you in advance

nik

Maybe like this:

Please Login or Register  to view this content.

The array formula in C2 and copied down is

=MATCH(B2 & B3 & B4, OFFSET(A1, C1, 0, 100) & OFFSET(A1, C1 + 1, 0, 100) & OFFSET(A1, C1 + 2, 0, 100), 0) + C1

Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

thank you, but I believe the match query just identifies the position.. I was hoping it would actually highlight the sequences, similar to how the "find" option jumps through the rows and highlights the searched term

I guess I am expecting too much?