selecting one from multiple occurrences

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
Back
Top