Finding matching records

[tbrynard01]

I have two spreadsheets with Data, I am wanting to find the Donor ID #, in the one sheet if it matches with the person in the other sheet. but of course when there are multiple last names that are the same and even first names how do I select for example an address as another match?

I'm not sure if this makes sense but it will save a lot of time if I can get this figured out with a formula as opposed to manual checking.

Thank you

 

[tonyyy]

One approach would be to...

1. In a new column of the first spreadsheet, use the Concatenate function to combine the last name, first name and address of the first row. Copy the formula down to the end.
2. Do the same for the second spreadsheet.
3. Use the Index and Match functions utilizing the just created concatenated columns to return the donor number.

If you're not familiar with Concatenate, Index and Match, you'll find many examples on this forum as well as elsewhere online.

 

[AhoyNC]

Since you are using Excel 365 you could use the FILTER function.
Example:
Sheet 1

Book2
	A	B	C	D
1	First Name	Last Name	Address	Find ID
2	Joe	Paul	123 Cort Lane	ID12
3	Sam	Smith	256 Oxy St	ID13
4	Jane	Paul	75 Gray Street	ID14
5	Sam	Smith	658 Down Ln	ID15
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(Sheet2!$A$2:$A$5,($A$2:$A$5=Sheet2!B2)*($B$2:$B$5=Sheet2!C2)*($C$2:$C$5=Sheet2!D2),"No Match")

Sheet 2

Book2
	A	B	C	D
1	ID#	First Name	Last Name	Address
2	ID12	Joe	Paul	123 Cort Lane
3	ID13	Sam	Smith	256 Oxy St
4	ID14	Jane	Paul	75 Gray Street
5	ID15	Sam	Smith	658 Down Ln
Sheet2

 

[tbrynard01]

One approach would be to...

1. In a new column of the first spreadsheet, use the Concatenate function to combine the last name, first name and address of the first row. Copy the formula down to the end.
2. Do the same for the second spreadsheet.
3. Use the Index and Match functions utilizing the just created concatenated columns to return the donor number.

If you're not familiar with Concatenate, Index and Match, you'll find many examples on this forum as well as elsewhere online.

Thank you. I'm not have taken a look at both but not sure how to combine correctly as I'm not getting the correct results. I did use vlookup on the concatenate fields and it did return results

 

[tbrynard01]

Since you are using Excel 365 you could use the FILTER function.
Example:
Sheet 1

Book2
	A	B	C	D
1	First Name	Last Name	Address	Find ID
2	Joe	Paul	123 Cort Lane	ID12
3	Sam	Smith	256 Oxy St	ID13
4	Jane	Paul	75 Gray Street	ID14
5	Sam	Smith	658 Down Ln	ID15
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(Sheet2!$A$2:$A$5,($A$2:$A$5=Sheet2!B2)*($B$2:$B$5=Sheet2!C2)*($C$2:$C$5=Sheet2!D2),"No Match")

Sheet 2

Book2
	A	B	C	D
1	ID#	First Name	Last Name	Address
2	ID12	Joe	Paul	123 Cort Lane
3	ID13	Sam	Smith	256 Oxy St
4	ID14	Jane	Paul	75 Gray Street
5	ID15	Sam	Smith	658 Down Ln
Sheet2

Thanks, did not get the correct results using this. Thanks though.

 

[AhoyNC]

It would help if you would post a small sample of your data and result you expect. Is the data I used in the example close to how your sheets look? What formula did you actual use that gave the wrong answer?