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
[TABLE="width: 391"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Deposit[/TD]
[TD]Checkin[/TD]
[TD]End Date[/TD]
[TD]Guest Name[/TD]
[TD](Desired outcome)[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]8/12/17[/TD]
[TD]8/19/17[/TD]
[TD]Smith, John[/TD]
[TD]no match[/TD]
[/TR]
[TR]
[TD]67589[/TD]
[TD]8/12/17[/TD]
[TD]8/19/17[/TD]
[TD]Manes, Ted[/TD]
[TD]match[/TD]
[/TR]
[TR]
[TD]58791[/TD]
[TD]8/12/17[/TD]
[TD]8/19/17[/TD]
[TD]Hall, Fred[/TD]
[TD]match[/TD]
[/TR]
[TR]
[TD]32567[/TD]
[TD]8/12/17[/TD]
[TD]8/19/17[/TD]
[TD]Jones, Bill[/TD]
[TD]match[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]RST ID[/TD]
[TD]ResID[/TD]
[TD]ArrivalDate[/TD]
[TD]DepartureDate[/TD]
[TD]GuestName[/TD]
[/TR]
[TR]
[TD]TST[/TD]
[TD]554466[/TD]
[TD]8/12/17[/TD]
[TD]8/19/17[/TD]
[TD]Manes, Ted & Jill
[/TD]
[/TR]
[TR]
[TD]TST[/TD]
[TD]889977[/TD]
[TD]8/12/17[/TD]
[TD]8/19/17[/TD]
[TD]Hall, Frederick
[/TD]
[/TR]
[TR]
[TD]TST[/TD]
[TD]789103[/TD]
[TD]8/12/17[/TD]
[TD]8/19/17[/TD]
[TD]Jones, Bill
[/TD]
[/TR]
[TR]
[TD]TST[/TD]
[TD]249789[/TD]
[TD]8/12/17[/TD]
[TD]8/19/17[/TD]
[TD]Gerry, Linda
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 [TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"] =IF(ISNUMBER(LOOKUP(99^99,SEARCH("*"&E2&"*",$D$8:$D$11))),"match","no match")[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 429"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]RST ID[/TD]
[TD]ResID[/TD]
[TD]Arrival Date[/TD]
[TD]Departure Date[/TD]
[TD]GuestName[/TD]
[/TR]
[TR]
[TD]TST[/TD]
[TD="align: right"]554466[/TD]
[TD="align: right"]8/12/2017[/TD]
[TD="align: right"]8/19/2017[/TD]
[TD]Manes, Ted & Jill[/TD]
[/TR]
[TR]
[TD]TST[/TD]
[TD="align: right"]889977[/TD]
[TD="align: right"]8/12/2017[/TD]
[TD="align: right"]8/19/2017[/TD]
[TD]Hall, Fred[/TD]
[/TR]
[TR]
[TD]TST[/TD]
[TD="align: right"]789103[/TD]
[TD="align: right"]8/12/2017[/TD]
[TD="align: right"]8/19/2017[/TD]
[TD]Jones, Bill[/TD]
[/TR]
[TR]
[TD]TST[/TD]
[TD="align: right"]249789[/TD]
[TD="align: right"]8/12/2017[/TD]
[TD="align: right"]8/19/2017[/TD]
[TD]Gerry, Linda[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deposit[/TD]
[TD]Checkin[/TD]
[TD]End Date[/TD]
[TD]Guest Name[/TD]
[TD]Desired Outcome[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD="align: right"]8/12/2017[/TD]
[TD="align: right"]8/19/2017[/TD]
[TD]Smith, John[/TD]
[TD]no match[/TD]
[/TR]
[TR]
[TD="align: right"]67589[/TD]
[TD="align: right"]8/12/2017[/TD]
[TD="align: right"]8/19/2017[/TD]
[TD]Manes, Ted[/TD]
[TD]match[/TD]
[/TR]
[TR]
[TD="align: right"]58791[/TD]
[TD="align: right"]8/12/2017[/TD]
[TD="align: right"]8/19/2017[/TD]
[TD]Hall, Fred[/TD]
[TD]match[/TD]
[/TR]
[TR]
[TD="align: right"]32567[/TD]
[TD="align: right"]8/12/2017[/TD]
[TD="align: right"]8/19/2017[/TD]
[TD]Jones, Bill[/TD]
[TD]no match[/TD]
[/TR]
</tbody>[/TABLE]

 

[sadistik9]

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