|
Using MATCH to find the 2nd match, not only 1st
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 |
=MATCH(A1,A:A)
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "MLP" wrote in message ... 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 |
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. "Bob Phillips" wrote: =MATCH(A1,A:A) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MLP" wrote in message ... 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 |
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 "MLP" wrote in message ... 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. "Bob Phillips" wrote: =MATCH(A1,A:A) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MLP" wrote in message ... 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 |
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) "MLP" wrote in message ... 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. "Bob Phillips" wrote: =MATCH(A1,A:A) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MLP" wrote in message ... 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 |
| All times are GMT +1. The time now is 04:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com