Chapitre 2 · Intermédiaire · Leçon 12 / 26
Recherche & correspondances
Tu as une liste de commandes d'un côté, un catalogue de prix de l'autre, et tu veux le prix en face de chaque commande. Recopier à la main ? Impensable sur mille lignes. C'est exactement ce que les fonctions de recherche font à ta place : elles vont chercher la bonne donnée dans un autre tableau et la ramènent, en une formule recopiable.
Le modèle mental est toujours le même : tu pars d'une clé, tu la cherches dans une plage, et tu ramènes le résultat qui lui correspond.
Une clé (la référence produit), une plage où la chercher (la colonne des références du catalogue), une plage d'où ramener (la colonne des prix). Toutes les fonctions de recherche, RECHERCHEX, RECHERCHEV, INDEX et EQUIV, sont des variantes de cette même idée. Comprends le concept une fois, et tu choisis ensuite l'outil selon le contexte.
Pour rendre ça concret, gardons le même exemple dans toute la leçon : un petit catalogue avec la référence en colonne A et le prix en colonne B. L'objectif : retrouver le prix de la référence B205 sans la chercher à la main.
| A | B | |
|---|---|---|
| 1 | Référence | Prix |
| 2 | A100 | 12,50 € |
| 3 | B205 | 8,90 € |
| 4 | C300 | 21,00 € |
Tu saisis la référence cherchée dans une cellule à part (ici E2), et la fonction ramène le prix en face, ici 8,90 €. Chaque fonction de recherche répond à cette même question à sa manière.
La clé doit être unique et propre
Une recherche s'appuie sur une clé qui identifie chaque ligne sans ambiguïté : une référence, un matricule, un code. Si la clé se répète ou traîne des espaces, la recherche ramène la mauvaise valeur ou échoue. C'est le point à vérifier en premier.
RECHERCHEV : l'historique et ses limites
Commençons par celle que tu rencontreras le plus souvent. Pendant vingt ans, RECHERCHEV a été la fonction reine : tu la croiseras dans la plupart des anciens classeurs, et beaucoup de collègues ne connaissent qu'elle. Elle reste utile, à condition de connaître ses deux faiblesses.
Sur notre catalogue, avec RECHERCHEV, la recherche du prix de B205 s'écrit :
=RECHERCHEV(E2;A2:B100;2;FAUX)Autrement dit : « cherche E2 dans la première colonne du tableau A2:B100, ramène la valeur de la 2e colonne, en correspondance exacte. » Le FAUX final est crucial, il exige une correspondance exacte, et l'oublier est l'erreur la plus fréquente. Là où RECHERCHEV montre ses limites, c'est sur deux points.
D'abord, elle ne regarde que vers la droite. RECHERCHEV cherche la clé dans la première colonne de la plage que tu lui donnes, puis ne peut ramener qu'une valeur située à droite de cette colonne. Si la donnée voulue est à gauche de la clé, par exemple retrouver une référence à partir d'un prix, RECHERCHEV en est incapable : il faut réorganiser le tableau ou changer de fonction.
Ensuite, le numéro de colonne est fragile. Dans notre formule, le 2 désigne la 2e colonne de la plage comme colonne de résultat. Mais ce numéro est figé : insère une colonne au milieu du tableau, et le 2 ne pointe plus la bonne donnée. La formule continue de tourner et ramène une valeur fausse, sans le moindre message d'erreur. C'est le piège le plus sournois, et la raison principale pour laquelle on lui cherche des alternatives.
INDEX et EQUIV : l'alternative flexible
Ces deux limites, les utilisateurs avancés les ont longtemps contournées avec un duo : INDEX et EQUIV. Le principe consiste à séparer le repérage de la position et la valeur à ramener, avec deux fonctions qui travaillent ensemble.
Sur le même catalogue, le duo s'écrit :
=INDEX(B2:B100;EQUIV(E2;A2:A100;0))EQUIV(E2;A2:A100;0) trouve à quelle ligne se situe la clé E2 dans A2:A100 (le 0 impose l'exact). INDEX(B2:B100;…) ramène la valeur de la colonne des prix à cette même ligne.
Comme INDEX et EQUIV désignent les colonnes directement, la formule cherche dans n'importe quel sens et ne casse pas quand tu insères une colonne. Plus de souplesse, en échange d'une formule un peu plus longue à écrire.
Recherche exacte ou approximative
Quelle que soit la fonction, RECHERCHEV comme INDEX et EQUIV, tu choisis entre deux modes de recherche. Prendre le mauvais, c'est s'exposer à des résultats faux qui passent inaperçus.
La recherche exacte ne ramène qu'une correspondance parfaite : soit la clé existe telle quelle dans la plage, soit la fonction renvoie une erreur. C'est le mode qu'on veut presque toujours, par exemple pour retrouver le prix exact de la référence B205. Avec RECHERCHEV, c'est précisément le rôle du FAUX en dernier argument ; l'oublier te fait basculer dans l'autre mode sans t'en rendre compte.
La recherche approximative, elle, ramène la valeur inférieure la plus proche quand la clé exacte n'existe pas. C'est utile pour les barèmes et les grilles : une remise par tranche de chiffre d'affaires, une note par intervalle de points. Mais elle suppose une plage triée par ordre croissant ; sur des données en désordre, elle renvoie n'importe quoi sans broncher. À réserver donc aux cas où tu cherches vraiment une tranche, jamais une valeur précise.
L'erreur #N/A et comment la gérer
En correspondance exacte, il arrive qu'une clé soit tout simplement absente de la plage. Excel répond alors #N/A : ce n'est pas un bug, c'est sa façon de dire « cette clé n'existe pas ici ». Deux réflexes pour la traiter proprement.
Avec RECHERCHEX, c'est intégré. Un quatrième argument prévoit le texte à afficher quand la clé est absente, sans aucune formule supplémentaire :
=RECHERCHEX(E2;A:A;B:B;"Non trouvé")Avec RECHERCHEV ou INDEX/EQUIV, tu enrobes la formule dans SIERREUR. Cette fonction attrape l'erreur et la remplace par le texte de ton choix :
=SIERREUR(RECHERCHEV(...);"Non trouvé")Dans les deux cas, le tableau reste lisible même quand une référence manque, au lieu d'afficher des #N/A un peu partout.
RECHERCHEX : la moderne, à préférer aujourd'hui
Sens de recherche bloqué à droite, numéro de colonne fragile, #N/A à enrober dans SIERREUR : RECHERCHEX balaie ces pièges d'un coup. Si ta version d'Excel la propose (365 ou 2021), c'est elle qu'il faut privilégier aujourd'hui ; elle reprend le modèle mental tel quel, en plus simple.
Toujours sur le même catalogue, RECHERCHEX retrouve le prix de B205 (saisi en E2) en une seule formule, sans rien enrober :
=RECHERCHEX(E2;A2:A100;B2:B100;"Absent")Chaque argument, en clair :
- •E2 : la référence que tu cherches (la clé)
- •A2:A100 : où la chercher (la colonne des références)
- •B2:B100 : quoi ramener (la colonne des prix)
- •"Absent" : ce qui s'affiche si la référence n'existe pas
Trois atouts résument pourquoi on la préfère, et chacun répond à un problème vu plus haut. Elle cherche dans tous les sens, à gauche comme à droite, là où RECHERCHEV restait bloquée vers la droite. Elle gère l'absence nativement, grâce au quatrième argument qu'on vient de voir, sans passer par SIERREUR. Et elle ne casse pas à l'insertion d'une colonne, puisqu'elle désigne les plages directement au lieu d'un numéro figé. En clair, elle efface d'un coup les deux limites de RECHERCHEV et la corvée du #N/A.
Quand utiliser quoi : tableau de décision
Tu connais le concept et les trois fonctions. Voici comment choisir en une lecture, selon ton contexte.
| Ton contexte | À choisir | Pourquoi |
|---|---|---|
| Tu es sur Excel 365 ou 2021 | RECHERCHEX | La plus simple et la plus robuste. À privilégier par défaut. |
| Le classeur doit tourner sur de vieilles versions | RECHERCHEV | Compatible partout, bien connue des collègues. |
| Tu cherches vers la gauche ou dans tous les sens | INDEX + EQUIV | RECHERCHEV ne sait pas remonter à gauche, INDEX/EQUIV si. |
| Tu insères souvent des colonnes dans le tableau | RECHERCHEX ou INDEX/EQUIV | Ne se basent pas sur un numéro de colonne fixe, donc ne cassent pas. |
Questions fréquentes sur la recherche dans Excel
Tu pars d'une clé (un identifiant, un nom, une référence), tu indiques la plage où chercher, et la fonction te ramène la donnée associée. Sur les versions récentes, RECHERCHEX fait ce travail simplement : =RECHERCHEX(clé;plage_recherche;plage_résultat). RECHERCHEV et INDEX/EQUIV sont les alternatives plus anciennes.
RECHERCHEX est la fonction recommandée si ta version la propose (Excel 365 et 2021). Elle cherche dans n'importe quelle direction, gère les absences sans formule supplémentaire et ne casse pas quand on insère une colonne. RECHERCHEV reste utile pour la compatibilité avec d'anciens classeurs.
L'erreur #N/A signale que la valeur cherchée est introuvable. Le plus souvent : un espace invisible, une casse différente, un nombre stocké comme texte, ou une recherche approximative sans tri. Vérifie la clé avec SUPPRESPACE, et utilise FAUX comme dernier argument pour exiger une correspondance exacte.
RECHERCHEV cherche toujours de gauche à droite, à partir de la première colonne. INDEX combiné à EQUIV sépare le repérage de la ligne et la colonne à ramener, ce qui permet de chercher dans n'importe quel sens et de ne pas casser quand on insère une colonne. Plus souple, un peu plus long à écrire.
La recherche exacte ramène uniquement une correspondance parfaite : c'est ce qu'on veut dans 95 % des cas (retrouver un prix par référence). La recherche approximative ramène la valeur inférieure la plus proche et sert aux tranches, par exemple un barème ou une grille de remise, à condition que les bornes soient triées.
RECHERCHEX accepte un argument « si_non_trouvé » pour afficher un message à la place de l'erreur. Avec RECHERCHEV ou INDEX/EQUIV, on enrobe la formule dans SIERREUR : =SIERREUR(formule;"Non trouvé"). Le tableau reste propre même quand une clé manque.
Tu veux croiser tes tableaux sans erreur ?
Le Dojo Club te donne accès à des exercices guidés sur RECHERCHEX et les croisements de tableaux, des lives experts chaque semaine et une communauté active pour passer de la théorie à des classeurs qui retrouvent la bonne donnée tout seuls.
Rejoindre le Dojo