Locate Cells With a Simple Criteria

Question

I have data like:

I need to locate (get row numbers) for cells starting with the letter A.

My current approach is to use a "helper" column. In B1 I enter:

=IF(LEFT(A1)="A",1,"")

and in B2 I enter:

=IF(LEFT(A2)="A",MAX($B$1:B1)+1,"")

and copy down. This identifies the rows of interest. Finally in C1 I use:

=IFERROR(MATCH(ROWS($1:1),$B$1:$B$23,0),"")

and copy down.

My goal is to eliminate the "helper" column.

The only two options I see are:

• use an autofilter
• use a VBA UDF

The autofilter approach is bad because the filter would have to be re-run whenever the data is refreshed. The UDF approach bad because the sheet must work in a VBA-free environment.

Is there any kind of formula or array formula that can get the row numbers or am I stuck with the "helper"column ??

Answer 1

Try this entered as an array formula with Ctrl+Shift+Enter:

{=SMALL(IF(LEFT($A$1:$A$23)="A",ROW($A$1:$A$23),9E+99),ROW())}

I presume your criteria is more complicated that this in your actual application, though. You can replace the LEFT($A$1:$A$23)="A" with any criteria you like and it'll still work.

Note that this will return 9E+99 if you copy it down for more rows than there are that start with "A". You could tweak it to return blank instead:

{=IFERROR(SMALL(IF(LEFT($A$1:$A$14)="A",ROW($A$1:$A$14),""),ROW()),"")}

Answer 2

Try this:
Put 0 in C1.
From C2: =match(indirect("a"&c1+1&":a<last row>",true),"a*",0)+C1
Notes: Match is not case sensitive. You need to manage errors after ask the matches are found.