selecting one from multiple occurrences
 

Hi All,

I have a column containing multiple occurrences of a string. I can use
function
=MATCH("?.2";Sheet1!A:A;0) to find the row number of the First occurrence,
but I need row No's of ALL occurences (in an array?) because I'd like to
choose one of them that fulfils a certain criterion. This criterion is that
does the row No occur in an other sequence of row No's?

E.g.
"?.2" occurs in rows 8,14,20
the other sequence of row No's: 13,20
I want to choose 20 because it occurs in the 2nd sequence.

How can I do that in a cell formula?

Thanks,
Stefi

selecting one from multiple occurrences
 

Put your formula in B1, then in B2, add

=IF(ISERROR(MATCH("?.2",INDIRECT("A"&B1+1&":A1000" ),0)),"",MATCH("?.2",INDIR
ECT("A"&B1+1&":A1000"),0)+B1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Hi All,

I have a column containing multiple occurrences of a string. I can use
function
=MATCH("?.2";Sheet1!A:A;0) to find the row number of the First occurrence,
but I need row No's of ALL occurences (in an array?) because I'd like to
choose one of them that fulfils a certain criterion. This criterion is
that
does the row No occur in an other sequence of row No's?

E.g.
"?.2" occurs in rows 8,14,20
the other sequence of row No's: 13,20
I want to choose 20 because it occurs in the 2nd sequence.

How can I do that in a cell formula?

Thanks,
Stefi

selecting one from multiple occurrences
 

Thanks Bob, it works!
Stefi


€žBob Phillips€� ezt Ã*rta:

Put your formula in B1, then in B2, add

=IF(ISERROR(MATCH("?.2",INDIRECT("A"&B1+1&":A1000" ),0)),"",MATCH("?.2",INDIR
ECT("A"&B1+1&":A1000"),0)+B1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Hi All,

I have a column containing multiple occurrences of a string. I can use
function
=MATCH("?.2";Sheet1!A:A;0) to find the row number of the First occurrence,
but I need row No's of ALL occurences (in an array?) because I'd like to
choose one of them that fulfils a certain criterion. This criterion is
that
does the row No occur in an other sequence of row No's?

E.g.
"?.2" occurs in rows 8,14,20
the other sequence of row No's: 13,20
I want to choose 20 because it occurs in the 2nd sequence.

How can I do that in a cell formula?

Thanks,
Stefi