Formule générique
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Sommaire
Pour classer le texte à l'aide de mots-clés avec une correspondance «contient», vous pouvez utiliser la fonction RECHERCHE, avec l'aide d'INDEX et de MATCH. Dans l'exemple illustré, la formule en C5 est:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
où mots - clés est la plage nommée E5: E14 et catégories est la plage nommée F5: F14.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
Au cœur, il s'agit d'une fonction INDEX et MATCH.
A l' intérieur de la fonction MATCH, nous utilisons la fonction de recherche pour rechercher les cellules dans la colonne B pour chaque mot - clé dans la plage classée nommés mots - clés (E5: E14):
SEARCH(keywords,B5)
Étant donné que nous recherchons plusieurs éléments (dans les mots clés de la plage nommée ), nous obtiendrons plusieurs résultats comme celui-ci:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
La valeur! Une erreur se produit lorsque SEARCH ne trouve pas le texte. Lorsque SEARCH trouve une correspondance, elle renvoie un nombre qui correspond à la position du texte à l'intérieur de la cellule.
Pour changer ces résultats dans un format plus utilisable, nous utilisons la fonction ISNUMBER, qui convertit toutes les valeurs en TRUE / FALSE comme ceci:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Ce tableau entre dans la fonction MATCH en tant que lookup_array, avec la valeur lookup_value définie sur TRUE. MATCH renvoie la position du premier TRUE qu'il trouve dans le tableau (7 dans ce cas) qui est fourni à la fonction INDEX en tant que row_num:
=INDEX(categories,7)
INDEX renvoie le 7ème élément des catégories , "Auto", comme résultat final.
Avec XLOOKUP
Avec la fonction XLOOKUP, cette formule peut être quelque peu simplifiée. XLOOKUP peut utiliser la même logique que celle utilisée dans la fonction MATCH ci-dessus, donc la formule équivalente est:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP localise le premier TRUE dans le tableau et renvoie la valeur correspondante à partir des catégories .
Empêcher les fausses correspondances
Un problème avec cette approche est que vous pouvez obtenir de fausses correspondances à partir de sous-chaînes qui apparaissent à l'intérieur de mots plus longs. Par exemple, si vous essayez de faire correspondre "dr", vous pouvez également trouver "Andrea", "drink", "dry", etc. puisque "dr" apparaît à l'intérieur de ces mots. Cela se produit parce que SEARCH effectue automatiquement une correspondance «contient».
Pour un piratage rapide, vous pouvez ajouter de l'espace autour des mots de recherche (par exemple "dr" ou "dr") pour éviter d'attraper "dr" dans un autre mot. Mais cela échouera si "dr" apparaît en premier ou en dernier dans une cellule, ou apparaît avec une ponctuation, etc.
Si vous avez besoin d'une solution plus précise, une option consiste à normaliser le texte d'abord dans une colonne d'aide, en prenant soin d'ajouter également un espace de début et de fin. Ensuite, vous pouvez rechercher des mots entiers entourés d'espaces.
