## Find a sequence

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

Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right […]

Excel has a great built-in versatile tool for creating number sequences. In some situations, however, you need to rely on […]

Find the longest/smallest consecutive sequence of a value [VBA]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

How to identify two consecutive dates in a list

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

### 2 Responses to “Find a sequence”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

[…] Find a sequence […]

Hi this is not working