Match nth instance

问题:

I have an array of data in a spreadsheet that I want to automagically pull the first seven instances that start with a specific string to seven independent cells so it can easily be seen what the full cell contents are. I have composed a formula that successfully finds the first occurrence but I can't find a way to extend this to the second, third etc. I have thought of using the previous cell's match as input to the next cell, but everything I've tried doesn't seem to work.

I have found some examples that use VBA or CSE formulas, but any CSE formulas I have tried don't seem to work. But due to constraints imposed on me I can't use VBA or CSE anyway so I never looked too deeply in to it.

Here is the formula that matches the first instance:

=INDEX($A$13:$A$5000,MATCH("start*",$A$13:$A$5000,0))  

This is in Cell A4; I would like A5:A11 to match the next six instances.

Is this possible in a vanilla formula?

回答1:

This can be done using an "array" function (Ctrl + Shift + Enter) but I personally do not like using those.

My suggestion would be the following instead:

A4 =INDEX($A$13:$A$5000,MATCH("start*",$A$13:$A$5000,0))

B4 =MATCH("start*",$A$13:$A$5000,0)

A5 =INDEX(OFFSET($A$13:$A$5000,B4,0),MATCH("start*",OFFSET($A$13:$A$5000,B4,0),0))

And copy this down to A11

B5 =MATCH("start*",OFFSET($A$13:$A$5000,B4,0),0)+B4

And copy this down to B11

Basically this "pushes" the search area down an amount equal to the sum of the previous matches, and then finds the next match.

回答2:

How about using a Helper column. In B13 enter:

=IF(LEFT(A13,5)="start",1+MAX($B$1:B12),"")

and copy down. Then in A4 enter:

=IFERROR(INDEX($A$13:$A$5000,MATCH(ROWS($1:1),$B$13:$B$5000,0)),"")

and copy down:

回答3:

A slight variation on the other answers, still using a helper column starting in B4 and pulled down to get the match position in a$13:a$5000:-

=IFERROR(MATCH("BR*",INDEX(A$13:A$5000,B3+1):A$5000,0)+B3,"")

(assuming B3 is empty).

Then to get the corresponding match in A4 and pulled down:-

=IFERROR(INDEX(A$13:A$5000,B4),"")

(my test data uses strings starting with BR).