- RECHERCHEX(valeur; plage_recherche; plage_résultat; si_non_trouvé; mode_correspondance; mode_recherche) désigne séparément la colonne où chercher et celle d’où renvoyer.
- La correspondance est exacte par défaut, la recherche peut aller vers la gauche, et un message de repli remplace #N/A : trois pièges de RECHERCHEV disparaissent.
- Le mode de recherche −1 renvoie la dernière occurrence (dernière facture d’un client) ; le mode de correspondance −1 range une valeur dans sa tranche sans table triée.
- Elle exige Excel 2021 ou Microsoft 365 ; pour les versions antérieures, INDEX/EQUIV (épisode 21) fait la même chose.
L’épisode précédent a montré tout ce qui peut mal tourner avec RECHERCHEV. RECHERCHEX (XLOOKUP en anglais) est la fonction conçue pour les corriger. Si votre version d’Excel la propose (Excel 2021 ou Microsoft 365), elle devrait devenir votre réflexe pour toute recherche dans un tableau.
Ce que vous saurez faire à la fin de l’épisode
- écrire une RECHERCHEX avec ses six arguments, dont trois facultatifs ;
- chercher vers la gauche, sans toucher à la structure d’un tableau ;
- afficher un message clair quand la valeur est introuvable ;
- retrouver la première ou la dernière occurrence d’une clé ;
- ranger une valeur dans une tranche sans table triée.
La syntaxe
=RECHERCHEX(valeur_cherchée; plage_recherche; plage_résultat; [si_non_trouvé]; [mode_correspondance]; [mode_recherche])| Argument | Rôle |
|---|---|
valeur_cherchée |
La clé à retrouver |
plage_recherche |
La colonne où chercher (une seule colonne) |
plage_résultat |
La colonne d’où renvoyer la valeur, de même hauteur |
si_non_trouvé |
Ce qu’il faut afficher si la clé est introuvable (au lieu de #N/A) |
mode_correspondance |
0 exacte (défaut) · −1 exacte ou plus petite · 1 exacte ou plus grande · 2 jokers |
mode_recherche |
1 du début à la fin (défaut) · −1 de la fin au début · 2 ou −2 recherche binaire |
Les trois premiers arguments suffisent dans 90 % des cas. Comparée à RECHERCHEV, la différence est énorme : plus de numéro de colonne à compter, plus de FAUX à ne pas oublier, plus de clé obligatoirement à gauche.
Chercher vers la gauche
Atlas Négoce tient un tarif : code, désignation, prix hors taxes. On a la désignation sous les yeux (« Armoire métallique ») et l’on veut le code, situé à gauche de la désignation, ce que RECHERCHEV ne sait pas faire.
Figure 1 : la colonne F retrouve le code (repère 1) ; « Fauteuil » n’existe pas dans le tarif (repère 2).
=RECHERCHEX(E2; B$2:B$5; A$2:A$5; "Inconnu")« Cherche la désignation de E2 dans la colonne B, renvoie la valeur correspondante de la colonne A ; si elle n’existe pas, affiche Inconnu. » Résultats : « Armoire métallique » → P-1004, « Lampe de bureau » → P-1003, « Fauteuil » → Inconnu. Pour retrouver le prix : =RECHERCHEX(E2;B$2:B$5;C$2:C$5;0) donne 2 750,00 pour l’armoire et 120,00 pour la lampe.
Remarquez que si_non_trouvé peut être un texte, un nombre (0) ou même une formule. Mais attention : afficher 0 pour un article inconnu est trompeur (un article gratuit ?) ; préférez un texte ou un signal très visible. Et n’oubliez pas la leçon de l’épisode 14 : un message de repli ne doit pas cacher une anomalie ; accompagnez-le d’un compteur de contrôle.
Première ou dernière occurrence
Lorsqu’une clé apparaît plusieurs fois, RECHERCHEV renvoie toujours la première. Avec RECHERCHEX, le mode de recherche décide.
Figure 2 : cinq factures, dont trois pour le même client. Repère 1 : dernière occurrence (mode −1) ; repère 2 : première (mode 1).
Dernière facture : =RECHERCHEX(F2; A$2:A$6; B$2:B$6; "-"; 0; -1) → FV-0005
Première facture : =RECHERCHEX(F2; A$2:A$6; B$2:B$6; "-"; 0; 1) → FV-0001Le sixième argument −1 fait lire la colonne de bas en haut. Si le tableau est classé par date croissante, c’est la dernière facture ; la même astuce donne le dernier prix d’achat d’un article ou la dernière écriture d’un compte. Si le tableau n’est pas trié par date, cherchez plutôt la date maximale avec MAX.SI.ENS (épisode 23) puis la ligne correspondante : « dernière » signifie « dernière ligne », pas « plus récente ».
Les tranches sans table triée
Reprenons la table des tranches de retard de l’épisode précédent, avec RECHERCHEX :
Figure 3 : la formule =RECHERCHEX(D2;$A$2:$A$5;$B$2:$B$5;"";-1) classe chaque retard.
Le mode de correspondance −1 demande « la valeur exacte, sinon la plus petite valeur immédiatement inférieure » : 12 tombe sur 0 (« 0-30 j »), 45 sur 31 (« 31-60 j »), 75 sur 61 (« 61-90 j »), 130 sur 91 (« > 90 j »). Contrairement à RECHERCHEV en mode VRAI, la table peut ne pas être triée avec ce mode (la recherche linéaire trouve quand même la bonne valeur), même si trier reste une bonne pratique de lisibilité.
Le mode 1 fait l’inverse (la plus petite valeur supérieure) : utile pour des seuils qui s’expriment « jusqu’à » plutôt que « à partir de ».
Les jokers, pour chercher un fragment
Avec mode_correspondance = 2, les caractères * (suite quelconque) et ? (un caractère) fonctionnent : =RECHERCHEX("Sud*"; A2:A100; B2:B100; "-"; 2) renvoie la première ligne dont le tiers commence par « Sud ». Pour chercher un fragment présent dans la valeur, concaténez des jokers : "*"&E2&"*".
Renvoyer plusieurs colonnes à la fois
Dans Excel 2021 et Microsoft 365, la plage de résultat peut compter plusieurs colonnes :
=RECHERCHEX(E2; B$2:B$5; A$2:C$5)Le résultat se déverse sur les cellules voisines : le code, la désignation et le prix de l’article trouvé apparaissent côte à côte avec une seule formule (épisode 48 sur les formules dynamiques). Si le classeur doit être lu dans une version qui ne gère pas le déversement, prévoyez plutôt une formule par colonne.
Comparaison et bonnes pratiques
| Besoin | RECHERCHEV | RECHERCHEX |
|---|---|---|
| Correspondance exacte | FAUX à ne pas oublier |
Par défaut |
| Clé à droite du résultat | Impossible | Oui |
| Colonne de résultat | Numéro en dur | Plage désignée |
| Valeur introuvable | #N/A ou SI.NON.DISP |
Argument si_non_trouvé |
| Dernière occurrence | Contournements | Mode de recherche −1 |
| Version requise | Toutes | 2021 / 365 |
Bonnes pratiques : figez les plages avec $ (ou mieux, utilisez des tableaux Excel : RECHERCHEX([@Compte]; tblPlan[Compte]; tblPlan[Intitulé])), contrôlez le type des clés (texte contre nombre, épisode 2) comme avant, et ne transmettez pas un classeur utilisant RECHERCHEX à quelqu’un qui a Excel 2016 ou 2019.
À vous de jouer
Dans le classeur d’exercice :
- Onglet Tarifs : complétez
F2:G4avec les deux formules de l’épisode. Que renvoie la recherche du prix du « Fauteuil » ? Remplacez le repli0par"n.d.". - Onglet Factures : retrouvez la dernière et la première facture de « Maroc Équipements », puis de « Atlas Export ».
- Onglet Tranches : classez les retards 12, 45, 75 et 130. Ajoutez le retard 61 : quelle tranche ?
- Modifiez le tarif pour que la désignation « Bureau » apparaisse deux fois (prix différents) : quelle ligne est renvoyée en mode 1 ? en mode −1 ?
Correction. Le prix du fauteuil renvoie 0,00 avec le repli 0, et « n.d. » après modification. Pour Maroc Équipements : dernière facture FV-0005, première FV-0001 ; pour Atlas Export (une seule facture) : FV-0004 dans les deux cas. Le retard 61 tombe pile sur le début de la tranche « 61-90 j ». Avec deux lignes « Bureau », le mode 1 renvoie la première (ligne du haut), le mode −1 la dernière (ligne du bas).
À retenir
RECHERCHEX(valeur; où_chercher; quoi_renvoyer; repli): exacte par défaut, vers la gauche ou la droite.si_non_trouvéremplaceSI.NON.DISP(RECHERCHEV(…)); il ne doit pas masquer une vraie anomalie.- Mode de recherche −1 : dernière occurrence ; mode de correspondance −1 : tranche.
- Disponible dans Excel 2021 et 365 seulement ; INDEX/EQUIV prend le relais pour les anciennes versions.
- Les tableaux Excel rendent la formule encore plus lisible.
Épisode 21 : INDEX et EQUIV, la recherche universelle, jusqu’au croisement d’une ligne et d’une colonne.
Questions fréquentes
RECHERCHEX existe-t-elle dans Excel 2016 ou 2019 ?
Non. Elle est disponible dans Excel 2021, Excel pour le web et Microsoft 365. Dans les versions antérieures, utilisez INDEX/EQUIV ; un classeur ouvert dans une version qui ne connaît pas la fonction affiche #NOM?.
Comment renvoyer plusieurs colonnes avec une seule formule ?
Désignez plusieurs colonnes comme plage de résultat (par exemple A2:C5) : le résultat se déverse sur les cellules voisines (Excel 2021 et Microsoft 365). Dans un classeur destiné à une version plus ancienne, utilisez une formule par colonne.
Que signifient les valeurs 0, −1, 1 et 2 du mode de correspondance ?
0 : exacte (défaut) ; −1 : exacte, sinon la plus petite valeur immédiatement inférieure ; 1 : exacte, sinon la plus grande valeur immédiatement supérieure ; 2 : correspondance avec jokers (* et ?).
Quand utiliser le mode de recherche −1 ?
Quand la clé apparaît plusieurs fois et qu’on veut la dernière occurrence : dernière facture d’un client, dernier prix d’achat d’un article, dernier solde d’un compte. Le mode 1 (défaut) renvoie la première.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.