I'm lost with a Google Sheets function

Question

I have a column named "Trip", in which I have infos such as the hotel, country, and type of trip. The thing is these infos are hand typed, so sometimes instead of typing "HOTEL ABC", the person entering the infos simply enters "ABC", or event "HTL ABC". Also, ABC can also refer to a country.

What I want is to extract all hotel names on each cell. Here's the function I created, but I can't get it to work. (Y2 is the TRIP Column)

=IFS(AND(REGEXMATCH(Y2; "ZAHIR");REGEXMATCH(Y2; "LODGE"));"ZAHIR LODGE"; AND(REGEXMATCH(Y2; "ZAHIR");REGEXMATCH(Y2; "ILE"));"ZAHIR DE L'ILE";AND(REGEXMATCH(Y2; "ARC");REGEXMATCH(Y2; "CIEL"));"ARC EN CIEL";REGEXMATCH(Y2; "NOSY"); "NOSY BE";REGEXMATCH(Y2;"ANTOREM");"ANTOREMBA LODGE"; REGEXMATCH(Y2;"VANILLE"); "JARDIN VANILLE"; REGEXMATCH(Y2;"SAKAT");"SAKATIA LODGE"; REGEXMATCH(Y2;"SAUVAGE");"NATURE SAUVAGE";REGEXMATCH(Y2;"ANDILA");"ANDILANA"; REGEXMATCH(Y2;"AMARI"); "AMARINA";REGEXMATCH(Y2;"NOIR") "CORAIL NOIR"; REGEXMATCH(Y2; "LOHA") "LOHARANO" ;REGEXMATCH(Y2;"ROYAL");"ROYAL BEACH";REGEXMATCH(Y2;"VANILA"); "VANILA";REGEXMATCH(Y2;"HEUR");"HEURE BLEUE"; REGEXMATCH(Y2;"RAVI"); "RAVINTSARA";REGEXMATCH(Y2;"RESIDENCE"); "THE RESIDENCE";REGEXMATCH(Y2;"KOMBA LODGE"); "TSARA KOMBA LODGE";REGEXMATCH(Y2;"PALUMBO REEF"); "PALUMBO REEF"; REGEXMATCH(Y2;"PALUMBO KENDWA"); "PALUMBO KENDWA";AND(REGEXMATCH(Y2; "BLUE");REGEXMATCH(Y2; "MARLIN"));"BLUEMARLIN";AND(REGEXMATCH(Y2; "MY");REGEXMATCH(Y2; "BLU"));"MY BLUE";REGEXMATCH(Y2;"MVUVI"); "MVUVI";REGEXMATCH(Y2;"PALUMBO REEF"); "PALUMBO REEF";REGEXMATCH(Y2;"RAFU"); "KARAFUU";REGEXMATCH(Y2;"PARADI"); "NEXT PARADISE";REGEXMATCH(Y2;"KINASI"); "KINASI"; REGEXMATCH(Y2;"PALACE");"RIU PALACE";REGEXMATCH(Y2;"MANTA"); "MANTA RESORT";REGEXMATCH(Y2;"ZURI"); "ZURI RESORT";AND(REGEXMATCH(Y2; "EDEN");REGEXMATCH(Y2; "KENDWA"));"EDEN KENDWA";REGEXMATCH(Y2;"KIWENGWA"); "KIWENGWA BEACH RESORT";REGEXMATCH(Y2;"PAJE"); "PAJE PALMS BEACH RESORT";REGEXMATCH(Y2; "SUNSHINE"); "SUNSHINE MARINE LODGE";REGEXMATCH(Y2;"HAKUNA"); "HAKUNA MAJIWE BEACH RESORT";REGEXMATCH(Y2;"MAJIWE"); "HAKUNA MAJIWE BEACH RESORT";REGEXMATCH(Y2;"BAOBAB"); "BAOBAB BEACH RESORT";REGEXMATCH(Y2;"JACAR"); "JACARANDA";REGEXMATCH(Y2;"GARODA"); "GARODA";REGEXMATCH(Y2;"MAWE"); "MAWE";REGEXMATCH(Y2;"TWIGA"); "TWIGA";REGEXMATCH(Y2;"RACUDA"); "BARRACUDA INN";REGEXMATCH(Y2;"ACQUA"); "ACQUARIUS";REGEXMATCH(Y2;"WATAM"); "EDEN WATAMU BEACH"; REGEXMATCH(Y2;"LONNO"); "LONNO LODGE"; REGEXMATCH(Y2;"ALAWI"); "ALAWI BOUTIQUE RESORT"; REGEXMATCH(Y2;"KOBE"); "KOBE RESORT"; REGEXMATCH(Y2;"SUN PALM"); "SUN PALM RESORT"; REGEXMATCH(Y2;"CAROL"); "VILLAS CAROLINE"; REGEXMATCH(Y2;"CORAL"); "CORAL AZUR"; REGEXMATCH(Y2;"RADISSON"); "RADISSON BLUPOSTE"; REGEXMATCH(Y2;"RECIF"); "RECIF ATTITUDE"; REGEXMATCH(Y2;"FRIDAY"); "FRIDAY ATTITUDE";)

I've been working on this table for 2 days now and just can't find the answer.

Thanks!

Answer 1

1. You are missing semicolons before "CORAIL NOIR" and "LOHARANO" .
2. You need to remove the final semicolon after "FRIDAY ATTITUDE" because IFS expects all arguments after position 0 to be in pairs and the final semicolon makes Google Sheets think another set of arguments is coming.
3. You can help yourself out with future troubleshooting by spacing out your function better - eg the following will still work when pasted into Google Sheets:

=IFS(
AND(REGEXMATCH(Y2;"ZAHIR");REGEXMATCH(Y2;"LODGE"));"ZAHIR LODGE";
AND(REGEXMATCH(Y2;"ZAHIR");REGEXMATCH(Y2;"ILE"));"ZAHIR DE L'ILE";
AND(REGEXMATCH(Y2;"ARC");REGEXMATCH(Y2;"CIEL"));"ARC EN CIEL";
REGEXMATCH(Y2;"NOSY"); "NOSY BE";
REGEXMATCH(Y2;"ANTOREM");"ANTOREMBA LODGE"; 
REGEXMATCH(Y2;"VANILLE"); "JARDIN VANILLE"; 
REGEXMATCH(Y2;"SAKAT"); "SAKATIA LODGE"; 
REGEXMATCH(Y2;"SAUVAGE"); "NATURE SAUVAGE";
REGEXMATCH(Y2;"ANDILA"); "ANDILANA"; 
REGEXMATCH(Y2;"AMARI"); "AMARINA";
REGEXMATCH(Y2;"NOIR"); "CORAIL NOIR"; 
REGEXMATCH(Y2;"LOHA"); "LOHARANO";
REGEXMATCH(Y2;"ROYAL");"ROYAL BEACH";
REGEXMATCH(Y2;"VANILA"); "VANILA";
REGEXMATCH(Y2;"HEUR"); "HEURE BLEUE"; 
REGEXMATCH(Y2;"RAVI"); "RAVINTSARA";
REGEXMATCH(Y2;"RESIDENCE"); "THE RESIDENCE";
REGEXMATCH(Y2;"KOMBA LODGE"); "TSARA KOMBA LODGE";
REGEXMATCH(Y2;"PALUMBO REEF"); "PALUMBO REEF"; 
REGEXMATCH(Y2;"PALUMBO KENDWA"); "PALUMBO KENDWA";
AND(REGEXMATCH(Y2;"BLUE");REGEXMATCH(Y2;"MARLIN")); "BLUEMARLIN";
AND(REGEXMATCH(Y2;"MY");REGEXMATCH(Y2;"BLU")); "MY BLUE";
REGEXMATCH(Y2;"MVUVI"); "MVUVI";
REGEXMATCH(Y2;"PALUMBO REEF"); "PALUMBO REEF";
REGEXMATCH(Y2;"RAFU"); "KARAFUU";
REGEXMATCH(Y2;"PARADI"); "NEXT PARADISE";
REGEXMATCH(Y2;"KINASI"); "KINASI"; 
REGEXMATCH(Y2;"PALACE"); "RIU PALACE";
REGEXMATCH(Y2;"MANTA"); "MANTA RESORT";
REGEXMATCH(Y2;"ZURI"); "ZURI RESORT";
AND(REGEXMATCH(Y2;"EDEN");REGEXMATCH(Y2;"KENDWA")); "EDEN KENDWA";
REGEXMATCH(Y2;"KIWENGWA"); "KIWENGWA BEACH RESORT";
REGEXMATCH(Y2;"PAJE"); "PAJE PALMS BEACH RESORT";
REGEXMATCH(Y2;"SUNSHINE"); "SUNSHINE MARINE LODGE";
REGEXMATCH(Y2;"HAKUNA"); "HAKUNA MAJIWE BEACH RESORT";
REGEXMATCH(Y2;"MAJIWE"); "HAKUNA MAJIWE BEACH RESORT";
REGEXMATCH(Y2;"BAOBAB"); "BAOBAB BEACH RESORT";
REGEXMATCH(Y2;"JACAR"); "JACARANDA";
REGEXMATCH(Y2;"GARODA"); "GARODA";
REGEXMATCH(Y2;"MAWE"); "MAWE";
REGEXMATCH(Y2;"TWIGA"); "TWIGA";
REGEXMATCH(Y2;"RACUDA"); "BARRACUDA INN";
REGEXMATCH(Y2;"ACQUA"); "ACQUARIUS";
REGEXMATCH(Y2;"WATAM"); "EDEN WATAMU BEACH"; 
REGEXMATCH(Y2;"LONNO"); "LONNO LODGE"; 
REGEXMATCH(Y2;"ALAWI"); "ALAWI BOUTIQUE RESORT"; 
REGEXMATCH(Y2;"KOBE"); "KOBE RESORT"; 
REGEXMATCH(Y2;"SUN PALM"); "SUN PALM RESORT"; 
REGEXMATCH(Y2;"CAROL"); "VILLAS CAROLINE"; 
REGEXMATCH(Y2;"CORAL"); "CORAL AZUR"; 
REGEXMATCH(Y2;"RADISSON"); "RADISSON BLUPOSTE"; 
REGEXMATCH(Y2;"RECIF"); "RECIF ATTITUDE"; 
REGEXMATCH(Y2;"FRIDAY"); "FRIDAY ATTITUDE"
)