Best Match VLookup

[help1234]

I have a list of hotels that I am trying to categorize by their parent company (or list as "independent"). I have a list of the names of each brand and their parent company but some hotels are iterations of the brand name ie. Courtyard Marriott (brand) but the hotel name would be Boston Courtyard Marriott.

Is there an easy formula that I could use?

This is what I am trying to full in below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Hotel Name[/TD]
[TD]BRAND[/TD]
[/TR]
[TR]
[TD][TABLE="width: 413"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Crowne Plaza Danbury[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRYING TO FILL IN[/TD]
[/TR]
[TR]
[TD][TABLE="width: 413"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Holiday Inn Bridgeport-Trumbull-Fairfield[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 413"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Best Western Plus Fairfield Hotel[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have a Brand data table/ list that looks like this:

[TABLE="class: grid, width: 445"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Edition[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]JW Marriott[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Luxury Collection[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Ritz-Carlton[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]St Regis[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]W Hotel[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Autograph Collection[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Delta Hotel[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Gaylord[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Le Meridien[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Marriott[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Marriott Conference Center[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Renaissance[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Sheraton Hotel[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Tribute Portfolio[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Westin[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]AC Hotels by Marriott[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]aloft Hotel[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Courtyard[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]element[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Four Points by Sheraton[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Residence Inn[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Springhill Suites[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Fairfield Inn[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]MOXY[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]TownePlace Suites[/TD]
[TD]Marriott International[/TD]
[/TR]
[TR]
[TD]Dolce Hotels & Resorts[/TD]
[TD]Wyndham Worldwide[/TD]
[/TR]
[TR]
[TD]Wyndham Grand Hotels[/TD]
[TD]Wyndham Worldwide[/TD]
[/TR]
[TR]
[TD]Wyndham[/TD]
[TD]Wyndham Worldwide[/TD]
[/TR]
[TR]
[TD]Tryp by Wyndham[/TD]
[TD]Wyndham Worldwide[/TD]
[/TR]
[TR]
[TD]Wyndham Garden Hotel[/TD]
[TD]Wyndham Worldwide[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What are desired results in the upper table?

 

[help1234]

I am hoping to get column 2 of the bottom table (the table has 349 rows), ie. from table 1, Best Western Plus Fairfield Hotel the second column returns "Best Western Hotels and resorts"

This is a continuation of that second table on the initial posting:

[TABLE="width: 508"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Best Western Premier[/TD]
[TD]Best Western Hotels & Resorts[/TD]
[/TR]
[TR]
[TD]BW Premier Collection[/TD]
[TD]Best Western Hotels & Resorts[/TD]
[/TR]
[TR]
[TD]Best Western Plus[/TD]
[TD]Best Western Hotels & Resorts[/TD]
[/TR]
[TR]
[TD]Best Western[/TD]
[TD]Best Western Hotels & Resorts[/TD]
[/TR]
[TR]
[TD]SureStay Plus[/TD]
[TD]Best Western Hotels & Resorts[/TD]
[/TR]
[TR]
[TD]SureStay Plus[/TD]
[TD]Best Western Hotels & Resorts[/TD]
[/TR]
</tbody>[/TABLE]

 

[help1234]

I have tried the formula below but it isn't producing the correct results:
=VLOOKUP(A2&"*",Brands,2,TRUE)

 

[Aladin Akyurek]

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Editions&" "," "&$A2&" "),Brands)

where A2 is Crowne Plaza Danbury, for example.

 

[help1234]

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Editions&" "," "&$A2&" "),Brands)

where A2 is Crowne Plaza Danbury, for example.

This formula doesn't seem to be working, its giving me a "#Name?" error

 

[Aladin Akyurek]

This formula doesn't seem to be working, its giving me a "#Name?" error

You need to define both Editions and Brands. The first is all of the cells under the header Edition, Brands all of the cells under the header Brand.

 

[help1234]

You need to define both Editions and Brands. The first is all of the cells under the header Edition, Brands all of the cells under the header Brand.

Aladin, Thank you so much for your help!!! The formula worked and saved me A TON of time. Really appreciate it!

 

[Aladin Akyurek]

Aladin, Thank you so much for your help!!! The formula worked and saved me A TON of time. Really appreciate it!

Glad to help. Thanks for the update.