Nous avons vu dans un précédent articles les bases de la fonction RECHERCHEX d’Excel.
Avec les arguments de base (obligatoires), nous pouvions rechercher une (ou plusieurs) valeur(s) à partir d’un critère de recherche.
Or, la fonction RECHERCHEX va plus loin que ça ! Je vous dis tout.
L'argument mode_correspondance
Par défaut, l’argument permet une correspondance exacte ce qui permet de rechercher une valeur précise dans un tableau.
Cela corrige un défaut de l’ancienne fonction RECHERCHEV qui avait un mode de correspondance approximative.
Mais avec RECHERCHEX, il existe 3 autres options de correspondance. Voyons tout cela avec un exemple par option.
-1 : Correspondance exacte ou élément inférieur suivant
Parfois, pour trouver une valeur, vous devez faire des formules avec des SI imbriqués. Le problème est que si le nombre de cas est important, la formule devient très longue.
RECHERCHEX permet d’éviter ce genre de situation.
Imaginons qu’on veut calculer automatiquement une remise pour une vente en fonction de son montant.
Sans RECHERCHEX, on devrait faire la formule suivante :
=SI($E4>$A$8;$B$8;SI($E4>$A$7;$B$7;SI($E4>$A$6;$B$6;SI($E4>$A$5;$B$5;0))))
Fastidieux n’est-ce pas ?
Essayons avec RECHERCHEX et l’argument mode_correspondance.
On recherche le taux de remise quand le montant de la vente atteint un seuil.
=RECHERCHEX(E4;Tabl_remises[Montant HT];Tabl_remises[Taux Remise];"erreur";-1)
Comme vous le constatez, pour la 1ère vente, on a un montant de 11880 €. Il dépasse le seuil des 10 000 €. On lui applique donc une remise de 20%.
Pour la 2nde vente, 7627 €, dépasse les 5 000 € donc une remise de 10%.
1 : Correspondance exacte ou élément supérieur suivant
C’est le même principe que précédemment mais au lieu de seuils on prend des plafonds.
Nous avons cette fois-ci une grille de primes en fonction des ventes de commerciaux d’une entreprise.
On met l’argument mode_correspondance à 1
=RECHERCHEX(E32;Tabl_primes[Montant HT];Tabl_primes[Prime];;1)
Dans notre exemple, Romain a atteint 45 000 €. Ce qui lui vaut une prime de 1500 €.
Or, il y a une erreur. Bastien qui a un chiffre d’affaires de 110 000 €, la prime est en erreur.
C’est en fait normal car on part du principe que 100 000 € est un plafond.
Mais si je vous en parle, c’est que j’ai une solution : il vous suffit de remplacer l’argument si_non_trouvé par le montant maximal de prime, soit 2000 €.
Cela donne :
=RECHERCHEX(E32;Tabl_primes[Montant HT];Tabl_primes[Prime];2000;1)
2 : correspondance de caractère générique
Il est possible que vous ayez un formulaire de recherche mais n’utilisez pas une liste déroulante pour votre critère de recherche.
Vous pouvez avoir un champ de saisie et laisser Excel rechercher si ce que vous avez saisi est trouvé dans votre base.
Avec RECHERCHEX, vous pouvez modifier vos arguments pour qu’Excel recherche dans la base les cellules qui contiennent le texte choisi.
Pour l’exemple, j’ai une liste de produits et je vais tenter de trouver le nom complet d’un produit en saisissant une partie de son nom dans la cellule F3.
Comment faire ?
On va modifier 2 arguments :
1°) valeur_cherchée
On souhaite trouver SSD mais on ne connaît pas ce qu’il peut y avoir avant ou après.
On va utiliser ce qu’on appelle les caractères génériques de recherche. Il en existe plusieurs, je vous présente les 2 principaux :
- * : Représente un nombre quelconque de caractères. Vous pouvez utiliser l’astérisque (*) n’importe où dans une chaîne de caractères.
Exemple : qu* renvoie question, qui et quoi, mais pas gymnastique.
- ? : Représente une seule lettre occupant une position spécifique
Exemple : ?oudre renvoie coudre, moudre et poudre.
Comme c’est du texte dans une formule, il faut les mettre entre guillemets « ». Enfin pour joindre les astérisques à notre texte saisi, on ajoute des esperluettes & pour concaténer les données.
Cela donne :
"*"&F3&"*"
Excel va ainsi chercher dans ma liste tous les noms de produits ….SSD…
2°) mode_correspondance
Pour lui dire qu’on fait une recherche générique, l’argument est mis avec la valeur 2.
On obtient la formule :
=RECHERCHEX("*"&F3&"*";$C$2:$C$18;$A$2:$A$18;;2) 
Excel a trouvé une correspondance : il y a bien un nom de produit qui contient le terme SSD saisi en cellule F3.
L'argument mode_recherche
1ère exemple : rechercher le 1er ou dernier résultat possible
D’habitude, nous utilisons les fonctions de recherche pour trouver des informations sur une occurrence qui n’apparaît qu’une seule fois dans un tableau
Exemple : Recherche du prix du produit A dans un tableau équivalent à un catalogue (réf, désignation, montant du produit…)
Or, vous pouvez avoir des tableaux pour lesquels votre critère de recherche apparaît sur plusieurs lignes.
Votre but est d’afficher un résultat spécifique. C’est là que rentre en jeu l’argument mode_recherche.
Vous avez plusieurs possibilités :
- 1 : Rechercher du premier au dernier
- -1 : Rechercher du dernier au premier
- 2 : Recherche binaire (tri croissant)
- -2 : Recherche binaire (tri décroissant)
C’est parti avec un exemple.
J’ai un tableau de livraisons pour lequel je veux rechercher le montant de la première livraison au client SOUFFLET. Pour cela, je vais mettre 1 à l’argument mode_recherche.
J’obtiens la formule :
=RECHERCHEX(I1;Tabl_ventes[Client];Tabl_ventes[Montant];"rien";0;1)
Inversement, si je veux obtenir la dernière livraison à SOUFFLET, je passe l’argument mode_recherche à -1
Comme vous pouvez le constater, Excel affiche la dernière saisie de mon tableau pour SOUFFLET soit la livraison d’un montant de 7300 €.
2ème exemple : Rechercher à partir de 2 bases
Pour certains d’entre vous, il est possible que vous ayez des bases de données dans lesquelles les données sont différentes en fonction d’un critère.
Prenons un exemple concret : J’ai un tableau de concordance de barême pour appliquer un taux de remise selon le statut du vendeur et son C.A.
Il existe 2 barêmes selon l’expérience du vendeur : junior et senior. Suivant ce statut, les taux de remise sont différents.
Maintenant, imaginons qu’on veut appliquer un taux de remise pour les juniors.
On créé notre formule :
=RECHERCHEX(F4;Tabl_remise[Montant HT];Tabl_remise[Taux Remise];;-1;1)
avec l’argument mode_recherche = 1 pour lui appliquer notre 1er barême, celui du junior.
Si l’on veut appliquer le barême senior, l’argument mode_recherche = -1 car il faut commencer par la fin de notre tableau de barême.
On peut rendre cela dynamique pour savoir quel profil choisir en fonction du vendeur.
En premier lieu, je vais ajouter une nouvelle colonne dans mon tableau de droite pour indiquer le profil du vendeur.
Ensuite, je vais mettre une condition dans l’argument mode_recherche pour savoir si je dois mettre 1 pour un junior ou -1 pour un sénior.
Pour cela, j’utilise une simple fonction SI:
SI(F5="Junior";1;-1)
J’obtiens au final la formule :
=RECHERCHEX(G5;Tabl_remise[Montant HT];Tabl_remise[Taux Remise];;-1;SI(F5="Junior";1;-1))
Maintenant, notre tableau va regarder le type de profil du vendeur, et appliquer un taux de remise de ce profil en fonction du chiffre d’affaires du vendeur.
Recherche binaire
Pour ces options de l’argument mode_recherche de la fonction RECHERCHEX, le résultat est le même qu’en utilisant les options 1 (première ligne trouvée) et -1 (dernière ligne trouvée).
Le 2 correspond au 1 et le -2 au -1.
Ce qui change, c’est qu’il y a un prérequis : pour utiliser le tri croissant (2), la plage de données dans votre argument tableau_recherche doit être rangée par ordre croissant.
Inversement, pour utiliser la recherche binaire en tri décroissant, votre plage de données dans votre argument tableau_recherche doit être rangée par ordre décroissant.
J’avoue connaître le principe mais ne voit pas de situations pour l’utiliser. Même des recherche sur les sites anglophones et le site officiel de Microsoft, je n’ai trouvé aucun exemple.
