# Search for a sequence of values

This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above shows two search values in cell E2 and E3, the formula in cell F2 looks for those search values in column A and the corresponding row numbers are returned if they match.

The order is important, meaning the search values must be in a sequence exactly as they are written in cell E2 and E3.

#### Table of Contents

## 1. How to find two search values next to each other vertically?

The image above shows a formula in cell F2 that returns only the first match based on the search values in cell E2 and E3.

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:

**Update 3-17-2021, **the following Excel 365 formula extracts the corresponding values from the column B and C based on the found sequences:

How to use the FILTER function

### How to enter an array formula

There is no need to enter the formulas if you own Excel 365, it uses dynamic array formulas.

- Double press with left mouse button on cell F2. The prompt shows up.
- Paste formula to cell.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.

The formula now looks like this: {=MATCH(E2&E3, $A$1:$A$23&$A$2:$A$24, 0)}

Don't enter the beginning and ending curly brackets yourself, they appear automatically if you managed to enter the array formula successfully.

### Explaining formula in cell F2

#### Step 1 - Concatenate cells

The ampersand lets you concatenate cell values.

E2&E3

returns "Axis Chemical Co.Southern Railway"

#### Step 2 - Concatenate cell ranges

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.

$A$1:$A$23&$A$2:$A$24

returns {"CompanyParadise Airlines";... ;"Southern Railway"}

#### Step 3 - Find the relative position

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

**MATCH(***lookup_value*,Â *lookup_array*,Â *[match_type]*)

MATCH(E2&E3, $A$1:$A$23&$A$2:$A$24, 0)

becomes

MATCH("Axis Chemical Co.Southern Railway", {"CompanyParadise Airlines";... ;"Southern Railway"}, 0)

and returns 11.

## 2. How to find all instances of two search values next to each other vertically?

The image above demonstrates an array formula that returns row numbers of all found sequences based on the search values in cells E2 and E3.

Array formula in cell F2:

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

**Update 3-17-2021,**the following Excel 365 formula extracts the corresponding values from the column B and C based on the found sequences:

How to use the FILTER function

### Explaining formula in cell F2

#### Step 1 - Logical expression

$E$2&$E$3=$A$1:$A$23&$A$2:$A$24

becomes

returns {FALSE; FALSE; ... ; FALSE}

#### Step 2 - Create a sequence of numbers from 1 to n

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

**MATCH(***lookup_value*,Â *lookup_array*,Â *[match_type]*)

MATCH(ROW($A$2:$A$23), ROW($A$2:$A$23)))

returns {1; 2; ... ; 22}

#### Step 3 - Evaluate IF function

The IF function returns the corresponding row number if the logical value is TRUE and FALSE if FALSE.

IF($E$2&$E$3=$A$1:$A$23&$A$2:$A$24, MATCH(ROW($A$2:$A$23), ROW($A$2:$A$23)))

returns {FALSE; FALSE; ... ; 22; FALSE}

#### Step 4 - Find k-th smallest number in array

The SMALL function returns the k-th smallest value from a group of numbers. It ignores text and boolean values.

SMALL(*array*,Â *k*)

SMALL(IF($E$2&$E$3=$A$1:$A$23&$A$2:$A$24, MATCH(ROW($A$2:$A$23), ROW($A$2:$A$23))), ROWS($A$1:A1))

and returns 11.

## 3. How to find a sequence with any value between?

The image above demonstrates a formula in cell F2 that looks for a sequence of three values where the middle one can be anything.

Array formula in cell F2:

**Update 3-17-2021,** the following Excel 365 formula extracts the corresponding values from the column B and C based on the found sequences:

## Explaining formula in cell F2

#### Step 1 - Concatenate lookup_value

Note that cells E2 and E4 are now being concatenated.

E2&E4

becomes

"Massive Dynamic"&"Southern Railway"

and returns

"Massive DynamicSouthern Railway"

#### Step 2 - Concatenate lookup_array

The lookup array consists of two cell ranges concatenated, note that the first one begins at $A$1 and the second one at cell $A$3.

$A$1:$A$23&$A$3:$A$25

returns

{"CompanyLexCorp";... ;"Southern Railway"}

#### Step 3 - Find the relative position of the lookup_value in the lookup_array

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

**MATCH(***lookup_value*,Â *lookup_array*,Â *[match_type]*)

MATCH(E2&E4,$A$1:$A$23&$A$3:$A$25,0)

returns 21.

If you examine this combined cell ref $A$1:$A$23&$A$3:$A$25 you can see that the first one starts 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.

Cell reference $A$3:$A$24 is wrong in the image above, it should be $A$3:$A$25.

### Find multiple sequences with any value between

Array formula in cell F2:

**Update 3-17-2021,** the following Excel 365 formula extracts the corresponding values from the column B and C based on the found sequences:

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

### Read more

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

### Sequence category

This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

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

### Excel categories

### 2 Responses to “Search for a sequence of values”

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