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!!

 

[Fluff]

How about

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	75.00%	00:30:11	00:30:11	No
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
Master

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

 

[jessebh2003]

Thanks, Fluff! That worked great to identify the matches on the VideoTracking sheet, except it seems that the formula is case sensitive. When I tried it, it would find the match for SueEllen.Williams@noemail.com but not for sueellen.williams@noemail.com. Is there a way for it not check case sensative too?

Any thoughts about this part of the question?

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​

Really appreciate your help!!!

 

[Fluff]

Is there a way for it not check case sensative too?

That formula is not case sensitive.

Any thoughts about this part of the question?

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

It already does that, which is why L4 shows No.

 

[jessebh2003]

That formula is not case sensitive.

It already does that, which is why L4 shows No.

How does column H (Sheet1[Completed Requirements ]) of the matching name or email become Yes? Maybe I'm not understanding?

In my real data sheet, I'm getting lots of No's that should be Yes's and all of the names with more than one first name or last name (e.g., Sue Ellen Williams, Mary Smith Jones) are coming back No. I checked to make sure the names and emails matched and that there weren't any extra spaces.

 

[Fluff]

I thought you wanted the formula in table1 rather than table2

 

[jessebh2003]

I'm sorry if my explanation was confusing.

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

This part is on the first tab, Sheet1 in Table2

4. No matches found, cell in column Q (VideoTracking[No Matches Found]) is No Matches

This part is on the second tab, VideoTracking in Table1

 

[Fluff]

So you are looking for two formulae not one?

 

[jessebh2003]

Yes, sorry if that was confusing.

 

[Fluff]

In that case for table2 try

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

in Table1

=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")