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

### Category: Sequence

Create number sequences in excel 2007

Create number sequences (Autofill) Example 1 Type "1" in cell B3 + Enter Right click on black dot and drag […]Comments(9) Filed in category: Excel, Sequence

Sequencing and numbering of batches in excel

Question: I have two volumes which keeps on varying in L1 & L2 head. This is the only info i […]Comments(6) Filed in category: Excel, Sequence

Find the longest/smallest 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 […]Comments(5) Filed in category: Excel, Sequence

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 […]Comments(3) Filed in category: Excel, Sequence

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 […]Comments(1) Filed in category: Excel, Sequence

### 2 Responses to “Find a sequence”

### Leave a Reply

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

<code>your formula</code>

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