Checking Names from a list on another TAB

[maddmann]

I am having an issue with excel. I have racked my brain with all the tricks I can think of and I am stuck. For a little back story, I work at a shelter and we have a list where we input guests that come in for dinner. My staff inputs whole names like "John Doe". I have another tab where I have a list of guests that have been separated from the facility. This list has a first name column and a last name column. It is structured this was for easier usability. I want to check the name that is inputted from, let's say, cell H69 on Tab 1 and check it against the list on Tab 2 and highlight the name on tab 1 if there is a match. I am using conditional formatting to accomplish this. The is the currently formula that I am using "=COUNT(SEARCH('Alpha Separated-Trespass List'!$B$2:$B$107, $H69))". The problem I am having is that it is only checking the last name. I have tried adding and IF in there and essentially copying the code to add the first name column to no avail.

Secondary issue I am having, the list on tab 2 is ever changing. People being added or removed. The above code does not like blank spaces either. I would like it to check a list that is going to grow without me manually changing the coding.

Please help me before I light something on fire. Thank you for your time and consideration!

 

[kevin9999]

See if this points you in the right direction:

Book1
	A
1	Full name
2	John Jones
3	Ann Smith
4	Amy Wills
5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=COUNTIFS(Sheet2!$A:$A,TEXTBEFORE(A2," "),Sheet2!$B:$B,TEXTAFTER(A2," "))>0	text	YES

Book1
	A	B
1	First Name	Last Name
2	John	Jones
3	John	Smith
4	Amy	Wills
5
6
Sheet2

 

[maddmann]

See if this points you in the right direction:

Book1
	A
1	Full name
2	John Jones
3	Ann Smith
4	Amy Wills
5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=COUNTIFS(Sheet2!$A:$A,TEXTBEFORE(A2," "),Sheet2!$B:$B,TEXTAFTER(A2," "))>0	text	YES

Book1
	A	B
1	First Name	Last Name
2	John	Jones
3	John	Smith
4	Amy	Wills
5
6
Sheet2

Kevin, that definitely did the trick, thank you! If I have anymore issues with this I will definitly post another comment

 

[kevin9999]

Happy to help, and thanks for the feedback