Formule générique
=LOOKUP(2,1/SEARCH(things,A1),things)
Sommaire
Pour vérifier une cellule pour l'une des plusieurs choses et renvoyer la dernière correspondance trouvée dans la liste, vous pouvez utiliser une formule basée sur les fonctions RECHERCHE et RECHERCHE. Dans le cas de plusieurs correspondances trouvées, la formule renverra la dernière correspondance de la liste des "choses".
Dans l'exemple illustré, la formule en C5 est:
=LOOKUP(2,1/SEARCH(things,B5),things)
Explication
Contexte: vous avez une liste de choses dans la plage nommée "choses" (E5: E8), et vous voulez vérifier les cellules de la colonne B pour voir si elles contiennent ces choses. Si tel est le cas, vous souhaitez renvoyer le dernier élément des «objets» trouvés.
Dans cette formule, la fonction RECHERCHE est utilisée pour rechercher des cellules dans la colonne B comme ceci:
SEARCH(things,B5)
Lorsque SEARCH trouve une correspondance, elle renvoie la position de la correspondance dans la cellule recherchée. Lorsque la recherche ne trouve pas de correspondance, elle renvoie l'erreur #VALUE. Parce que nous donnons à SEARH plus d'une chose à rechercher, il renverra plus d'un résultat. Dans l'exemple illustré, SEARCH renvoie un tableau de résultats comme celui-ci:
(8;24;#VALUE!;#VALUE!)
Ce tableau est ensuite utilisé comme un diviseur pour le nombre 1. Le résultat est un tableau composé d'erreurs et de valeurs décimales. Les erreurs représentent des choses non trouvées et les valeurs décimales représentent des choses trouvées. Dans l'exemple illustré, le tableau ressemble à ceci:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Ce tableau sert de "lookup_vector" pour la fonction LOOKUP. La valeur de recherche est fournie sous la forme du nombre 2 et le vecteur de résultat est la plage nommée «choses». C'est la partie la plus intelligente.
La formule est construite de telle sorte que le vecteur de recherche ne contiendra jamais une valeur supérieure à 1, alors que la valeur de recherche est 2. Cela signifie que la valeur de recherche ne sera jamais trouvée. Dans ce cas, LOOKUP correspondra à la dernière valeur numérique trouvée dans le tableau, qui correspond à la dernière "chose" trouvée par SEARCH.
Enfin, en utilisant la plage nommée "choses" fournie comme vecteur de résultat, LOOKUP renvoie la dernière chose trouvée.
Avec des valeurs codées en dur
L'utilisation d'une plage telle que "choses" facilite la modification de la liste des termes de recherche (et l'ajout de termes de recherche supplémentaires), mais ce n'est pas obligatoire. Vous pouvez également coder en dur les valeurs directement dans la formule comme ceci:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
