Ce que #N/A dit, et ce qu'il ne dit pas
Le message #N/A a un sens étroit : la fonction a parcouru la zone désignée et n'y a trouvé aucune correspondance pour la clé demandée. Il ne dit pas si la donnée manque dans la table source, si la clé est mal orthographiée, si elle est stockée comme texte face à des nombres, ou si la plage a été mal cadrée. La documentation Microsoft liste ces causes séparément, et toutes produisent le même affichage.
La répartition des erreurs est votre première information. Trente #N/A sur trente lignes décrivent un problème systématique ; deux ou trois erreurs isolées au milieu d'une colonne qui fonctionne pointent vers des lignes à examiner une à une. Rappel structurel : RECHERCHEV ne cherche la valeur que dans la première colonne de la plage indiquée. Si la clé se trouve en deuxième ou troisième colonne de cette plage, aucune correction de saisie n'y changera rien.
Relire ses clés en quatre contrôles
L'objectif n'est pas de corriger la formule, mais de produire un verdict : valeur réellement absente, ou valeur présente mais inatteignable. Travaillez dans des colonnes de contrôle placées à côté de votre formule, que vous supprimerez ensuite, afin de ne pas modifier les données d'origine pendant le diagnostic.
Déroulez les contrôles dans cet ordre, du plus global au plus fin. Les deux premiers concernent la formule, les deux suivants les données elles-mêmes.
- Cadrage : la clé cherchée est-elle bien dans la première colonne de la plage ? Vérifiez aussi que le dernier argument vaut FAUX, car omis il vaut VRAI et suppose une liste triée.
- Existence : écrivez =NB.SI(colonne_des_clés;clé_cherchée). Un 0 signe une absence réelle, un 1 ou plus signifie que la valeur est là et que l'échec vient d'ailleurs.
- Longueur : comparez =NBCAR(clé_du_tableau) et =NBCAR(clé_de_la_table). Un caractère d'écart dénonce un espace de début, de fin, ou un caractère invisible.
- Type : avec ESTTEXTE et ESTNUM, vérifiez que les deux clés sont de même nature. Un identifiant stocké en texte d'un côté et en nombre de l'autre ne se rencontre jamais.
Du symptôme au contrôle à mener
Cette grille relie ce que vous voyez à l'écran à la vérification qui tranche, sans passer par des essais successifs sur la formule.
| Ce que vous observez | Cause la plus fréquente | Contrôle à mener |
|---|---|---|
| Toute la colonne en #N/A, sans exception | Plage mal cadrée ou clé absente de la première colonne | Vérifier l'argument de plage et la position de la colonne de clés |
| Quelques lignes en erreur, les autres correctes | Clés mal saisies sur ces lignes précises | Comparer NBCAR entre les deux clés, puis nettoyer les espaces |
| NB.SI renvoie 1 mais la recherche échoue | Types différents, texte contre nombre, ou caractère invisible | Tester ESTTEXTE et ESTNUM, puis retirer le caractère 160 |
| Aucune erreur mais des résultats manifestement faux | Correspondance approximative sur une liste non triée | Forcer FAUX comme dernier argument et recontrôler quelques lignes |
Mise en situation fictive : trente références de pièces
Exemple entièrement fictif, construit pour l'exercice. Dans un classeur d'entraînement, une feuille Commandes liste trente références de pièces saisies à la main ; une feuille Catalogue contient ces références avec leur désignation et leur poids. La recherche renvoie #N/A sur les trente lignes. Premier contrôle : la plage commence bien par la colonne des références et le dernier argument vaut FAUX, le cadrage est donc correct. Deuxième contrôle : NB.SI appliqué à la colonne du Catalogue renvoie 0. Troisième contrôle : NBCAR donne 8 côté Catalogue et 9 côté Commandes, avec un écart constant sur les trente lignes.
Le verdict se construit tout seul : la saisie manuelle a ajouté un espace final sur chaque référence. Les valeurs existent, elles sont simplement inatteignables, et sur une clé nettoyée les trente résultats apparaissent. Pour vous entraîner, ajoutez volontairement un espace final sur dix lignes d'un tableau fictif et retrouvez-les sans inspecter les cellules une à une ; puis remplacez deux références du Catalogue par des valeurs inexistantes, de façon à devoir produire deux listes distinctes. N'utilisez qu'un jeu de données inventé et ne saisissez aucune information confidentielle ni donnée personnelle.
Erreurs fréquentes au moment de corriger
Première erreur : envelopper la formule dans SIERREUR dès l'apparition du message. L'écran devient propre, la synthèse semble complète, et les lignes non rapprochées disparaissent silencieusement du total. SIERREUR a sa place après le diagnostic, pour afficher un libellé explicite, pas pour faire taire une alerte. Deuxième erreur : appliquer SUPPRESPACE, constater que l'erreur persiste et conclure que les espaces n'étaient pas en cause ; comparez la longueur avant et après, puis traitez l'espace insécable séparément.
Troisième erreur : corriger les clés à la main, ligne par ligne, sans identifier la cause — le fichier suivant reproduira le même problème. Mieux vaut noter en une phrase ce qui a été trouvé et conserver une colonne de contrôle dans le classeur. Quatrième erreur : changer de fonction en espérant que l'erreur disparaisse. INDEX et EQUIV lèvent la contrainte de la première colonne, mais une clé comportant un espace parasite reste introuvable quelle que soit la fonction employée.
Où cette compétence se situe
Relire des clés de recherche est inséparable du travail sur les fonctions de recherche et les tableaux de synthèse croisant plusieurs feuilles. Cet exercice fictif constitue un point d'entrée vers la formation Excel du catalogue, consacrée à la maîtrise du tableur le plus utilisé au monde ; le programme publié en détaille les contenus.
Questions fréquentes
Pourquoi certaines lignes fonctionnent et d'autres non dans la même colonne de formules ?
C'est le signe que la formule est bonne et que les clés diffèrent entre les deux tables. Comparez une clé qui fonctionne et une clé en erreur avec NBCAR, puis vérifiez le type des valeurs.
Comment savoir si une référence est vraiment absente de la table source ?
Comptez ses occurrences dans la colonne de recherche avec NB.SI. Un résultat de 0 confirme l'absence ; un résultat de 1 ou plus signifie que la formule échoue pour une autre raison.
SUPPRESPACE ne règle pas mon problème d'espaces, est-ce normal ?
Oui : la documentation Microsoft indique que SUPPRESPACE supprime l'espace ASCII de valeur 32 mais pas l'espace insécable de code 160. Traitez ce caractère à part, par exemple avec SUBSTITUE et CAR(160), avant de relancer la recherche.
Le dernier argument de RECHERCHEV change-t-il quelque chose aux #N/A ?
Oui : omis, il vaut VRAI et lance une correspondance approximative qui suppose la première colonne triée. Microsoft signale qu'elle peut produire des #N/A ou des résultats erronés, donc indiquez FAUX pour une clé unique.
Repères et limites
Cet article porte sur la relecture des clés de recherche et la lecture du message #N/A dans un classeur de taille modeste. Il ne traite pas des autres messages d'erreur, ni des recherches multicritères, ni du comportement des fonctions de recherche sur des sources de données externes volumineuses. Les libellés de fonctions cités correspondent à une interface en français.
Sources et ressources
Donnez une suite concrète à votre projet.
Consultez le parcours « Excel » et ses modalités pour préparer votre échange avec notre équipe.
Demander le programme par e-mailVotre messagerie s’ouvre avec la formation et l’article préremplis. Envoyez ensuite votre demande à contact@ceemfi.org.
Consulter le programme →