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:

Hotel Name	BRAND
Crowne Plaza Danbury <colgroup><col></colgroup><tbody> </tbody>	TRYING TO FILL IN
Holiday Inn Bridgeport-Trumbull-Fairfield <colgroup><col></colgroup><tbody> </tbody>
Best Western Plus Fairfield Hotel <colgroup><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

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

Edition	Marriott International
JW Marriott	Marriott International
Luxury Collection	Marriott International
Ritz-Carlton	Marriott International
St Regis	Marriott International
W Hotel	Marriott International
Autograph Collection	Marriott International
Delta Hotel	Marriott International
Gaylord	Marriott International
Le Meridien	Marriott International
Marriott	Marriott International
Marriott Conference Center	Marriott International
Renaissance	Marriott International
Sheraton Hotel	Marriott International
Tribute Portfolio	Marriott International
Westin	Marriott International
AC Hotels by Marriott	Marriott International
aloft Hotel	Marriott International
Courtyard	Marriott International
element	Marriott International
Four Points by Sheraton	Marriott International
Residence Inn	Marriott International
Springhill Suites	Marriott International
Fairfield Inn	Marriott International
MOXY	Marriott International
TownePlace Suites	Marriott International
Dolce Hotels & Resorts	Wyndham Worldwide
Wyndham Grand Hotels	Wyndham Worldwide
Wyndham	Wyndham Worldwide
Tryp by Wyndham	Wyndham Worldwide
Wyndham Garden Hotel	Wyndham Worldwide

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

 

[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:

Best Western Premier	Best Western Hotels & Resorts
BW Premier Collection	Best Western Hotels & Resorts
Best Western Plus	Best Western Hotels & Resorts
Best Western	Best Western Hotels & Resorts
SureStay Plus	Best Western Hotels & Resorts
SureStay Plus	Best Western Hotels & Resorts

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

 

[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.