Match Data and Input Value

[jessebh2003]

Hello all!
I'm working on a workbook where I need to match data and if the match is found, input a value. The workbook has two sheets - Sheet1 and VideoTracking.

Here's what I'm trying to accomplish...

1. Find an email match - match column F (VideoTracking) to column F (Sheet1[Provider Email ])
   1. If match found, move to step 3
   2. If no match found, move to step 2
2. No match found - match column E (VideoTracking[Full Name]) to column A (Sheet1[First Name ]) and column C (Sheet1[Last Name ])
   1. If match found, move to step 3
   2. If no match found, move to step 4
3. Match found, check that column I (VideoTracking[Coverage]) is equal to or greater than 90%
   1. If true, cell in column H (Sheet1[Completed Requirements ]) of matching name or email is Yes
4. No matches found, cell in column Q (VideoTracking[No Matches Found]) is No Matches

I think I'm overcomplicating this with too many helper columns:

1. Matching column column F (VideoTracking[ Email]) to column F (Sheet1[Provider Email ])

=IF(ISNUMBER(MATCH([@Email],Table2[[Group Name ]])),"Match","No Match")

2. Splitting column E (VideoTracking[Full Name]) into column M (VideoTracking[First Name]) - this causes an issue if there are two first names (e.g., Sue Ellen)

=TEXTBEFORE([@[Full Name]]," ")

3. and column N (VideoTracking[Last Name]

=TEXTAFTER([@[Full Name]]," ")

4. Matching column M (VideoTracking[First Name]) to column A (Sheet1[First Name ])

=IF(ISNUMBER(MATCH([@[First Name]],Table2[[First Name ]])),"Match","No Match")

Unfortunately, my XLSBB add-in isn't working, even after uninstalling and reinstalling, so I'm pasting in my sample data.

File Name: Simple Portal Check Status Spreadsheet 5-15-23.xlsx
Sheet Name: Sheet1
Table Name: Table 2
Columns: A to I
Rows: 1 to 5
Cell Formulas: None
Cells with Data Validation:

H2 - Yes,No​

H3 - Yes,No​

H4 - Yes,No​

H5 - Yes,No​

I2 - Active,Un-Active,Delete​

I3 - Active,Un-Active,Delete​

I4 - Active,Un-Active,Delete​

I5 - Active,Un-Active,Delete​

First Name	Middle Name	Last Name	Group Name	Group TIN	Provider Email	Provider NPI	Completed Requirements	Status
John		Smith	Office Bldg 1	123456789​	John.Smith@noemail.com	9876543210​	No	Active
Jane	A.	Johnson	Office Bldg 2	123456789​	Jjohnson@yesemail.org	9876543210​	No	Active
Mary	C	Jones	Office Bldg 3	123456789​	MCJones@newemail.net	9876543210​	No	Active
Sue Ellen	R	Williams	Office Bldg 4	123456789​	SueEllen.Williams@noemail.com	9876543210​	No	Active

File Name: Simple Portal Check Status Spreadsheet 5-15-23.xlsx
Sheet Name: VideoTracking
Table Name: Table1
Columns: A to Q
Rows: 1 to 5
Cell Formulas:

L2 - =IF(ISNUMBER(MATCH([@Email],Table2[[Group Name ]])),"Match","No Match")​

L3 - =IF(ISNUMBER(MATCH([@Email],Table2[[Group Name ]])),"Match","No Match")​

L4 - =IF(ISNUMBER(MATCH([@Email],Table2[[Group Name ]])),"Match","No Match")​

L5 - =IF(ISNUMBER(MATCH([@Email],Table2[[Group Name ]])),"Match","No Match")​

M2 - =TEXTBEFORE([@[Full Name]]," ")​

M3 - =TEXTBEFORE([@[Full Name]]," ")​

M4 - =TEXTBEFORE([@[Full Name]]," ")​

M5 - =TEXTBEFORE([@[Full Name]]," ")​

N1 - =TEXTAFTER([@[Full Name]]," ")​

N2 - =TEXTAFTER([@[Full Name]]," ")​

N3 - =TEXTAFTER([@[Full Name]]," ")​

N4 - =TEXTAFTER([@[Full Name]]," ")​

O2 - =IF(ISNUMBER(MATCH([@[First Name]],Table2[[First Name ]])),"Match","No Match")​

O3 - =IF(ISNUMBER(MATCH([@[First Name]],Table2[[First Name ]])),"Match","No Match")​

O4 - =IF(ISNUMBER(MATCH([@[First Name]],Table2[[First Name ]])),"Match","No Match")​

O5 - =IF(ISNUMBER(MATCH([@[First Name]],Table2[[First Name ]])),"Match","No Match")​

P2 - =IF(ISNUMBER(MATCH([@[First Name]],Table2[[Last Name ]])),"Match","No Match")​

P3 - =IF(ISNUMBER(MATCH([@[First Name]],Table2[[Last Name ]])),"Match","No Match")​

P4 - =IF(ISNUMBER(MATCH([@[First Name]],Table2[[Last Name ]])),"Match","No Match")​

P5 - =IF(ISNUMBER(MATCH([@[First Name]],Table2[[Last Name ]])),"Match","No Match")​

Cells with Data Validation: None

Tracking ID

Case ID

Case Title

User ID

Full Name

Email

Insert Date

Last Watched

Coverage

Video Length (hh:mm:ss)

Watch Time (hh:mm:ss)

Email Match

First Name

Last Name

First Name Match

Last Name Match

No Matches Found

29545​

1110​

Morbid Obesity

31036​

John Smith

John.Smith@noemail.com

5/15/2023 10:37​

5/15/2023 10:37​

100.000%

00:30:11

00:30:11

No Match

John

Smith

Match

No Match

29546​

1110​

Morbid Obesity

30452​

Jane Johnson

JaneJohnson@noemail.com

5/15/2023 10:38​

5/15/2023 10:38​

100.000%

00:30:11

00:30:11

No Match

Jane

Johnson

Match

No Match

29565​

1110​

Morbid Obesity

30409​

Mary Jones

MCJones@newemail.net

5/15/2023 11:22​

5/15/2023 11:22​

100.000%

00:30:11

00:30:11

No Match

Mary

Jones

Match

Match

29571​

1110​

Morbid Obesity

30668​

Sue Ellen Williams

SueEllen.Williams@noemail.com

5/15/2023 11:44​

5/15/2023 11:44​

100.000%

00:30:11

00:30:11

Match

Sue

Ellen Williams

Match

Match

The helper columns are needed unless they are for the solution. I've been trying to figure this out for a while now to no avail. Any help would be greatly appreciated. Thanks!!

 

[jessebh2003]

Thanks. The formulas gave me syntax errors, so I tried to update them but they're returning some correct data and some incorrect data. For instance:

In Table1, L2 has the formula

=IF(OR(XLOOKUP(Table2[@[Provider Email ]],[Email],[Coverage],0)>=90%,XLOOKUP(Table2[@[First Name ]]&" "&Table2[@[Last Name ]],[Full Name],[Coverage],0)>=90%),"Yes","No")

Tracking ID	Case ID	Case Title	User ID	Full Name	Email	Insert Date	Last Watched	Coverage	Video Length (hh:mm:ss	Watch time (hh:mm:ss)	Match Found
12345​	1110​	Morbid Obesity	12345​	Jane Smith	Jane.Smith@noemail.com	5/15/2023 22:21​	5/15/2023 22:21​	100.000%	00:30:11	00:30:11	No
67890​	1110​	Morbid Obesity	67890​	John Doe	John.Doe@noemail.com	5/18/2023 22:47​	5/18/2023 22:47​	100.000%	00:30:11	00:30:11	Yes

In Table2, H2 has the formula

=IF(OR(ISNUMBER(XMATCH(Table1[@Email],[[Provider Email ]])),ISNUMBER(XMATCH(Table1[@[Full Name]],[[First Name ]]&" "&[[Last Name ]]))),"Yes","No")

First Name	Middle Name	Last Name	Group Name	Group TIN	Provider Email	Provider NPI	Completed Requirements	Status
Jane		Smith	Bldg 1	123456789	Jane.smith@noemail.com	1234567890	No	Active
John		Doe	Bldg 2	987654321	johndoe@gmail.com	0987654321	Yes	Active

However, both formulas for Jane Smith return "No" instead of "Yes," John Doe they return "Yes" instead of "No." Am I doing something wrong? I tried flipping "Yes" and "No" in the formulas but that doesn't give me the correct responses either.

 

[Fluff]

I get Yes for Jane Smith

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	First Name	Middle Name	Last Name	Group Name	Group TIN	Provider Email	Provider NPI	Completed Requirements	Status
2	John		Smith	Office Bldg 1	123456789	John.Smith@noemail.com	9876543210	Yes	Active
3	Jane	A.	Johnson	Office Bldg 2	123456789	Jjohnson@yesemail.org	9876543210	Yes	Active
4	Mary	C	Jones	Office Bldg 3	123456789	MCJones@newemail.net	9876543210	Yes	Active
5	Sue Ellen	R	Williams	Office Bldg 4	123456789	SueEllen.Williams@noemail.com	9876543210	Yes	Active
6	Jane		Smith	Bldg 1	123456789	Jane.smith@noemail.com	1234567890	Yes	Active
7	John		Doe	Bldg 2	987654321	johndoe@gmail.com	987654321	Yes	Active
Main

Cell Formulas
Range	Formula
H2:H7	H2	=IF(OR(XLOOKUP([@[Provider Email]],Table1[Email],Table1[Coverage],0)>=90%,XLOOKUP([@[First Name]]&" "&[@[Last Name]],Table1[Full Name],Table1[Coverage],0)>=90%),"Yes","No")

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Tracking ID	Case ID	Case Title	User ID	Full Name	Email	Insert Date	Last Watched	Coverage	Video Length (hh:mm:ss)	Watch Time (hh:mm:ss)	Column1
2	29545	1110	Morbid Obesity	31036	John Smith	John.Smith@noemail.com	5/15/2023 10:37	5/15/2023 10:37	100.00%	00:30:11	00:30:11	Yes
3	29546	1110	Morbid Obesity	30452	Jane Johnson	JaneJohnson@noemail.com	5/15/2023 10:38	5/15/2023 10:38	100.00%	00:30:11	00:30:11	Yes
4	29565	1110	Morbid Obesity	30409	Mary Jones	MCJones@newemail.net	5/15/2023 11:22	5/15/2023 11:22	95.00%	00:30:11	00:30:11	Yes
5	29571	1110	Morbid Obesity	30668	Sue Ellen Williams	sueellen.williams@noemail.com	5/15/2023 11:44	5/15/2023 11:44	100.00%	00:30:11	00:30:11	Yes
6	12345	1110	Morbid Obesity	12345	Jane Smith	Jane.Smith@noemail.com	5/15/2023 22:21	5/15/2023 22:21	100.00%	00:30:11	00:30:11	Yes
7	67890	1110	Morbid Obesity	67890	John Doe	John.Doe@noemail.com	5/18/2023 22:47	5/18/2023 22:47	100.00%	00:30:11	00:30:11	Yes
Master

Cell Formulas
Range	Formula
L2:L7	L2	=IF(OR(ISNUMBER(XMATCH([@Email],Table2[Provider Email])),ISNUMBER(XMATCH([@[Full Name]],Table2[First Name]&" "&Table2[Last Name]))),"Yes","No")

Why should John Doe be NO as the name matches?