INDEX Function

[limo2088]

Can anything be done with the fact that the names dont match exactly? So I have a formula which compares Column B with Column A, But as you can see some of the names dont exacly match. They have a middle name or a middle initial. Is there any way to circumvent this? Maybe just use the last name as a verifier.

Master_Schedule Final3.xlsx
	A	B	C
2	DA Logged Into Mentor	DA Working Today	Missing In Mentor
3	Dylan Stewart	Benjamin B Kratky	Benjamin B Kratky
4	Lee Donnelly	Brandon Brian Brownlee	Brandon Brian Brownlee
5	Jonathon Honaker	Cassidy Lynn Kaurich	Cassidy Lynn Kaurich
6	Quinton Clemons	Colton Cyclle Starcher	Colton Cyclle Starcher
7	Colton Starcher	DAsia Clodeen Moore	DAsia Clodeen Moore
8	Anita Russell	Jayda Marche Nix	Jayda Marche Nix
9	Anita Russell	Jeffrey Carl Othberg	Jeffrey Carl Othberg
10	Benjamin Kratky	Jessie Muheisien	Johnathan Terry McRae
11	Marlesha Berry	Johnathan Terry McRae	Jonathon Andrew Honaker
12	Jessie Muheisien	Jonathon Andrew Honaker	Kelvonne Aisha King
13	Cassidy Kaurich	Kelvonne Aisha King	LaShae Cherie Johnson
14	Brandon Brownlee	LaShae Cherie Johnson	Lauren Calloway
15	Ronald Knight	Lauren Calloway	Lawrence Phillip Lambert
16	Lawrence Lambert	Lawrence Phillip Lambert	Quinton Demoine Clemons
17	Jervon Austin	Marlesha Berry	Ronald Michael Knight
18	Andrew Pickett	Quinton Demoine Clemons	0
19	Johnathan MCRAE	Ronald Michael Knight	0
20	Lashae Johnson	0	0
21	Jayda Nix	0	0
22	Jeffrey Othberg	0
Mentor

Cell Formulas
Range	Formula
B3:B22	B3	='Manual Morning Schedule'!B3
C3	C3	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$50)/ISNA(MATCH(B$3:B$50,A$3:A$50,0)),ROWS(C$3:C3))),"")
C4:C22	C4	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$26)/ISNA(MATCH(B$3:B$26,A$3:A$23,0)),ROWS(C$3:C4))),"")

 

[bebo021999]

List of missing names:

Book2
	A	B	C
2	DA Logged Into Mentor	DA Working Today	Missing In Mentor
3	Dylan Stewart	Benjamin B Kratky	DAsia Clodeen Moore
4	Lee Donnelly	Brandon Brian Brownlee	Kelvonne Aisha King
5	Jonathon Honaker	Cassidy Lynn Kaurich	Lauren Calloway
6	Quinton Clemons	Colton Cyclle Starcher
7	Colton Starcher	DAsia Clodeen Moore
8	Anita Russell	Jayda Marche Nix
9	Anita Russell	Jeffrey Carl Othberg
10	Benjamin Kratky	Jessie Muheisien
11	Marlesha Berry	Johnathan Terry McRae
12	Jessie Muheisien	Jonathon Andrew Honaker
13	Cassidy Kaurich	Kelvonne Aisha King
14	Brandon Brownlee	LaShae Cherie Johnson
15	Ronald Knight	Lauren Calloway
16	Lawrence Lambert	Lawrence Phillip Lambert
17	Jervon Austin	Marlesha Berry
18	Andrew Pickett	Quinton Demoine Clemons
19	Johnathan MCRAE	Ronald Michael Knight
20	Lashae Johnson
21	Jayda Nix
22	Jeffrey Othberg
Sheet1

Cell Formulas
Range	Formula
C3:C22	C3	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$3:$B$19)/(COUNTIF($A$3:$A$22,LEFT($B$3:$B$19,SEARCH(" ",$B$3:$B$19))&TRIM(RIGHT(SUBSTITUTE($B$3:$B$19," ",REPT(" ",100)),100)))=0),ROW(1:1))),"")

 

[KansaiDorifto]

You could try comparing the first and last names of both columns, i.e.
First names of Column B
Last names of Column B
compare with
First names of Column C
Last names of Column C

also compare them by swapping first/last names.

Assuming the name is in cell C4:
To get the first names, you can use:
=+MID(C4,1,+FIND(" ", C4, 1)-1)
To get the last names, you can use:
=+RIGHT(C4, +LEN(C4)-+FIND("@", +SUBSTITUTE(C4, " ", "@", +LEN(C4)-LEN(SUBSTITUTE(C4," ",""))),1))

 

[limo2088]

List of missing names:

Book2
	A	B	C
2	DA Logged Into Mentor	DA Working Today	Missing In Mentor
3	Dylan Stewart	Benjamin B Kratky	DAsia Clodeen Moore
4	Lee Donnelly	Brandon Brian Brownlee	Kelvonne Aisha King
5	Jonathon Honaker	Cassidy Lynn Kaurich	Lauren Calloway
6	Quinton Clemons	Colton Cyclle Starcher
7	Colton Starcher	DAsia Clodeen Moore
8	Anita Russell	Jayda Marche Nix
9	Anita Russell	Jeffrey Carl Othberg
10	Benjamin Kratky	Jessie Muheisien
11	Marlesha Berry	Johnathan Terry McRae
12	Jessie Muheisien	Jonathon Andrew Honaker
13	Cassidy Kaurich	Kelvonne Aisha King
14	Brandon Brownlee	LaShae Cherie Johnson
15	Ronald Knight	Lauren Calloway
16	Lawrence Lambert	Lawrence Phillip Lambert
17	Jervon Austin	Marlesha Berry
18	Andrew Pickett	Quinton Demoine Clemons
19	Johnathan MCRAE	Ronald Michael Knight
20	Lashae Johnson
21	Jayda Nix
22	Jeffrey Othberg
Sheet1

Cell Formulas
Range	Formula
C3:C22	C3	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$3:$B$19)/(COUNTIF($A$3:$A$22,LEFT($B$3:$B$19,SEARCH(" ",$B$3:$B$19))&TRIM(RIGHT(SUBSTITUTE($B$3:$B$19," ",REPT(" ",100)),100)))=0),ROW(1:1))),"")

Fantastic, Thank you

 

[limo2088]

You could try comparing the first and last names of both columns, i.e.
First names of Column B
Last names of Column B
compare with
First names of Column C
Last names of Column C

also compare them by swapping first/last names.

Assuming the name is in cell C4:
To get the first names, you can use:
=+MID(C4,1,+FIND(" ", C4, 1)-1)
To get the last names, you can use:
=+RIGHT(C4, +LEN(C4)-+FIND("@", +SUBSTITUTE(C4, " ", "@", +LEN(C4)-LEN(SUBSTITUTE(C4," ",""))),1))

I would have to always sort so its in alpabetical order correct? Because these don't always come in alphabetical order.