First name, surname and DOB checking against large database

[Gav13]

Hi all,

I have a list (table 1) of names + date of births (dobs) which I'd like to search against a larger list (table 2) of names and dobs. Unfortunately table 2 has extra name columns and is quite unstructured; for example first names could appear in surname columns and dob may be missing.

What I'd like to do is check how many matches there are on a specific row - so for example if a name matches on first name and surname and dob then it is 3 matches as opposed just name and surname would be 2 matches.

Below are examples of the two tables and the sort of expected match (the match field can look like anything really). Notice that Charles Xavier matches on 3 despite the names being out of order in table 2. I've been trying to work this out with Sumproduct but apart from a ridiculously long formula looking up every single possible combination i can't get it to work.

Any help appreciated thanks!

Table 1​
First Name​	Surname​	DOB​	MATCH?​
John​	Smith​	01/01/1958​	No match​
Jane​	Doe​	15/12/1980​	Match x 3​
Charles​	Xavier​	06/06/1932​	Match x 3​
Oliver​	Queen​	09/09/2021​	Match x 2​

Table 2​
Name 1​	Name 2​	Name 3​	Name 4​	Name 5​	DOB​
Kimberly​	Burgess​	Harper​	Yoder​		04/07/1959​
Briana​	Smith​	Josiah​			05/06/1960​
James​	Young​	Latisha​	Maldonado​		04/06/1985​
James​	Glass​				12/08/1985​
James​	Watts​				dd/mm/yy​
Travis​	Arnold​				10/04/1986​
Ian​	Kerr​				10/07/1988​
Jane​	Doe​				15/12/1980​
Haley​	Goodwin​				11/10/1988​
Lisa​	Williams​	Jazmyn​			20/12/1989​
Joshua​	Brown​	Humayra​	Christensen​		03/02/1991​
Wendy​	Todd​				11/07/1992​
Jessica​	Davis​				27/08/1993​
Steven​	Romero​				20/04/1995​
Xavier​	Charles​				06/06/1932​
Charles​	Little​				24/04/2009​
Rhonda​	Smith​	Holloway​	Bowers​		26/11/2011​
Janice​	Reed​	Ferguson​	Fuller​	Zavala​	08/02/2013​
Smith​	John​				dd/mm/yy​
Jeremy​	Howard​				24/06/2021​
Oliver​	Queen​				12/08/1985​

 

[Takiar]

I don't know if you really need the Table 2's columns Name 3, Name 4 and Name 5. But here is a way to do it based on the match (or xmatch) function.

Book1
	A	B	C	D	E	F
1	Table 1
2	First Name	Surname	DOB	MATCH?
3	John	Smith	01/01/1958	Match x 2
4	Jane	Doe	15/12/1980	Match x 3
5	Charles	Xavier	06/06/1932	Match x 3
6	Oliver	Queen	09/09/2021	Match x 2
7
8
9	Table 2
10	Name 1	Name 2	Name 3	Name 4	Name 5	DOB
11	Kimberly	Burgess	Harper	Yoder		04/07/1959
12	Briana	Smith	Josiah			05/06/1960
13	James	Young	Latisha	Maldonado		04/06/1985
14	James	Glass				12/08/1985
15	James	Watts				dd/mm/yy
16	Travis	Arnold				10/04/1986
17	Ian	Kerr				10/07/1988
18	Jane	Doe				15/12/1980
19	Haley	Goodwin				11/10/1988
20	Lisa	Williams	Jazmyn			20/12/1989
21	Joshua	Brown	Humayra	Christensen		03/02/1991
22	Wendy	Todd				11/07/1992
23	Jessica	Davis				27/08/1993
24	Steven	Romero				20/04/1995
25	Xavier	Charles				06/06/1932
26	Charles	Little				24/04/2009
27	Rhonda	Smith	Holloway	Bowers		26/11/2011
28	Janice	Reed	Ferguson	Fuller	Zavala	08/02/2013
29	Smith	John				dd/mm/yy
30	Jeremy	Howard				24/06/2021
31	Oliver	Queen				12/08/1985
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=IF(ISNA(XMATCH(A3&B3&C3,$A$11:$A$31&$B$11:$B$31&$F$11:$F$31,0)),IF(ISNA(XMATCH(B3&A3&C3,$A$11:$A$31&$B$11:$B$31&$F$11:$F$31,0)),IF(ISNA(XMATCH(A3&B3,$A$11:$A$31&$B$11:$B$31)),IF(ISNA(XMATCH(B3&A3,$A$11:$A$31&$B$11:$B$31)),"No Match","Match x 2"),"Match x 2"),"Match x 3"),"Match x 3")