## Find a sequence

*Article updated on July 29, 2017*

This array formula finds the sequence "Axis Chemical Co." (cell E2) and "Southern Railway" (cell E3) in two consecutive cells in column A and returns the row number in cell F2.

**Array formula in cell F11:**

The second argument in the MATCH function is $A$1:$A$23&$A$2:$A$24. The ampersand concatenates the two cell ranges into one. By concatenating a value with the value below you can easily search for a sequence of values.

**How to enter an array formula**

- Select cell F2
- Click in the formula bar
- Paste formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter once
- Release all keys

### Find multiple sequences

**Array formula in cell F2:**

Copy cell F2 and paste cells below as far as needed.

### Find a sequence with any value between

**Array formula in cell F2:**

If you examine this combined cell ref $A$1:$A$23&$A$3:$A$25 you can see that the first one start at A1 and the second one at A3. This is because the second value in the sequence is a value that can be anything. Let us see the values behind these cell refs.

### Find multiple sequences with any value between

**Array formula in cell F2:**

### Finding a sequence - potential problems

As you can see, the formula returns row 2. Why is that? The MATCH function looks for the value in cell E2 and E3 combined like this, 53 and 89 becomes 5389. The values in cell B2 and B3 are 538 and 9, becomes 5389 and that is a match.

So if we add a delimiting character we can rule out these problems, see the next formula.

**Array formula in cell F6:**

But to be really sure that this works you must check that the character "-" does not exist at all in column B or you could get same the error again. The next formula uses the COUNTIFS function and I think this is the best method, no need for delimiting characters.

**Array formula in cell F10:**

The downside with this formula is that you can´t find multiple sequences in a column.

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

### Download excel *.xlsx file

### Read more

- Find a sequence of values - wildcard search
- Repeat values
- Create number sequences
- Merge lists with criteria

### Functions in this post:

**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

### 2 Responses to “Find a sequence”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] Find a sequence […]

Hi this is not working