Skip to main content

New User - need help with either IF or SWITCH formula

T
Forum|alt.badge.img+6
  • TRBS
  • Known Participant
  • 11 replies

Hi there,

I am trying to attempt to add a formula, that when a certain hotel name is entered in column A, then in column B, depending on that hotel name, it adds the hotel brand automatically. For example:

If Hotel Name is Hyatt Centric, then Hotel Brand is Hyatt. I can get this formula to work: 

SWITCH("Hyatt Centric", "Hyatt")

But my question is can I/how do I have multiple Hotel Names in one hotel brand? For example:

If hotel name is Hyatt Centric, Hyatt House, Hyatt Place or Hyatt Regency in column A, then Hotel Brand is Hyatt in column B.

How do I write the switch formula to have multiple options?

Thanks!

 

 

CJSmith
1 person likes this
  • CJSmith
    CJSmith

5 replies

CJSmith
Forum|alt.badge.img+12
  • CJSmith
  • Participating Frequently
  • 42 replies
  • July 18, 2023

Hi @TRBS, what you're trying to do is certainly achievable in Airtable with the switch operator. Your formula isn't working because it's incomplete (linking the support page for detailed information). 

Essentially, a successful switch formula will include:

  1. the field to reference
  2. a list of patterns (in this case, the patterns will be the hotel names)
  3. the corresponding result for each pattern (the hotel brands)
  4. a default result if none of the patterns are matched

The formula, then, would look like this:

SWITCH( {Column A/Hotel Name}, "Hyatt Centric", "Hyatt", "Hyatt House", "Hyatt", "Hyatt Place", "Hyatt", "Sheraton Grand Los Angeles", "Marriot", "Sheraton Universal", "Marriot", BLANK() )

Each of the patterns (hotel names) have a corresponding result (brand), and if a pattern isn't recognized, the value will default to blank (that's what that "BLANK()" is doing at the end). Just keep adding patterns and results in the "pattern, result," grouping and you should be all set. Let me know if that works!

T
Forum|alt.badge.img+6
  • TRBS
  • Author
  • Known Participant
  • 11 replies
  • July 18, 2023
CJSmith wrote:

Hi @TRBS, what you're trying to do is certainly achievable in Airtable with the switch operator. Your formula isn't working because it's incomplete (linking the support page for detailed information). 

Essentially, a successful switch formula will include:

  1. the field to reference
  2. a list of patterns (in this case, the patterns will be the hotel names)
  3. the corresponding result for each pattern (the hotel brands)
  4. a default result if none of the patterns are matched

The formula, then, would look like this:

SWITCH( {Column A/Hotel Name}, "Hyatt Centric", "Hyatt", "Hyatt House", "Hyatt", "Hyatt Place", "Hyatt", "Sheraton Grand Los Angeles", "Marriot", "Sheraton Universal", "Marriot", BLANK() )

Each of the patterns (hotel names) have a corresponding result (brand), and if a pattern isn't recognized, the value will default to blank (that's what that "BLANK()" is doing at the end). Just keep adding patterns and results in the "pattern, result," grouping and you should be all set. Let me know if that works!


Hi! I think I am missing something - still getting an error:

SWITCH(
<Tab in 1>{Hotel Portfolio},
<Tab in 2 all below> "25 Hours", "Accor",
"Adagio", "Accor",
"Angsana", "Accor",
"Art Series", "Accor",
"Banyan Tree", "Accor",
"Delano", "Accor",
"Fairmont", "Accor",
"Grand Mercure", "Accor",
"Hotelf1", "Accor",
"Hyde", "Accor",
"Ibis", "Accor",
"Ibis Budget", "Accor",
"Ibis Styles", "Accor",
"Jo & JOE", "Accor",
"Mama Shelter", "Accor",
"Mercure", "Accor",
"Mgalleryx21C", "Accor",
"Mondrian", "Accor",
"Movenpick", "Accor",
"Novotel", "Accor",
"Onefinestay", "Accor",
"Orient Express", "Accor",
"Peppers", "Accor",
"Pullman", "Accor",
"Rixos", "Accor",
"SL/", "Accor",
"SLS", "Accor",
"Sofitel", "Accor",
"Sofitel Legend", "Accor",
"Swissotel", "Accor",
"The House of Originals", "Accor",
"The Sebel", "Accor",
"Tribe", "Accor",
"Breakfree", "Accor",
"Mantis", "Accor",
"Mantra", "Accor",
"Raffles", "Accor",
"Aiden", "Best Western Hotels",
"Best Western Plus", "Best Western Hotels",
"Best Western Premier", "Best Western Hotels",
"BW Signature Collection by Best Western", "Best Western Hotels",
"Executive Residency by Best Western", "Best Western Hotels",
"GLō", "Best Western Hotels",
"Sadie", "Best Western Hotels",
"SureStay Collection", "Best Western Hotels",
"SureStay Hotel", "Best Western Hotels",
"SureStay Plus Hotel", "Best Western Hotels",
"The BW Premier Collection by Best Western", "Best Western Hotels",
"Vīb", "Best Western Hotels",
"Best Western", "Best Western Hotels",
"Bally’s", "Caesars",
"Flamingo", "Caesars",
"Harrahs", "Caesars",
"Horseshoe", "Caesars",
"Nobu Hotel", "Caesars",
"Paris", "Caesars",
"Planet Hollywood", "Caesars",
"Rio", "Caesars",
"The Cromwell", "Caesars",
"The LINQ", "Caesars",
"Tunica Roadhouse", "Caesars",
"Caesars", "Caesars",
"Ascend Hotel Collection", "Choice Hotels",
"Cambria Hotels", "Choice Hotels",
"Clarion", "Choice Hotels",
"Clarion Pointe", "Choice Hotels",
"Comfort Suites", "Choice Hotels",
"Econo Lodge", "Choice Hotels",
"MainStay Suites", "Choice Hotels",
"Quality", "Choice Hotels",
"Rodeway Inn", "Choice Hotels",
"Sleep Inn", "Choice Hotels",
"Suburban", "Choice Hotels",
"WoodSpring Suites", "Choice Hotels",
"Comfort Inn", "Choice Hotels",
"Avid Hotels", "Hilton",
"Candlewood Suites", "Hilton",
"Canopy by Hilton", "Hilton",
"Conrad Hotels & Resorts", "Hilton",
"Crowne Plaza Hotels & Resorts", "Hilton",
"Curio Collection by Hilton", "Hilton",
"DoubleTree by Hilton", "Hilton",
"Embassy Suites by Hilton Hotels", "Hilton",
"EVEN Hotels", "Hilton",
"Hampton by Hilton", "Hilton",
"Hilton Garden Inn", "Hilton",
"Hilton Grand Vacations", "Hilton",
"Holiday Inn Club Vacations", "Hilton",
"Holiday Inn Express", "Hilton",
"Home2 Suites by Hilton", "Hilton",
"Homewood Suites by Hilton", "Hilton",
"Hotel Indigo", "Hilton",
"HUALUXX Hotels and Resorts", "Hilton",
"InterContinental Hotels", "Hilton",
"LXR Hotels & Resorts.", "Hilton",
"MOTTO", "Hilton",
"Regent Hotels & Resorts", "Hilton",
"Signia Hilton", "Hilton",
"Six Senses Hotels Resorts Spas", "Hilton",
"Staybridge Suites", "Hilton",
"Tapestry Collection by Hilton", "Hilton",
"Tru by Hilton", "Hilton",
"Waldorf Astoria Hotels & Resorts", "Hilton",
"Hilton Hotels & Resorts", "Hilton",
"Holiday Inn", "Hilton",
"Kimpton Hotel & Restaurants", "Hilton",
"VOCO Hotels", "Hilton",
"Alila", "Hyatt",
"Andaz", "Hyatt",
"Destination Hotels", "Hyatt",
"Exhale", "Hyatt",
"Grand Hyatt", "Hyatt",
"Hyatt", "Hyatt",
"Hyatt Centric", "Hyatt",
"Hyatt House", "Hyatt",
"Hyatt Place", "Hyatt",
"Hyatt Regency", "Hyatt",
"Hyatt Residence Club", "Hyatt",
"Hyatt Zilara", "Hyatt",
"Hyatt Ziva", "Hyatt",
"Joie de Vivre", "Hyatt",
"Miraval", "Hyatt",
"Park Hyatt", "Hyatt",
"The Unbound Collection by Hyatt", "Hyatt",
"Thompson Hotels", "Hyatt",
"Caption by Hyatt", "Hyatt",
"Avid Hotels", "IHG",
"Candlewood Suites", "IHG",
"Crowne Plaza Hotels & Resorts", "IHG",
"EVEN Hotels", "IHG",
"Holiday Inn Club Vacations", "IHG",
"Holiday Inn Express", "IHG",
"Hotel Indigo", "IHG",
"HUALUXX Hotels and Resorts", "IHG"
"InterContinental Hotels & Resorts", "IHG",
"Regent Hotels & Resorts", "IHG",
"Six Senses Hotels Resorts Spas", "IHG",
"Staybridge Suites", "IHG",
"Holiday Inn", "IHG",
"Kimpton Hotel & Restaurants", "IHG",
"VOCO Hotels", "IHG",
"AC Hotels Marriott", "Marriott",
"Aloft Hotels", "Marriott",
"Autograph Collection Hotels", "Marriott",
"Bvlgari Hotels & Resorts", "Marriott",
"Delta Hotels", "Marriott",
"Design Hotels", "Marriott",
"Element by Westin", "Marriott",
"Fairfield by Marriott", "Marriott",
"Four Points by Sheraton", "Marriott",
"Gaylord Hotels", "Marriott",
"JW Marriott", "Marriott",
"Le Meridien", "Marriott",
"Marriott", "Marriott",
"Marriott Executive Apartments", "Marriott",
"Marriott Vacation Club", "Marriott",
"Moxy Hotels", "Marriott",
"Protea Hotels Marriott", "Marriott",
"Renaissance Hotels", "Marriott",
"Springhill Suites by Marriott", "Marriott",
"St Regis Hotels & Resorts", "Marriott",
"The Luxury Collection", "Marriott",
"The Ritz-Carlton", "Marriott",
"Towneplace Suites By Marriott", "Marriott",
"Tribute Portfolio", "Marriott",
"W Hotels Worldwide", "Marriott",
"Westin Hotels & Resorts", "Marriott",
"Courtyard By Marriott", "Marriott",
"Edition", "Marriott",
"Residence Inn by Marriott", "Marriott",
"Sheraton", "Marriott",
"AmericInn", "Wyndham",
"Baymont", "Wyndham",
"Days Inn", "Wyndham",
"Dazzler by Wyndham", "Wyndham",
"Dolce Hotels and Resorts", "Wyndham",
"Esplendor Boutique Hotels by Wyndham", "Wyndham",
"Hawthorn Suites by Wyndham.", "Wyndham",
"Howard Johnson", "Wyndham",
"La Quinta", "Wyndham",
"Landal Greenprarks", "Wyndham",
"Margaritaville Vacation Club", "Wyndham",
"Microtel", "Wyndham",
"Ramada", "Wyndham",
"Shell Vacations Club", "Wyndham",
"Super 8", "Wyndham",
"Trademark Collection by Wyndham", "Wyndham",
"Travelodge", "Wyndham",
"TRYP", "Wyndham",
"Wingate", "Wyndham",
"Worldmark", "Wyndham",
"Wyndham", "Wyndham",
"Wyndham Garden", "Wyndham",
"Wyndham Vacation Rentals", "Wyndham",
"Club Wyndham", "Wyndham",
"Cottages", "Wyndham",
"Wyndham Grand", "Wyndham",
"Omni", "Omni",
"Independent", "Indy",
<Tab in 3>BLANK()
)
CJSmith
1 person likes this
  • CJSmith
    CJSmith
CJSmith
Forum|alt.badge.img+12
  • CJSmith
  • Participating Frequently
  • 42 replies
  • July 18, 2023

Hi @TRBS, the error is due to the IHG hotel names being duplicates of Hilton hotels. The pattern:result ration has to be 1:1, otherwise the formula doesn't know which one to resolve to. Also looks like a comma was missing after "HUALUXX Hotels and Resorts", "IHG", but the formula won't work until the duplicate issue is resolved. Hope this helps!

S
Forum|alt.badge.img+19
  • Sho
  • Inspiring
  • 560 replies
  • July 19, 2023

Is there a hotel table?
In this case, I would like to create a hotel table and create a select field for the brand and reference that in lookup.

This formula is difficult to manage

T
Forum|alt.badge.img+6
  • TRBS
  • Author
  • Known Participant
  • 11 replies
  • July 28, 2023
CJSmith wrote:

Hi @TRBS, the error is due to the IHG hotel names being duplicates of Hilton hotels. The pattern:result ration has to be 1:1, otherwise the formula doesn't know which one to resolve to. Also looks like a comma was missing after "HUALUXX Hotels and Resorts", "IHG", but the formula won't work until the duplicate issue is resolved. Hope this helps!


Thank you @CJSmith ! That worked - I had been staring at it too long and didn't see that.

Reply


Most helpful members this week

Terms & Conditions