Formule générique
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sommaire
Pour vérifier une cellule pour l'une des nombreuses choses et renvoyer la première correspondance trouvée dans la liste, vous pouvez utiliser une formule INDEX / MATCH qui utilise SEARCH ou FIND pour localiser une correspondance. Dans l'exemple illustré, la formule en C5 est:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
où "choses" est la plage nommée E5: E9.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
Dans cet exemple, nous avons une liste de couleurs dans une plage nommée appelée «choses» (E5: E9). Nous voulons vérifier le texte de la colonne B pour voir s'il contient l'une de ces couleurs. Si c'est le cas, nous voulons renvoyer le nom de la première couleur trouvée.
Travaillant de l'intérieur vers l'extérieur, cette formule utilise la fonction ISNUMBER et la fonction SEARCH pour rechercher dans le texte en B5 chaque couleur répertoriée dans "choses" comme ceci:
ISNUMBER(SEARCH(things,B5)
Cette expression est basée sur une 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, l'expression renvoie TRUE. Sinon, l'expression renvoie FALSE.
Lorsque nous donnons à cette RECHERCHE une liste de choses (au lieu d'une chose) nous donnera un tableau de résultats. Chaque couleur trouvée générera une position numérique, et les couleurs non trouvées généreront une erreur:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
La fonction ISNUMBER convertit ensuite les résultats en valeurs TRUE / FALSE. Tout nombre devient VRAI et toute erreur (non trouvée) devient FAUX. Le résultat est un tableau comme celui-ci:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Ce tableau est renvoyé à la fonction MATCH comme argument de tableau. La valeur de recherche est TRUE et le type de correspondance est défini sur zéro pour forcer une correspondance exacte. Lorsqu'il y a une couleur correspondante, MATCH renvoie la première position TRUE trouvée. Cette valeur est introduite dans la fonction INDEX comme numéro de ligne, avec la plage nommée «choses» fournie comme tableau. Lorsqu'il y a au moins une correspondance, INDEX renvoie la couleur à cette position. Lorsqu'aucune correspondance n'est trouvée, cette formule renvoie l'erreur # N / A.
Avec des valeurs codées en dur
Si vous ne souhaitez pas configurer une plage nommée externe comme "choses" dans cet exemple, vous pouvez coder en dur les valeurs dans la formule en tant que "constantes de tableau" comme ceci:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Obtenez la première correspondance dans la cellule
Le langage ici est assez déroutant, mais la formule ci-dessus renverra la première correspondance trouvée dans la liste des choses à rechercher. Si à la place vous souhaitez renvoyer la première correspondance trouvée dans la cellule testée, vous pouvez essayer une formule comme celle-ci:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Dans cette version de la formule, la fonction MATCH est configurée pour rechercher le résultat de cet extrait de code:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
qui utilise la fonction AGGREGATE pour obtenir la valeur minimale dans les résultats renvoyés par SEARCH. Nous avons besoin d'AGREGATE ici, car le tableau entrant contiendra probablement des erreurs (renvoyées par SEARCH lorsque des éléments ne sont pas trouvés), et nous avons besoin d'une fonction qui ignorera ces erreurs et nous donnera toujours la valeur numérique minimale.
Le résultat de AGGREGATE est renvoyé directement à MATCH en tant que valeur de recherche, avec le même tableau renvoyé par SEARCH. Le résultat final est la première correspondance trouvée dans la cellule, pas la première correspondance trouvée dans la liste des choses.
