Tuto Excel : faire une recherche sensible à la casse (RECHERCHEV)

Dans ce tutoriel, nous découvrirons comment effectuer une recherche verticale qui est sensible à la casse dans une feuille de calcul Excel.

Dans un ancien tutoriel, nous avons abordé la fonction qui permet de faire une recherche verticale : “la fonction RECHERCHEV“. Nous pouvons dire que chaque utilisateur Excel connait cette fonction, cependant, la plupart d’eux ne savent pas que RECHRCHEV est insensible à la casse. En effet, par défaut, la valeur de recherche dans la fonction RECHERCHEV ne respecte pas la casse. Cela signifie qu’elle traitera les deux mots, « pomme » qui est en minuscule et « POMME » qui est en majuscule, d’une manière identique.

Nous vous expliquerons comment rendre la fonction RECHERCHEV sensible à la casse, et vous montrerons également d’autres fonctions à utiliser comme solutions alternatives.

Table des matières

Comment rendre la fonction RECHERCHEV sensible à la casse

Rendre RECHERCHEV sensible à la casse : utilisation de la colonne d'assistance

Rendre RECHERCHEV sensible à la casse : sans la colonne d'aide

Comment effectuer une recherche de correspondance exacte avec SOMMEPROD

EQUIV/INDEX - recherche sensible à la casse pour tous les types de données

Comment rendre la fonction RECHERCHEV sensible à la casse ?

Comme vous le savez déjà, une formule RECHERCHEV habituelle est insensible à la casse. Cependant, il existe des techniques pour la rendre ainsi.

Dans l’ensemble des données indiqué ci-dessous, nous avons une liste des noms des élèves (avec différentes casses) et leurs notes en mathématique :

Comme vous pouvez le voir, il y a trois cellules avec le même nom (A2, A4 et A6) mais avec une casse différente. Sur la droite (en D3:E5), nous avons les trois noms : « Amine », « AMINE » et « amine », ainsi que leurs notes en mathématiques.

Comme vous pouvez le deviner, une formule RECHERCHEV habituelle =RECHERCHEV("AMINE"; $A$2:$B$9;2; FAUX) récupérera la valeur 10 qui est associée à "Amine" car il vient avant "AMINE" dans le tableau de recherche. Mais ce n'est pas ce que vous souhaitez, non ?

Pour pouvoir effectuer une RECHERCHEV sensible à la casse dans Excel, vous devez utiliser l'une des méthodes suivantes :

  • Utilisation d'une colonne d'assistance.
  • Utilisation d’une formule sans colonne d’assistance.

Rendre RECHERCHEV sensible à la casse : utilisation de la colonne d'assistance

Vous pouvez utiliser une colonne d'assistance pour obtenir une valeur de recherche unique pour chaque élément du tableau de recherche. En effet, cela aide à différencier les noms avec une casse différente.

Pour ce faire, suivez les étapes ci-dessous :

  1. Insérez une colonne d'assistance à gauche de la colonne à partir de laquelle vous souhaitez récupérer les données. Dans l'exemple ci-dessous, nous allons insérer la colonne d'assistance entre les deux colonnes A et B :
  1. Dans la colonne d'assistance, entrez la formule =LIGNE().Cette formule sert à insérer le numéro de ligne dans chaque cellule comme illustré ci-dessous :
  1. Tapez la formule suivante dans la cellule F3pour obtenir le résultat de la recherche sensible à la casse :

=RECHERCHEV(MAX(EXACT(E3; $A$2:$A$9) * (LIGNE($A$2:$A$9))); $B$2:$C$9; 2; 0)

  1. Finalement, copiez et collez la formule dans les autres cellules (F4 et F5) :

Remarque : puisqu'il s'agit d'une formule matricielle, utilisez le raccourci Ctrl + Shift + Entrée au lieu de simplement “Entrée“.

Comment fonctionne cette formule ?

Décomposons la formule en parties pour comprendre son fonctionnement :

  • EXACT(E3; $A$2:$A$9) : cette partie de la formule compare la valeur de recherche dans la cellule E3 avec toutes les valeurs dans la plage A2:A9. Ensuite, elle renvoie un tableau de « VRAI » et « FAUX » où la valeur « VRAI » est renvoyée lorsqu'il y a une correspondance exacte. Dans ce cas, où la valeur dans E3 est « Amine », cette formule renverra le tableau suivant :

{VRAI; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX}

  • EXACT(E3; $A$2:$A$9) * (LIGNE($A$2:$A$9) : cette formule multiplie le tableau de « VRAI et FAUX » ci-dessus par le numéro de ligne A2:A9. Partout où il y a un VRAI, elle donne le numéro de ligne, sinon elle donne 0. Dans ce cas, elle renvoie le tableau suivant :

{2; 0; 0; 0; 0; 0; 0; 0}

  • MAX(EXACT(E3; $A$2:$A$9) * (LIGNE($A$2:$A$9)) : cette formule renvoie la valeur maximale du tableau de nombres ci-dessus. Dans ce cas, elle renverra 2 (qui est le numéro de ligne où il y a une correspondance exacte de « Amine »).
  • Maintenant, nous utilisons simplement ce nombre comme valeur de recherche et nous utilisons ainsi le tableau de recherche comme B2:C9 dans la fonction RECHERCHEV.

Les deux notes suivantes sont essentielles pour le bon fonctionnement de votre formule RECHERCHEV sensible à la casse :

  • La colonne d'assistance doit être la colonne dans l’extrimité gauche dans la plage de recherche (argument « table_matrice »).
  • La valeur de recherche (argument « valeur_cherchée ») doit être un "code de caractère" plutôt qu'une valeur réelle.

Vous pouvez également remplir votre colonne d'assistance avec la formule ci-dessous (où B est votre colonne de recherche) :

=CODE(STXT(A2; 1; 1))&CODE(STXT(A2; 2; 1))&CODE(STXT(A2; 3; 1))&CODE(STXT(A2; 4; 1))&SIERREUR(CODE(STXT(A2; 5; 1)); "")

La formule ci-dessus analyse la valeur de recherche en caractères individuels, convertit chaque caractère en son code (par exemple, «A» est 65 et «a» est 97), puis concatène ces codes en une chaîne numérique unique :

Après avoir rempli votre colonne d’assistance, vous pouvez utiliser une formule RECHERCHEV simple sensible à la casse :

=RECHERCHEV(F3; $B$2:$C$9; 2; FAUX)

La formule CODE qui est copiée dans la colonne d'assistance implique que toutes vos valeurs de recherche ont le même nombre de caractères. Sinon, vous devez connaître les nombres minimum et maximum et ajouter autant de fonctions SIERREUR que le nombre de caractères faisant la différence entre les valeurs de recherche les plus petites et les plus grandes.

Par exemple, si la plus petite valeur de recherche a 3 caractères et la plus grande dispose de 5 caractères, vous utiliserez cette formule :

=CODE(STXT(A2; 1; 1))&CODE(STXT(A2; 2; 1))&CODE(STXT(A2; 3; 1))&SIERREUR(CODE(STXT(A2; 3; 1)); "")&SIERREUR(CODE(STXT(A2; 4; 1)); "")

Dans la fonction STXT, vous devez spécifier les arguments suivants :

  • Le premier argument « texte » : il s'agit d’un texte ou d'une référence de cellule contenant les caractères que vous souhaitez extraire (A2 dans notre cas).
  • Le deuxième argument « no_départ » : il représente la position du premier caractère que vous souhaitez extraire. Vous entrez 1 dans la première fonction STXT, 2 dans la seconde et ainsi de suite.
  • Le dernier argument « no_car » : il spécifie le nombre de caractères que vous souhaitez renvoyer à partir du texte. Puisque vous voulez toujours 1, vous entrez "1" dans toutes les fonctions.

Désormais, si vous n'aimez pas l’utilisation d’une colonne d'assistance, vous pouvez également effectuer une recherche sensible à la casse en utilisant la méthode suivante.

Rendre RECHERCHEV sensible à la casse : sans la colonne d'assistance

Dans cette méthode vous n’avez pas à vous soucier d’insérer une colonne d’assistance. Cependant, vous devez toujours disposer d'une colonne d'assistance virtuelle ! Cette colonne virtuelle ne fait pas partie de la feuille de calcul mais elle est construite dans la formule.

Voici la formule qui vous donnera le résultat sans la colonne d'assistance :

=RECHERCHEV(MAX(EXACT(D3; $A$2:$A$9) * (LIGNE($A$2:$A$9))); CHOISIR({1.2}; LIGNE($A$2:$A$9); $B$2:$B$9); 2; 0)

Remarque : N’oubliez pas qu'il s'agit d'une formule matricielle, utilisez Ctrl + Shift + Entrée au lieu d’utiliser simplement Entrée.

Comment fonctionne cette formule ?

Cette formule se base sur le concept de colonne d'assistance. La différence est qu'au lieu de créer la colonne d'assistance dans la feuille de calcul, considérez-la comme une donnée d'assistance virtuelle faisant partie de la formule globale.

Voici la partie qui fonctionne comme les données d'assistance dans la formule :

CHOISIR({1.2}; LIGNE($A$2:$A$9); $B$2:$B$9)

Pour comprendre ce que nous voulons dire par des données d'assistance virtuelle, jetez un coup d’œil sur la capture ci-dessous :

Dans l'illustration ci-dessus, lorsqu’on sélectionne la partie CHOISIR de la formule et qu’on clique sur F9, cela montre le résultat que la formule CHOISIR renvoi. Le résultat dans ce cas est le suivant :

{2.10; 3.15; 4.15; 5.12; 6.14; 7.12; 8.10; 9.17}

Il s’agit d’un tableau dans lequel un point représente la cellule suivante de la même ligne et un point-virgule indique que les données suivantes se trouvent dans la ligne suivante. Par conséquent, cette formule crée 2 colonnes de données : une colonne qui contient les numéros de lignes et une autre qui contient les notes de mathématique.

Maintenant, lorsque vous utilisez la fonction RECHERCHEV, elle recherche simplement la valeur de recherche dans la première colonne (de ces données virtuelles à 2 colonnes) et renvoie la note correspondante. La valeur de recherche ici est un nombre que nous obtenons de la combinaison des fonctions MAX et EXACT.

Comment effectuer une recherche de correspondance exacte avec SOMMEPROD

Comme vous l'avez déjà compris à partir du sous-titre, SOMMEPROD est encore une autre fonction Excel qui peut effectuer une recherche sensible à la casse. Cependant, elle ne peut renvoyer que des valeurs numériques.

La fonction SOMMEPROD multiplie les composantes dans les tableaux spécifiés en argument et renvoie la somme des produits. Sa syntaxe est la suivante :

SOMMEPROD(tableau1; tableau2 ; tableau3 ; ...)

Puisque nous souhaitons effectuer une recherche sensible à la casse, nous allons utiliser la fonction EXACT de l'exemple précédent comme l'un des multiplicateurs.

La fonction EXACT fonctionne comme un outil de recherche. Cependant, elle est sensible à la casse. Cette fonction compare deux chaînes de texte et renvoie VRAI si elles sont exactement identiques, FAUX sinon.

Bien que SOMMEPROD fonctionne avec des tableaux, elle ne nécessite pas l'utilisation de raccourci de formule matricielle (CTRL + Shift + Enter). Vous pouvez compléter une formule SOMMEPROD en appuyant simplement sur « Enter » après avoir tapé la formule.

En considérant l'exemple précédent, la formule en E4 est la suivante :

=SOMMEPROD((EXACT($A$2:$A$9; D4) * ($B$2:$B$9)))

Dans ce cas, les recherches standard comme RECHERCHEH de RECHERCHEV ignoreront la casse et renverront la première correspondance, qui est « Amine ». Cependant, la fonction EXACT correspondra aux cas exacts dans « tableau_recherche ». Le résultat sera donc 14 :

Comment fonctionne cette formule ?

Comme vous pouvez le deviner, la fonction EXACT compare la valeur de la cellule D4 à tous les éléments de la colonne A et si une correspondance exacte sensible à la casse est trouvée, elle retourne « VRAI », sinon elle retourne « FAUX ». Dans les opérations mathématiques, Excel traite « VRAI » comme « 1 » et FAUX comme « 0 », de sorte que la fonction SOMMEPROD multiplie les nombres renvoyés et additionne les produits.

N’oubliez pas que les zéros ne comptent pas ici, car un zéro produit toujours "0" quel que soit le nombre par lequel il est multiplié. Alors, expliquons ce qui se passe lorsqu'une correspondance exacte (sensible à la casse) est trouvée dans la colonne A et que "1" est renvoyé : la fonction SOMMEPROD multiplie 1 par le nombre correspondant dans la colonne B (de la même ligne) et renvoie exactement ce nombre ! En effet, les produits des autres multiplications sont des zéros, ce qui n'aura aucun effet sur la valeur renvoyée.

EQUIV/INDEX - recherche sensible à la casse pour tous les types de données

Enfin, dans cette section nous allons aborder une formule de recherche sensible à la casse sans limitation qui fonctionne sur tous les types de données.

Par défaut, les recherches standard avec RECHERCHEV ou INDEX + EQUIV ne sont pas sensibles à la casse. Les fonctions RECHERCHEV et EQUIV renverront simplement la première correspondance, en ignorant la casse.

La fonction EQUIV recherche une valeur, spécifiée en argument, dans une plage spécifiée (Tableau_recherche) et renvoie sa position relative dans le tableau. Ensuite, cette position relative de la valeur de recherche va directement à l'argument « no_lig » de la fonction INDEX lui demandant de renvoyer une valeur à partir de cette ligne. Toutefois, si vous devez faire une recherche qui sera sensible à la casse, vous pouvez le faire avec une formule matricielle qui utilise INDEX, EQUIV et la fonction EXACT.

Dans notre exemple, nous allons utiliser la formule suivante :

=INDEX($B$2:$B$9; EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0))

Cette formule est une formule matricielle et doit être saisie avec le raccourcie Ctrl + Shift + Enter.

Puisque la fonction EQUIV seule n'est pas sensible à la casse, nous avons besoin d'un moyen pour vérifier la correspondance de la casse. C’est ainsi que nous avons besoin de la fonction EXACT, mais la façon dont nous l'utilisons est un peu inhabituelle, car nous devons comparer une cellule à une plage de cellules. Expliquons cette formule de l'intérieur vers l'extérieur :

  • EXACT(D4; $A$2:$A$9) : où la cellule « D4 » contient la valeur de recherche et « $A$2:$A$9 » est une référence à la colonne de recherche (Élève). Cette formule renverra un tableau de valeurs « VRAI » et « FAUX » comme suit :

{FAUX; FAUX; FAUX; FAUX; VRAI; FAUX; FAUX; FAUX}

  • Ce tableau est le résultat de la comparaison de la valeur de D4 à chaque cellule de la colonne de recherche. Partout où nous voyons « VRAI », nous savons que nous avons une correspondance exacte qui respecte la casse.
  •  Maintenant, nous devons obtenir la position ou, en d’autres termes le numéro de ligne, de la valeur « VRAI » dans ce tableau. Pour cela, nous utilisons la fonction EQUIV, à la recherche de « VRAI » :

EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0)

  • Il est important de noter que la fonction EQUIV retournera toujours la première correspondance s'il y a des doublons, donc si, par exemple, elle trouve qu'il y a une autre correspondance exacte dans la colonne, elle ne vous retournera que la première.
  • Après avoir eu le numéro de ligne de la valeur qui correspond exactement à la recherche, nous devons simplement utiliser la fonction INDEX pour récupérer la valeur à l'intersection droite de la ligne et de la colonne. Le numéro de colonne dans ce cas n’est pas mentionné, car les données de plage nommées incluent seulement la colonne des notes (B2:B9).

Remarque : cette formule récupérera à la fois le texte et les valeurs numériques. Si vous souhaitez récupérer uniquement des nombres, vous pouvez utiliser une formule basée sur SOMMEPROD expliquée ci-dessus.

Supposons maintenant qu'une cellule de la colonne de retour correspondant à une valeur de recherche soit vide. Que retournera la formule ? Rien. Et maintenant, voyons ce qu'elle renvoie réellement :

Comme vous pouvez le voir, la formule renvoie un zéro. Peut-être ce n'est pas une grande chose si vous travaillez uniquement avec des valeurs textuelles. Cependant, si votre ensemble de données contient des nombres et que certains d'entre eux sont de vrais zéros, cela vous causera un problème.

Pour rendre la formule INDEX/EQUIV sensible à la casse absolument parfaite, vous devez l'envelopper dans la fonction SI qui vérifiera si une cellule de retour est vide et ne renverra rien dans ce cas :

=SI(INDIRECT("B" & (1 + EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0))) ""; INDEX($B$2:$B$9; EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0)); "")

Dans cette formule, « B » est la colonne de retour. Le « 1+ » est utilisé pour transformer la position relative d'une cellule renvoyée par la fonction EQUIV en une adresse de cellule réelle. Par exemple, le tableau de recherche dans notre fonction EQUIV est A2:A9, alors la position relative de la cellule A2 est « 1 », car il s'agit de la première cellule du tableau. Mais la position réelle de la cellule A2 dans la colonne est « 2 », donc nous ajoutons 1 pour compenser la différence, pour que la fonction INDIRECT renvoie une valeur de la cellule de droite.

Comme illustré ci-dessous, la formule renvoie une chaine vide si la cellule de retour contient un zéro :

Télécharger le fichier Excel de ce tutoriel

Article publié le 06 Septembre 2020par Hanane Mouqqadim