Formule Excel : Vérifier si une cellule contient l’une des plusieurs valeurs tout en excluant d’autres valeurs

Supposons que vous disposiez de deux listes de valeurs. Vous souhaitez vérifier si la cellule B3 contient l’une des valeurs de la plage E3:E5, tout en n’en contenant aucune de la plage F3:F4, comme le montre la capture d’écran ci-dessous. Ce tutoriel vous propose une formule pour accomplir cette tâche rapidement dans Excel et détaille les arguments qui la composent.

Formule générique :

Arguments

Valeur de retour:

La formule renvoie 1 ou 0. Lorsque la cellule contient l’une des valeurs à inclure et ne contient aucune des valeurs à exclure, elle renvoie 1 ; sinon, elle renvoie 0. Dans cette formule, 1 et 0 sont traités comme des valeurs logiques VRAI et FAUX.

Fonctionnement de cette formule

Supposons que vous souhaitiez vérifier si la cellule B3 contient l’une des valeurs de la plage E3:E5, tout en excluant en même temps celles de la plage F3:F4. Utilisez alors la formule suivante :

Appuyez sur Entrée pour obtenir le résultat de la vérification.

Explication

Partie 1 : (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) vérifie si la cellule contient des valeurs présentes en E3:E5

Fonction TROUVE : la fonction TROUVE renvoie la position du premier caractère d’une chaîne de texte à l’intérieur d’une autre. Si le texte recherché est trouvé, elle renvoie sa position relative ; sinon, elle génère une erreur #VALEUR!. Par exemple, ici, la formule SEARCH($E$3:$E$5,B3) recherche chaque valeur de la plage E3:E5 dans la cellule B3 et renvoie la position de chaque chaîne trouvée dans cette cellule, produisant un résultat sous forme de tableau tel que : {1;7;12}.

Fonction ESTNUM : cette fonction renvoie VRAI lorsqu’une cellule contient un nombre. Ainsi, ISNUMBER(SEARCH($E$3:$E$5,B3)) renvoie un tableau de résultats tel que {VRAI;VRAI;VRAI}, car la fonction TROUVE a détecté trois nombres.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) convertit la valeur VRAI en 1 et la valeur FAUX en 0, transformant ainsi le tableau en {1;1;1}.

Fonction SOMMEPROD : elle permet de multiplier des plages ou des tableaux entre eux, puis d’additionner les résultats pour renvoyer la somme des produits. La formule SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) renvoie 1 + 1 + 1 = 3.

Enfin, comparez la formule de gauche SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) à 0. Dès que le résultat de cette formule est supérieur à 0, le résultat devient VRAI ; sinon, il est FAUX. Ici, le résultat est VRAI.

Partie 2 : (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) vérifie si la cellule ne contient pas les valeurs présentes en F3:F4

La formule TROUVE($F$3:$F$4;B3) recherche chaque valeur de la plage F3:F4 dans la cellule B3 et renvoie la position de chaque chaîne de texte trouvée. Elle génère un résultat sous forme de tableau, tel que : {#VALEUR!;#VALEUR!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) renvoie un tableau de résultats tel que {FAUX;FAUX}, car la fonction TROUVE n’a trouvé aucun résultat.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) convertit la valeur VRAI en 1 et la valeur FAUX en 0, transformant ainsi le tableau en {0;0}.

Fonction SOMMEPROD : elle permet de multiplier des plages ou des tableaux entre eux, puis d’additionner les résultats pour renvoyer la somme des produits. La formule SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) renvoie 0 + 0 = 0.

Enfin, comparez la formule de gauche SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) à 0. Dès que le résultat de cette formule est égal à 0, le résultat devient VRAI ; sinon, il est FAUX. Ici, le résultat est VRAI.

Partie 3 : multiplication des deux formules

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

Dans cette formule, 1 et 0 sont interprétés comme les valeurs logiques VRAI et FAUX.

Fichier exemple

Cliquez pour télécharger le fichier exemple

Formules associées

• Vérifier si une cellule contient un texte spécifique
  Pour vérifier si une cellule contient certains textes de la plage A tout en n’en contenant aucun de la plage B, utilisez une formule matricielle combinant les fonctions NB, TROUVE et ET dans Excel.
• Vérifier si une cellule contient l’une des nombreuses valeurs
  Ce tutoriel vous présente une formule Excel permettant de vérifier si une cellule contient l’une des nombreuses valeurs, et explique en détail ses arguments ainsi que son fonctionnement.
• Vérifier si une cellule contient l’une des valeurs
  Supposons qu’Excel contienne une liste de valeurs dans la colonne E. Vous souhaitez vérifier si les cellules de la colonne B contiennent l’une des valeurs de la colonne E et renvoyer VRAI ou FAUX.
• Vérifier si une cellule contient un nombre
  Vous souhaitez parfois savoir si une cellule contient un nombre ? Ce tutoriel vous propose une formule qui renvoie VRAI si c’est le cas, et FAUX dans le cas contraire.