Using MATCH to find the 2nd match, not only 1st

M

MLP

I have a set of data of people and their corresponding registration numbers.
This list is over 9000 rows long and many names can be duplicates if someone
registered two different times over the course of a year. I currently have a
formula set up to find the person (based on an another list) using
Index/Match. I can always find the first matching occurence using Match, but
in the case where someone registers more than one time, I can't get the
second matching registration number. Any ideas?

Peters Carol 345122
Peters Carol 345141
peters katie 333496
Peterseso Abby 375938
Petersmark Jennifer 392800
Peterson Aaron 371537
Peterson Amy 364819
Peterson Cornelia 352957
Peterson eric 443652
Peterson Jake 400413
Peterson Jeromey 371646
Peterson John 327468
Peterson John 362066
 
B

Bob Phillips

=MATCH(A1,A:A)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

MLP

Yes, that tells me how many matches I have, but I need to know what the two
matches are. For example if I want to know all the diffent numbers that are
assinged to Peters, Carol, I would want the result to be 345122 and 345141.
I didn't quite explain that clearly.
 
P

Peo Sjoblom

Filter are made for these things, one way using a formula would be

=INDEX($B$1:$B$10,SMALL(IF($A$2:$A$10="ADAM",ROW($A$2:$A$10)),ROW(1:1)))

entered with ctrl + shift & enter

copied down until you get an error, but using a filter would be much better

Regards,

Peo Sjoblom
 
B

Bob Phillips

It returns the instance of the match, so you just pass it to an INDEX
function to get the value

=INDEX(B:B,MATCH(A1,A:A))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top