Vlookup? Match? Index? My brain aint workin today!

[sadistik9]

Ok my brain is totally not working today and im having trouble figuring this out. I have 2 tabs with similar lists. Anything that is in Sheet 1 but NOT sheet 2 should say "no match" or whatever but the data might not be totally the same (i.e. Smith, John on Sheet 1 and Smith, John & Susan on Sheet 2 but that should still show a match). Any help would be greatly appreciated

Sheet 1

Deposit Checkin End Date Guest Name (Desired outcome) 12345 8/12/17 8/19/17 Smith, John no match 67589 8/12/17 8/19/17 Manes, Ted match 58791 8/12/17 8/19/17 Hall, Fred match 32567 8/12/17 8/19/17 Jones, Bill match <tbody> </tbody>

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

Sheet 2

RST ID	ResID	ArrivalDate	DepartureDate	GuestName
TST	554466	8/12/17	8/19/17	Manes, Ted & Jill
TST	889977	8/12/17	8/19/17	Hall, Frederick
TST	789103	8/12/17	8/19/17	Jones, Bill
TST	249789	8/12/17	8/19/17	Gerry, Linda

<tbody>
</tbody>

 

[Mike Szczesny]

Can you use something like this? Assume your ranges (including headings) are the following: Sheet2 (A7:E11) and Sheet1 (A1:E5). Place formula in E8

=IF(ISNUMBER(LOOKUP(99^99,SEARCH("*"&E2&"*",$D$8:$D$11))),"match","no match")

<tbody>
</tbody>

RST ID	ResID	Arrival Date	Departure Date	GuestName
TST	554466	8/12/2017	8/19/2017	Manes, Ted & Jill
TST	889977	8/12/2017	8/19/2017	Hall, Fred
TST	789103	8/12/2017	8/19/2017	Jones, Bill
TST	249789	8/12/2017	8/19/2017	Gerry, Linda
Deposit	Checkin	End Date	Guest Name	Desired Outcome
12345	8/12/2017	8/19/2017	Smith, John	no match
67589	8/12/2017	8/19/2017	Manes, Ted	match
58791	8/12/2017	8/19/2017	Hall, Fred	match
32567	8/12/2017	8/19/2017	Jones, Bill	no match

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sadistik9]

Wow thank you so much, there wasn't enough coffee in the world to get my brain wrapped around that!