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
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, […]
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 to swetha
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.
[…] Find a sequence […]
Hi this is not working