RECHERCHEV renvoie #N/A : les 6 causes et comment les corriger

Le test qui trouve la cause en 10 secondes

Repérez une ligne en #N/A, retrouvez à l'œil la même référence dans l'autre tableau, puis tapez dans une cellule vide une comparaison directe, par exemple =A2=Fournisseur!A15. Si Excel répond FAUX alors que les deux cellules semblent identiques, le problème vient des données et non de la formule.

Deux formules complètent le diagnostic : =NBCAR(A2) et =NBCAR(Fournisseur!A15) comptent les caractères (un écart trahit une espace), et =ESTTEXTE(A2) indique si la cellule contient du texte ou un nombre.

Les 6 causes en un tableau

1. Une espace en trop

C'est la cause la plus fréquente, surtout avec des fichiers exportés d'un logiciel ou copiés depuis un site. « REF-1001 » suivi d'une espace n'est pas égal à « REF-1001 ». L'espace peut aussi être insécable (caractère 160), que l'on ne voit pas et que SUPPRESPACE ne retire pas.

Correction : nettoyez la clé dans une colonne intermédiaire avec =SUPPRESPACE(SUBSTITUE(A2;CAR(160);" ")), recopiez, collez en valeurs, puis faites porter la RECHERCHEV sur cette colonne. La même opération est souvent nécessaire dans l'autre fichier.

2. Du texte d'un côté, un nombre de l'autre

Pour Excel, le nombre 3760123456789 et le texte « 3760123456789 » sont deux valeurs différentes. C'est très courant avec les EAN et les références numériques : un fichier les a importés comme texte (souvent signalé par un petit triangle vert dans le coin de la cellule), l'autre comme nombres.

Correction : alignez les formats. Pour chercher un nombre dans une colonne de texte, convertissez la valeur cherchée : =RECHERCHEV(TEXTE(A2;"0");Fournisseur!A:C;3;FAUX). Dans l'autre sens, CNUM() transforme le texte en nombre. Attention : si Excel a déjà supprimé le zéro de tête d'un EAN, le code n'est plus le même et aucune conversion ne le retrouvera. Le guide Excel supprime les zéros de vos EAN explique comment l'éviter dès l'ouverture du fichier.

3. La clé n'est pas dans la première colonne de la plage

RECHERCHEV cherche uniquement dans la première colonne de la plage indiquée. Si votre plage commence par la désignation et que la référence est en deuxième colonne, la formule cherche la référence parmi les désignations et renvoie #N/A.

Correction : faites commencer la plage à la colonne de la clé, ou, si la valeur à renvoyer est à gauche de la clé, passez à RECHERCHEX ou à INDEX/EQUIV, qui cherchent dans n'importe quelle colonne. Les trois formules sont comparées dans Alternative à RECHERCHEV.

4. Une plage qui glisse quand vous recopiez la formule

Sans $, la plage A2:C500 devient A3:C501 sur la ligne suivante, puis A4:C502, et ainsi de suite. Les premières lignes fonctionnent, les dernières renvoient #N/A parce que le début du tableau est sorti de la plage.

Correction : figez la plage avec la touche F4, =RECHERCHEV(A2;$A$2:$C$500;3;FAUX), ou utilisez des colonnes entières (Fournisseur!$A:$C) qui ne glissent pas.

5. Une recherche approximative

Le dernier argument de RECHERCHEV vaut VRAI par défaut : sans FAUX, Excel suppose que la première colonne est triée et cherche la valeur la plus proche. Sur une liste de références non triée, le résultat est #N/A ou, pire, la valeur d'une autre ligne, sans aucun message d'erreur.

Correction : terminez toujours la formule par FAUX (ou 0). RECHERCHEX n'a pas ce piège : elle cherche une correspondance exacte par défaut.

6. La valeur n'existe vraiment pas

Parfois, #N/A dit la vérité : le produit n'est pas dans le fichier du fournisseur. Vérifiez avec Ctrl+F dans l'autre fichier. Dans ce cas, plutôt que de laisser des #N/A, affichez un texte clair et comptez les lignes concernées :

1. 1=SIERREUR(RECHERCHEV(A2;Fournisseur!$A:$C;3;FAUX);"absent") remplace toute erreur par « absent ».
2. 2=SI.NON.DISP(RECHERCHEV(A2;Fournisseur!$A:$C;3;FAUX);"absent") ne remplace que les #N/A et laisse visibles les autres erreurs, comme une référence de plage cassée : c'est le choix le plus sûr.
3. 3=NB.SI(D:D;"absent") donne ensuite le nombre de produits sans correspondance.

Masquer les #N/A ne les corrige pas : avant de les remplacer par « absent », vérifiez sur quelques lignes qu'il ne s'agit pas d'une des cinq causes précédentes.

Éviter les #N/A sans formule

Mergy relie les deux fichiers sur la colonne commune sans RECHERCHEV. Avant de comparer, il retire les espaces en début et en fin de cellule et ignore les majuscules : les causes 1 et 3 à 5 disparaissent. Chaque cellule est lue comme du texte, donc un EAN reste un EAN de 13 chiffres de l'import à l'export.

Les références réellement absentes ne sont pas cachées derrière une erreur : elles sont comptées dans « seulement dans A », et un clic sur ce compteur n'affiche qu'elles. Un code qui a déjà perdu son zéro dans le fichier d'origine reste en revanche différent : il apparaît lui aussi dans « seulement dans A », ce qui le rend visible. La méthode complète est dans Comment fusionner deux fichiers Excel.