RECHERCHEV ne fonctionne pas : corriger #N/A quand la valeur existe

RECHERCHEV renvoie #N/A ou une valeur fausse ? Testez nombres en texte, espaces cachés, valeur proche et plage non figée, puis corrigez ou passez à RECHERCHEX.

Fondateur, Glow Reports. , 4 min de lecture
  • Excel
  • Qualité des données
  • Reporting
Deux piles de fiches, et une fiche sans correspondance mise à part sous une loupe
Sur cette page
  1. Pourquoi ma RECHERCHEV ne fonctionne pas ?
  2. Pourquoi RECHERCHEV renvoie #N/A alors que la valeur existe ?
  3. Pourquoi RECHERCHEV renvoie une mauvaise valeur ?
  4. Pourquoi la formule marche en haut et échoue plus bas ?
  5. Que faire si l'identifiant n'est pas dans la première colonne ?
  6. Comment afficher un vide au lieu de #N/A ?
  7. Faut-il passer à RECHERCHEX ?
  8. Où se place Glow Reports ?
  9. Questions fréquentes

Vous saisissez une RECHERCHEV, l'identifiant figure bien dans l'autre tableau, et Excel répond #N/A. Ou bien la formule renvoie une valeur, mais pas la bonne. Presque tous les cas se ramènent à cinq causes. La correspondance approximative est restée active, un des deux côtés stocke les nombres en texte, des espaces invisibles traînent dans la clé, l'identifiant n'est pas dans la première colonne, ou la plage s'est décalée pendant la recopie. Chaque cause a son test et sa correction, et les mêmes tests marchent dans Google Sheets.

Les exemples qui suivent cherchent l'offre d'un client. L'export des commandes est sur une feuille, avec les identifiants clients en colonne A. La liste des clients est sur une feuille nommée , avec l'identifiant en A, le nom en B et l'offre en C.

La recherche que l'article corrige
=RECHERCHEV(A2;Clients!$A$2:$C$500;3;FAUX)

Pourquoi ma RECHERCHEV ne fonctionne pas ?

Parce que la valeur cherchée et la valeur du tableau ne sont pas identiques, ou parce que la formule cherche au mauvais endroit. Repérez votre symptôme dans le tableau, puis passez à la section qui le corrige.

SymptômeCause probableCorrection
#N/A pour une valeur visible dans le tableauNombre stocké en texte d'un côté, ou espaces en tropConvertir le type, ou entourer la clé de SUPPRESPACE
Une valeur revient, mais ce n'est pas la bonneDernier argument absent, la recherche est approximativeAjouter FAUX en dernier argument
Les premières lignes marchent, pas les suivantesLa plage a glissé pendant la recopieFiger la plage avec des dollars
#N/A sur toutes les lignesL'identifiant n'est pas dans la première colonneDéplacer la plage, ou passer à RECHERCHEX
#REF!Numéro de colonne plus grand que la plageRecompter les colonnes
#N/A pour des valeurs absentesLa valeur n'est pas dans le tableauAfficher un vide ou un libellé avec SI.NON.DISP

Pourquoi RECHERCHEV renvoie #N/A alors que la valeur existe ?

Les deux cellules se ressemblent mais ne contiennent pas la même valeur. Dans la plupart des exports, un fichier stocke l'identifiant en nombre et l'autre en texte, ou un côté porte un espace que vous ne voyez pas. Excel compare la valeur stockée, pas ce qui s'affiche.

Testez avant de corriger. Dans une cellule vide, saisissez , puis . Si l'un renvoie VRAI et l'autre FAUX, les types diffèrent. Microsoft cite cette cause en premier : la valeur cherchée et les données source n'ont pas le même type Support Microsoft.

Deux façons de corriger un type différent :

  • Convertir la colonne une fois pour toutes. Sélectionnez-la, choisissez le format voulu avec Ctrl+1, puis lancez DonnéesConvertirTerminer pour qu'Excel réapplique le type à chaque cellule.
  • Convertir dans la formule. Si le tableau contient des nombres et votre clé du texte, cherchez . Si le tableau contient du texte et votre clé un nombre, cherchez .
Clé en texte, tableau en nombres
=RECHERCHEV(CNUM(A2);Clients!$A$2:$C$500;3;FAUX)

Les espaces sont l'autre suspect habituel. Comparez au nombre de caractères attendu. Un caractère en trop signale un espace en fin de cellule. SUPPRESPACE retire les espaces en début et en fin de texte et réduit les espaces doubles Excel-Pratique. Un espace copié depuis une page web est souvent un espace insécable, le caractère 160, que SUPPRESPACE ne retire pas. Remplacez-le d'abord : .

Si c'est la colonne du tableau qui est sale, nettoyez-la une fois plutôt que de corriger chaque formule. La méthode complète pour les espaces, les nombres en texte et les dates se trouve dans nettoyer ses données dans Excel.

Pourquoi RECHERCHEV renvoie une mauvaise valeur ?

Le dernier argument manque ou vaut VRAI, donc RECHERCHEV cherche une valeur proche. Elle renvoie la valeur inférieure la plus proche au lieu de signaler qu'elle n'a rien trouvé. Mettez FAUX en dernier argument pour toute recherche d'identifiant, d'e-mail ou de référence produit.

La correspondance approximative s'applique par défaut quand l'argument est omis, et elle suppose une première colonne triée par ordre croissant Support Microsoft. Sur un export non trié, elle renvoie des offres plausibles mais fausses, ce qui est pire qu'une erreur, car personne ne le remarque. La recherche approximative garde un usage légitime : trouver une valeur dans une tranche, comme un taux de commission selon le montant vendu, dans un tableau trié par borne basse.

Pourquoi la formule marche en haut et échoue plus bas ?

La plage du tableau n'est pas figée. Recopiée d'une ligne, devient , puis , et le haut de la liste clients sort de la recherche. Les premiers clients de la liste renvoient alors #N/A dans les lignes du bas.

Ajoutez des dollars pour que la plage ne bouge plus : . Sélectionnez la plage dans la formule et appuyez sur F4 pour les ajouter Support Microsoft. La valeur cherchée, , reste relative et suit chaque ligne. Un tableau mis en forme avec Ctrl+T évite aussi le problème, car ses références nomment la colonne au lieu des cellules.

Que faire si l'identifiant n'est pas dans la première colonne ?

RECHERCHEV ne cherche que dans la première colonne de la plage et renvoie une valeur située à sa droite. Si l'identifiant est en colonne B et que la plage commence en A, chaque ligne renvoie #N/A. Faites commencer la plage à la colonne de l'identifiant, ou passez à RECHERCHEX, qui cherche dans n'importe quelle colonne.

Le numéro de colonne se compte depuis le bord gauche de la plage, pas depuis la colonne A de la feuille Support Microsoft. Avec , l'offre en colonne D est la colonne 3. Un numéro plus grand que la plage renvoie #REF!. Si quelqu'un insère une colonne dans la feuille clients, l'offre passe en colonne 4 alors que la formule demande toujours la 3, et le résultat change sans aucune erreur. RECHERCHEX désigne directement la colonne à renvoyer, donc une colonne insérée ne la casse pas.

Comment afficher un vide au lieu de #N/A ?

Entourez la recherche de SI.NON.DISP, une fois vérifié que les valeurs manquantes sont bien absentes du tableau. SI.NON.DISP ne remplace que l'erreur #N/A, donc un #REF! ou un #VALEUR! dû à une formule cassée reste visible.

L'offre, ou le mot Inconnu
=SI.NON.DISP(RECHERCHEV(A2;Clients!$A$2:$C$500;3;FAUX);"Inconnu")

SI.NON.DISP renvoie la valeur que vous indiquez quand la formule renvoie #N/A Support Microsoft. SIERREUR fonctionne aussi, mais masque toutes les erreurs, y compris une plage mal saisie. Comptez les « Inconnu » avant d'envoyer le rapport. S'il y en a beaucoup, reprenez les tests de type et d'espaces ci-dessus. Pour lister les identifiants absents de l'autre tableau, suivez les méthodes de comparer deux colonnes Excel.

Faut-il passer à RECHERCHEX ?

Oui, si votre version d'Excel la propose. RECHERCHEX cherche une correspondance exacte par défaut, dans n'importe quelle colonne, renvoie la colonne que vous désignez et prend un quatrième argument pour le cas sans résultat. Elle existe dans Excel pour Microsoft 365 et Excel 2021 ou plus récent, pas dans Excel 2016 ni 2019.

La même recherche avec RECHERCHEX
=RECHERCHEX(A2;Clients!$A$2:$A$500;Clients!$C$2:$C$500;"Inconnu")

Microsoft indique la correspondance exacte comme mode par défaut et décrit le texte renvoyé quand rien n'est trouvé Support Microsoft. RECHERCHEX échoue elle aussi sur un nombre stocké en texte ou un espace final, donc les tests ci-dessus valent pour les deux fonctions. Google Sheets propose la même recherche verticale, dont le dernier argument vaut aussi correspondance approximative par défaut Aide Éditeurs Google Docs, et l'équivalent de RECHERCHEX Aide Éditeurs Google Docs.

Où se place Glow Reports ?

Les corrections ci-dessus marchent dans toutes les versions d'Excel et dans Google Sheets, et une recherche lancée une fois par mois y a toute sa place. Glow Reports aide quand la recherche n'est que la première étape d'un rapport.

Prenez le chiffre d'affaires par offre à partir d'un export de commandes et d'une liste clients. Vous importez les deux fichiers, CSV ou Excel, et vous demandez le chiffre en langage courant. L'IA écrit le SQL qui rapproche les deux fichiers, construit les graphiques et rédige le texte. Le SQL figure sous chaque chiffre : vous voyez sur quelles colonnes il a fait la correspondance, et vous modifiez la requête s'il s'est trompé de colonne. Un import CSV détecte son séparateur, virgule, point-virgule ou tabulation. Les rapports s'exportent en PDF et restent dans l'espace de travail. L'offre gratuite donne 300 crédits une fois, et un rapport coûte 100 crédits.

Questions fréquentes

Pourquoi RECHERCHEV renvoie #N/A alors que la valeur existe ?

La valeur de votre cellule et celle du tableau ne sont pas stockées de la même façon, le plus souvent un nombre face à du texte, ou un espace en fin de cellule. Testez les deux cellules avec ESTNUM et NBCAR, puis convertissez le type ou entourez la clé de SUPPRESPACE.

Que veut dire FAUX à la fin de RECHERCHEV ?

FAUX demande une correspondance exacte. Sans lui, RECHERCHEV utilise une correspondance approximative et renvoie la valeur inférieure la plus proche d'une première colonne triée, ce qui donne des résultats faux sur une liste non triée.

RECHERCHEV peut-elle chercher vers la gauche ?

Non. RECHERCHEV renvoie une valeur d'une colonne située à droite de celle où elle cherche. Utilisez RECHERCHEX, ou INDEX avec EQUIV dans les anciennes versions, pour renvoyer une valeur d'une colonne à gauche.

Pourquoi ma RECHERCHEV marche sur une feuille et pas après recopie ?

La plage du tableau est relative, elle se déplace quand vous copiez ou recopiez la formule. Figez-la avec des dollars, , ou faites pointer la formule vers un tableau mis en forme.

El Mehdi Benane
Fondateur, Glow Reports

Je suis ingénieur produit et fondateur de Glow Reports. J'ai passé plus de 6 ans à construire des produits dans la tech, les données et la cybersécurité, pour des grands groupes, des startups et des PME. Dans la plupart des organisations, j'ai vu les données traitées comme un sujet technique, alors qu'elles guident les décisions. J'ai construit l'outil de reporting que je cherchais, pour que les dirigeants des petites et moyennes structures lisent leurs chiffres sans équipe data.

Importez votre export de commandes et votre liste clients dans Glow Reports et demandez le chiffre, avec la jointure SQL sous chaque graphique.

À lire aussi