In my last post I showed you how to find a sequence of values. The array formula extracted the row of the first found sequence, see below.

Find a sequence of values

Array formula in cell F3:

=MATCH(1, COUNTIFS(E3, B1:B23, E4, B2:B24), 0)

What I didn´t show you was how to find multiple sequences with this particular array formula.

Find multiple sequences of values

This array formula finds all instances of a sequence. It returns their starting row number.

Array formula in cell F8:

=SMALL(IF(COUNTIFS($E$8, $B$1:$B$23, $E$9, $B$2:$B$24)=1, MATCH(ROW($A$1:$A$23), ROW($A$1:$A$23)), ""), ROW(A1))

Look for a multi-level sequence

The next array formula looks for a values in both columns and rows. The picture explains it all.

Find a sequence on multiple columns

Array formula in cell H3:

=MATCH(1,COUNTIFS(F3, A1:A23, F4, A2:A24, G3, B1:B23, G4,B2:B24), 0)

Use this formula if you are looking for the first instance of a sequence. It looks for a specific sequence (LexCorp and Paradise Airlines) in column A and for 604 and 767 in column B. LexCorp and 604 must be on the same row and Paradise Airlines and 767 must be on the next row below.

This formula is quite small, if you compare it to the following formula.

Look for multiple multi-level sequences

Find multiple sequences on multiple columns

Array formula in cell H8:

=SMALL(IF(COUNTIFS($F$8, $A$1:$A$23, $F$9, $A$2:$A$24, $G$8, $B$1:$B$23, $G$9, $B$2:$B$24)=1, MATCH(ROW($A$1:$A$23), ROW($A$1:$A$23)), ""), ROW(A1))

It is like the formula above but it returns the location of multiple sequences.

Wild card search for a sequence

This demonstrates how to do a wild card search for a sequence. This formula returns the row number of first found sequence in column A. The formula looks for a text string in a cell and another text string in the cell below. You don´t need to enter the wild cards * (asterisks), see cell F3 and F4.

Find a sequence - wildcard search

=MATCH(TRUE, ISNUMBER(FIND($F$11, $A$1:$A$23)*FIND($F$12, $A$2:$A$24)), 0)

Wild card search for multiple sequences

This demonstrates how to do wild card searches for multiple sequences. This formula returns the row number of all found sequences in column A. The sequence is in cell F11 and F12.

Find multiple sequences - wildcard search

Array formula in cell G11:

=SMALL(IF(ISNUMBER(FIND($F$11, $A$1:$A$23)*FIND($F$12, $A$2:$A$24)), MATCH(ROW($A$1:$A$23), ROW($A$1:$A$23)), ""), ROW(A1))

If a cell contains "Corp" and the next cell beneath contains "Airlines" the array formula returns the row number.

If you want to learn more about array formulas join Advanced excel course.

Read more

Functions in this post:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

SMALL(array,k)
Returns the k-th smallest number in this data set.

ROW(reference)
Returns the rownumber of a reference.

MATCH(lookup_value, lookup_array, [match_type]
Returns the relative position of an item in an array that matches a specified value.

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria.

FIND(find_text,within_text,[start_num])
Returns the starting position of one text string within another text string. FIND is case-sensitive.

Download excel *.xlsx file

Find a sequence of values.xlsx