Comparer le contenu d'une cellule à une liste prédéfinie

Bonjour à tous,

j'ai un fichier Google sheet avec 2 onglets :

- Le 1er onglet contient en colonne A une liste d'une centaine de mots clés divers et variés :

Quelques exemples des contenus des cellules:

inscription bts
bts Paris
marketing formation nantes
Brest inscription
Bac+2 meudon
Etc...

- Le 2ème onglet contient en colonne A une liste de villes

Paris
Nantes
Cholet
Rennes

Je souhaiterais pouvoir obtenir dans le 1er onglet, en colonne B, la ville éventuellement mentionnée dans la colonne A sur la base de la liste que j'ai indiqué dans le 2ème onglet.

J'ai déjà tenté avec des formules type =si(nb.si('zone de recherche';'valeur cherchée')>0;"OK";"KO") mais cela ne fonctionne pas car cette formule recherche une valeur de cellule strictement identique. Notez que la formule ne devra pas prendre la casse en considération.

Merci par avance pour votre aide,

Julien

Bonsoir,

Tu peux utiliser cette formule dans l'onglet "LISTE", cellule B1, pour arriver à tes fins :

=byrow(A1:A;lambda(Liste;if(and(Liste<>"";isna(QUERY(UPPER(Liste);"SELECT Col1 WHERE Col1 MATCHES "&ArrayFormula("'"&TEXTJOIN(".*|"; TRUE; ".*"&TOCOL(UPPER(query(VILLES!$A$1:$A;"select * where A is not null "));3))&".*'")&" "; 0)));"KO";if(Liste<>"";"OK";""))))

https://docs.google.com/spreadsheets/d/1jdD9f03cRmDiAQrPrcQcGnU3PaiAeljTsnL78z5yylE/edit?usp=sharing

Cordialement,

Fil.

Bonjour à tous,

Une autre proposition car j'aime bien ces petits problèmes, basé sur la feuille de filoche que je salue :

=LET(
    _descr; A1:A5;
    _villes; VILLES!A1:A5;
    IsInList; LAMBDA(_x; _list; ISNUMBER(XMATCH(_x; _list)));
    MatchByWord; LAMBDA(_txt; _list;
        LET(_w; SPLIT(_txt; " "); OR(ARRAYFORMULA(IsInList(_w; _list))))
    );
    _bool; MAP(_descr; LAMBDA(_d; MatchByWord(_d; _villes)));
    ARRAYFORMULA(IF(_bool; "OK"; "KO"))
)

Bonjour à tous les 2,

je confirme que les 2 solutions fonctionnent bien. C'est donc résolu. Pour le sport, comment devrais-je adapter l'une ou l'autre des formules pour qu'au lieu de OK ou KO la formule me renvoie la ville mentionnée ?

Merci pour votre retour, pour ma proposition, on peut l'adapter ainsi :

=LET(
    _descr; A1:A5;
    _villes; A13:A16;
    FindInList; LAMBDA(_x; _list; XLOOKUP(_x; _list; _list));
    MatchByWord; LAMBDA(_txt; _list;
        LET(
            _w; SPLIT(_txt; " ");
            _f0; ARRAYFORMULA(FindInList(_w; _list));
            _f1; FILTER(_f0; NOT(ISERROR(_f0)));
            TEXTJOIN(",";TRUE; _f1)
        )
    );
    MAP(_descr; LAMBDA(_d; MatchByWord(_d; _villes)))
)

Voilà à ma sauce :

=byrow(TOCOL( $A$1:$A;3);lambda(Liste;iferror(REGEXEXTRACT(UPPER(Liste);ArrayFormula(TEXTJOIN("|"; TRUE; TOCOL(UPPER(query(VILLES!$A$1:$A;"select * where A is not null "));3))));"KO")))

https://docs.google.com/spreadsheets/d/1jdD9f03cRmDiAQrPrcQcGnU3PaiAeljTsnL78z5yylE/edit?usp=sharing

J'ai pu adapter la nouvelle formule de Filoche à mon fichier de base, ca marche très bien. Concernant la 2ème de Saboh, j'ai aussi testé. Au début ca ne marchait pas mais il manquait simplement la référence à l'onglet VILLES et aux colonnes entières.

J'ai corrigé comme suit pour info :

=LET( _descr; A:A; _villes;VILLES!A:A; FindInList; LAMBDA(_x; _list; XLOOKUP(_x; _list; _list)); MatchByWord; LAMBDA(_txt; _list; LET( _w; SPLIT(_txt; " "); _f0; ARRAYFORMULA(FindInList(_w; _list)); _f1; FILTER(_f0; NOT(ISERROR(_f0))); TEXTJOIN(",";TRUE; _f1) ) ); MAP(_descr; LAMBDA(_d; MatchByWord(_d; _villes))))

Pas sûr de l'avantage de l'une ou l'autre des 2 solutions par rapport à l'autre

Merci à tous les 2 en tout cas et bonne journée :)

Ce sont 2 approches un peu différentes, moi j'ai simplement adapté une formule Excel à Google Sheets, donc éventuellement repasser sur Excel serait très facile. En revanche Filoche utilise des fonctionnalités vraiment propres à Gsheets comme QUERY.

Les 2 sont parfaitement équivalents, il n'y a pas de mieux ou moins bien.

Si je pouvais tout de même vous donner un petit conseil, disons de "bonnes pratiques", ce serait de filtrer vos plages avant de les utiliser. Passer par les colonnes entières c'est vraiment demander à votre tableau des milliers/millions d'évaluations inutiles. Il y a aussi les tables in Google Sheets - Google Docs Editors Help qui sont l'équivalent des tableaux structurés (XL) qui vous permettraient de contourner ce problème.

Je prends bonne note de cette recommandation. Je prendrai le temps de consulter ces liens car mon fichier en l'occurence comporte près de 40000 lignes.

Merci