Erreur #REF! dans RECHERCHEV : comment la corriger
#REF!RECHERCHEVRECHERCHEV est l'une des formules les plus utilisées en Excel, mais elle génère régulièrement l'erreur #REF! qui bloque vos analyses. Cette erreur apparaît généralement quand la plage de données référencée est supprimée, quand des colonnes sont effacées, ou quand le numéro de colonne dépasse les limites de votre table_matrice. Elle survient aussi lors de manipulations maladroites : copier-coller une formule sans adapter les références, ou importer des données qui modifient la structure du tableau. Bonne nouvelle : #REF! dans RECHERCHEV est un problème extrêmement courant et facile à corriger une fois qu'on en comprend les causes. Quelques vérifications rapides suffisent généralement à identifier et résoudre le problème. Cet article vous guide à travers les solutions pratiques pour éliminer définitivement cette erreur frustrante.
Pourquoi RECHERCHEV cause #REF!
Suppression de colonnes dans la plage de référence
Vous avez supprimé une colonne entre la colonne de recherche et la colonne d'index. RECHERCHEV compte les colonnes depuis le début de la plage, donc si vous supprimez une colonne, l'index pointe vers une colonne inexistante ou vers une référence cassée. Le numéro de colonne devient invalide par rapport à la structure actuelle.
=RECHERCHEV(A2;B:D;3;FAUX) où vous aviez initialement B:E, puis supprimé la colonne D - maintenant l'index 3 ne correspond plus à la bonne colonneRéférence de plage invalide ou modifiée
La plage de table_matrice contient une référence cassée, souvent due à la suppression de feuilles, au renommage de feuilles, ou à une plage mal définie qui inclut des références circulaires. RECHERCHEV ne peut pas accéder à une plage qui n'existe plus ou qui pointe vers une feuille supprimée.
=RECHERCHEV(A2;Feuille2!A:C;2;FAUX) où la 'Feuille2' a été supprimée, ou =RECHERCHEV(A2;A:C;2;FAUX) où la plage a des références corrompuesNuméro d'index supérieur au nombre de colonnes
Le paramètre no_index_col dépasse le nombre réel de colonnes dans la plage table_matrice. Par exemple, si votre plage contient 3 colonnes mais que vous demandez la colonne 5, RECHERCHEV génère #REF! car cette colonne n'existe pas physiquement dans la plage définie.
=RECHERCHEV(A2;B2:D100;5;FAUX) où la plage B2:D100 n'a que 3 colonnes mais l'index demande la colonne 5Solution pas à pas
- 1Cliquez sur la cellule affichant #REF! et appuyez sur F2 pour entrer en mode édition et examiner la formule complète dans la barre de formule
- 2Identifiez la plage de recherche (argument 2) : vérifiez que les références de colonnes ne contiennent pas de colonnes supprimées ou de références cassées en consultant l'historique des modifications (Ctrl+Z pour revenir en arrière si nécessaire)
- 3Testez chaque argument séparément en créant une formule intermédiaire : saisissez =RECHERCHEV(A2,B:C,2,FAUX) ligne par ligne pour isoler l'erreur (appuyez sur Entrée après chaque test)
- 4Vérifiez que l'argument numéro de colonne (argument 3) ne dépasse pas le nombre de colonnes de votre plage : si votre plage est B:C (2 colonnes), le numéro de colonne doit être 1 ou 2 maximum
- 5Corrigez les références cassées en remplaçant les plages dynamiques par des plages absolues : changez RECHERCHEV(A2,B:C,2,FAUX) en RECHERCHEV(A2,$B$1:$C$100,2,FAUX) avec Ctrl+Maj+F4 pour générer les $
- 6Si la plage source a été modifiée, recalculez la formule en appuyant sur Ctrl+Maj+F9 pour forcer le recalcul de tous les classeurs
- 7Entourez votre RECHERCHEV avec SIERREUR pour éviter l'affichage de l'erreur : =SIERREUR(RECHERCHEV(A2,$B$1:$C$100,2,FAUX),"Non trouvé")
- 8Validez la correction en appuyant sur Entrée, puis copiez la formule corrigée vers les autres cellules avec Ctrl+C et Ctrl+V
Exemple concret
Gestion des commandes clients avec recherche de tarifs
Un responsable commercial utilise RECHERCHEV pour récupérer automatiquement le prix unitaire des produits commandés en fonction de leur référence. La feuille 'Commandes' contient les références produits, et la feuille 'Tarifs' contient les prix.
Avant (erreur)
=RECHERCHEV(A2;Tarifs!A:D;3;FAUX)Après (corrigé)
=SIERREUR(RECHERCHEV(A2;Tarifs!$A$2:$D$100;2;FAUX);"Produit introuvable")Problème : L'erreur #REF! apparaît après suppression accidentelle de la colonne 2 de la table de tarifs. La formule référençait une colonne qui n'existe plus, rendant l'index invalide.
Solution : Reconstruire la table de tarifs avec toutes les colonnes nécessaires OU modifier l'index pour pointer vers une colonne existante. Utiliser SIERREUR pour afficher un message explicite au lieu de l'erreur.
Conseil de prévention
Vérifiez que la plage de recherche n'a pas de colonnes supprimées ou décalées après la création de votre formule, car RECHERCHEV référence des colonnes par numéro qui deviennent invalides si vous en supprimez. Utilisez RECHERCHEX ou INDEX/MATCH pour plus de flexibilité si votre structure de données change régulièrement.
Outils gratuits pour corriger vos formules
Utilisez ces outils gratuits pour éviter cette erreur :
Générateur de Formules Excel
Décrivez ce que vous voulez calculer et obtenez la formule Excel instantanément
Générateur RECHERCHEV
Générez des formules RECHERCHEV instantanément en décrivant votre besoin en français
Expliqueur de Formules Excel
Collez n'importe quelle formule Excel et obtenez une explication claire, étape par étape, propulsée par l'IA.