Formule générique
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sommaire
Pour tester une cellule pour l'une des chaînes et renvoyer un résultat personnalisé pour la première correspondance trouvée, vous pouvez utiliser une formule INDEX / MATCH basée sur la fonction SEARCH. Dans l'exemple illustré, la formule en C5 est:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
où les choses (E5: E8) et les résultats (F5: F8) sont des plages nommées.
Il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
Cette formule utilise deux plages nommées: les choses et les résultats . Si vous portez directement cette formule, veillez à utiliser des plages nommées avec les mêmes noms (définis en fonction de vos données). Si vous ne souhaitez pas utiliser de plages nommées, utilisez plutôt des références absolues.
Le noyau de cette formule est cet extrait:
ISNUMBER(SEARCH(things,B5)
Ceci est basé sur une autre formule (expliquée en détail ici) qui vérifie une cellule pour une seule sous-chaîne. Si la cellule contient la sous-chaîne, la formule renvoie TRUE. Sinon, la formule renvoie FALSE.
Parce que nous donnons la fonction de recherche plus d'une chose à rechercher, dans la plage nommée choses , il nous donnera plus d' une conséquence, dans un tableau qui ressemble à ceci:
(#VALUE!;9;#VALUE!;#VALUE!)
Les nombres représentent des correspondances dans les choses , les erreurs représentent des éléments qui n'ont pas été trouvés.
Pour simplifier le tableau, nous utilisons la fonction ISNUMBER pour convertir tous les éléments du tableau en TRUE ou FALSE. Tout nombre valide devient VRAI, et toute erreur (c'est-à-dire une chose non trouvée) devient FAUX. Le résultat est un tableau comme celui-ci:
(FALSE;TRUE;FALSE;FALSE)
qui entre dans la fonction MATCH en tant qu'argument lookup_array, avec une valeur lookup_value de TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH renvoie alors la position du premier TRUE trouvé, 2 dans ce cas.
Enfin, nous utilisons la fonction INDEX pour récupérer un résultat de la plage nommée résultats à cette même position:
=INDEX(results,2) // returns "found red"
Vous pouvez personnaliser la plage de résultats avec toutes les valeurs pertinentes dans votre cas d'utilisation.
Empêcher les fausses correspondances
Un problème avec cette approche avec l'approche ISNUMBER + SEARCH est que vous pouvez obtenir de fausses correspondances à partir de correspondances partielles dans des mots plus longs. Par exemple, si vous essayez de faire correspondre «dr», vous pouvez également trouver «Andrea», «drank», «drip», etc. puisque «dr» apparaît à l'intérieur de ces mots. Cela se produit parce que SEARCH effectue automatiquement une correspondance "contient-type".
Pour une solution rapide, vous pouvez encapsuler les mots de recherche dans des caractères d'espacement (par exemple "dr" ou "dr") pour éviter de trouver "dr" dans un autre mot. Mais cela échouera si "dr" apparaît en premier ou en dernier dans une cellule.
Si vous avez besoin d'une solution plus robuste, une option consiste à normaliser le texte d'abord dans une colonne d'aide et à ajouter un espace de début et de fin. Utilisez ensuite la formule de cette page sur le texte de la colonne d'assistance, au lieu du texte d'origine.
