RECHERCHEX renvoie #N/A alors que la valeur existe : 6 tests avant SIERREUR

La valeur est visible dans le tableau, mais RECHERCHEX renvoie #N/A. La cause n’est pas forcément la formule elle-même : deux cellules qui semblent identiques peuvent contenir des types ou des caractères différents. Avant de masquer l’erreur avec SIERREUR, vérifiez successivement la règle de correspondance, le type, les caractères invisibles, les plages, les doublons et le mode de recherche.

Cette méthode transforme un « pourtant, je le vois » en diagnostic vérifiable. Elle évite aussi de remplacer un défaut de données par un zéro qui pourrait ensuite être pris pour un vrai résultat.

Ce que #N/A signifie — et ce qu’il ne signifie pas

La syntaxe documentée par Microsoft est :

=RECHERCHEX(valeur_cherchée;tableau_recherche;tableau_renvoyé;[si_non_trouvé];[mode_correspondance];[mode_recherche])

La correspondance exacte est le mode par défaut. Si Excel ne trouve pas de correspondance valable, la fonction renvoie #N/A, sauf si l’argument facultatif [si_non_trouvé] est renseigné. Microsoft précise aussi que RECHERCHEX renvoie la première correspondance trouvée par défaut. La documentation officielle de RECHERCHEX/XLOOKUP détaille ces arguments, ainsi que les recherches approximatives, par caractères génériques et binaires.

#N/A ne prouve donc pas que la référence n’apparaît nulle part à l’écran. Il indique que, selon les valeurs réellement stockées et les règles de la formule, aucune correspondance exploitable n’a été trouvée.

La fiche CLÉ 6 TESTS

Travaillez sur une copie anonymisée ou autorisée. Ne corrigez pas d’abord toute la colonne : isolez une ligne qui échoue et une ligne qui fonctionne, puis comparez-les.

Le nom CLÉ 6 TESTS désigne ici cet outil pédagogique ; ce n’est ni une norme Microsoft ni une méthode validée par une étude ou un formateur.

Test 1 — vérifier la valeur et la règle de correspondance

Commencez avec une formule explicite :

=RECHERCHEX(A2;TablePrix[Code];TablePrix[Prix];"À contrôler";0)

Le 0 demande une correspondance exacte. L’argument "À contrôler" rend l’absence visible sans transformer le problème en faux prix nul.

Trois pièges sont fréquents :

• la formule pointe vers la désignation alors que la cellule contient un code ;
• la valeur recherchée a été concaténée avec un préfixe ou un suffixe ;
• un mode approximatif ou avec caractères génériques a été copié depuis un autre besoin.

Si le code métier est AB-1042, testez A2=TablePrix[@Code] sur la ligne supposée correspondante. Si le résultat est FAUX, passez aux tests de type et de caractères au lieu de réécrire immédiatement la recherche.

Test 2 — distinguer le nombre 1042 du texte « 1042 »

Une apparence identique n’impose pas un type identique. Utilisez temporairement :

• =ESTNUM(A2) ;
• =ESTTEXTE(A2) ;
• les mêmes deux tests sur la cellule de la table de référence.

Microsoft rappelle que des nombres stockés comme texte peuvent produire des résultats inattendus. Excel peut proposer « Convertir en nombre » ; Microsoft documente également CNUM/VALUE selon la langue de l’interface. Faites cette conversion dans une colonne de contrôle avant de remplacer une source entière.

La bonne cible dépend du métier. Un identifiant comme 001042 n’est pas nécessairement un nombre : le convertir ferait perdre les zéros initiaux. En revanche, une quantité ou un tarif importé comme texte doit souvent devenir numérique. La question n’est pas « comment forcer Excel ? », mais « quel est le type attendu pour cette clé ? ».

Test 3 — rendre visibles espaces et caractères parasites

Comparez d’abord les longueurs :

=NBCAR(A2)

Si AB-1042 devrait compter sept caractères mais en compte huit, un espace final ou un caractère importé est probable. Une colonne de diagnostic peut utiliser :

=SUPPRESPACE(NETTOYER(A2))

La documentation Microsoft de SUPPRESPACE/TRIM précise cependant une limite importante : la fonction retire l’espace ASCII 32, mais pas à elle seule l’espace insécable Unicode 160. Dans un fichier copié depuis le Web ou un export, il peut donc rester un caractère invisible après SUPPRESPACE.

Pour un cas identifié d’espace insécable, testez dans une colonne auxiliaire, selon la version et la plateforme :

=SUPPRESPACE(SUBSTITUE(NETTOYER(A2);CAR(160);" "))

Conservez la valeur source tant que vous n’avez pas vérifié que la substitution ne modifie pas un identifiant légitime. Les tirets, apostrophes et espaces peuvent faire partie d’un code métier.

Test 4 — contrôler la géométrie des plages

Dans la barre de formule, sélectionnez successivement tableau_recherche et tableau_renvoyé. Ils doivent représenter des lignes correspondantes. Avec une table structurée, c’est généralement plus lisible :

=RECHERCHEX(A2;TablePrix[Code];TablePrix[Prix];"À contrôler";0)

Un copier-coller peut faire glisser une référence relative, mélanger deux feuilles ou omettre les nouvelles lignes d’une plage fixe. Une recherche peut alors trouver un code dans une zone qui ne correspond plus au bon résultat.

Contrôlez aussi les filtres et les lignes masquées : ils changent ce que vous voyez, pas nécessairement la plage que la formule parcourt. La preuve recherchée est simple : la nième clé doit renvoyer la nième valeur de la même table logique.

Test 5 — traiter les doublons comme une décision métier

Comptez les occurrences :

=NB.SI(TablePrix[Code];A2)

• 0 : aucune clé exactement équivalente n’a été trouvée ; revenez aux tests 1 à 3 ;
• 1 : la clé est unique dans la table ;
• 2 ou plus : la formule peut renvoyer une ligne, mais le résultat n’est pas forcément celui que le métier attend.

La documentation Microsoft indique que RECHERCHEX part du premier élément par défaut. Le mode de recherche -1 permet de partir du dernier. Choisir « premier » ou « dernier » n’est pourtant pas une règle suffisante si deux tarifs actifs portent le même code. Il faut alors une clé composée, une date d’effet, un statut ou une étape de dédoublonnage explicitement décidée.

Test 6 — vérifier le mode de recherche et la compatibilité

Les modes binaires 2 et -2 peuvent être rapides sur des listes importantes, mais Microsoft exige respectivement un tri croissant ou décroissant ; sinon, les résultats peuvent être invalides. Pour diagnostiquer un #N/A, revenez d’abord au mode standard, sans recherche binaire, puis ne réintroduisez ce choix que si le tri est garanti et contrôlé.

Vérifiez aussi l’environnement. La page Microsoft indique que RECHERCHEX n’est pas disponible nativement dans Excel 2016 et Excel 2019, même si ces versions peuvent ouvrir un classeur contenant une formule créée dans une version plus récente. Sur un poste ancien, une alternative ou une montée de version peut être nécessaire ; ne promettez pas qu’une formule sera portable sans test.

Simulation fictive : le prix qui « existe »

Cette simulation ne décrit aucun client réel.

Dans Commandes, la cellule A2 affiche 1042. Dans TablePrix, le code visible affiche également 1042. La formule renvoie pourtant #N/A.

1. ESTNUM(A2) renvoie VRAI.
2. Sur la ligne de référence, ESTTEXTE(TablePrix[@Code]) renvoie VRAI.
3. NBCAR vaut 4 des deux côtés : il ne s’agit pas d’un espace supplémentaire.
4. Une colonne auxiliaire convertit le texte vers un nombre, après confirmation que les zéros initiaux ne sont pas significatifs.
5. La recherche exacte fonctionne sur la colonne convertie.
6. NB.SI confirme une seule occurrence.

La correction n’est pas « ajouter SIERREUR ». Elle consiste à rendre cohérent le type de la clé, à conserver la transformation visible et à contrôler l’unicité.

Exercice corrigé — quel test lancer en premier ?

Associez chaque symptôme au premier test utile.

Quand passer d’un dépannage à une méthode Excel plus robuste ?

Un dépannage ponctuel suffit si la source est stable, le type de clé est clair et la formule reste compréhensible par son mainteneur. Un accompagnement devient pertinent lorsque plusieurs classeurs répètent les conversions, que les doublons ont un impact métier, que les plages évoluent ou que personne ne sait expliquer pourquoi un résultat est accepté.

L’offre Formation Excel expérimenté : construire des formules fiables et des modèles contrôlables présente deux parcours publics : 14 heures pour approfondir notamment les recherches, dates, textes, fonctions dynamiques et erreurs ; 21 heures pour ajouter architecture, audit, recette et documentation. La page indique Strasbourg, la distance et l’intra-entreprise, avec des prérequis sur les tableaux, formules de base et filtres. Elle invite aussi à vérifier la compatibilité des fonctions selon la version. Dates, places, prix, financement et adaptation doivent être confirmés pour le projet réel.

Si la difficulté vient plutôt du choix entre formules, tableau croisé dynamique et transformation de données, le guide Excel : formules, TCD ou Power Query — comment arrêter le copier-coller ? aide à choisir l’outil avant de construire le modèle.

Préparer une demande d’orientation utile

Pour recevoir une orientation adaptée, indiquez : votre version et votre plateforme Excel, la formule anonymisée, le type attendu de la clé, un exemple qui fonctionne et un qui échoue, la source des données, la règle d’unicité, l’usage du résultat, la personne qui maintient le fichier, votre échéance et la modalité souhaitée.

Cela permet de distinguer un réglage ponctuel d’un besoin de formation sur les recherches, le nettoyage des données, l’audit ou l’architecture. Aucune réussite, disponibilité, prise en charge ou inscription n’est garantie sans étude du dossier.

Sources

• Microsoft Support — XLOOKUP function, consulté le 22 septembre 2026.
• Microsoft Support — TRIM function, consulté le 22 septembre 2026.
• Microsoft Support — Convert numbers stored as text to numbers in Excel, consulté le 22 septembre 2026.
• StraFormation — Formation Excel expérimenté, consultée le 22 septembre 2026.