Fonction VLOOKUP d'Excel

La fonction VLOOKUP d'Excel est un outil puissant qui vous aide à rechercher une valeur spécifiée en faisant correspondre la première colonne d'un tableau ou d'une plage verticalement, puis à retourner une valeur correspondante d'une autre colonne dans la même ligne. Bien que VLOOKUP soit incroyablement utile, il peut parfois être difficile à comprendre pour les débutants. Ce tutoriel vise à vous aider à maîtriser VLOOKUP en fournissant une explication étape par étape des arguments, des exemples utiles et des solutions aux erreurs courantes que vous pourriez rencontrer lors de l'utilisation de la fonction VLOOKUP.

• Explication étape par étape des arguments
• Syntaxe et arguments
• Exemples de VLOOKUP
  Correspondance exacte vs. correspondance approximative | VLOOKUP avec multi-conditions
• Erreurs courantes de VLOOKUP et solutions
  Erreur #N/A | Erreur #VALUE | Erreur #REF | Valeur incorrecte

Exemples de VLOOKUP .xlsx Erreurs de VLOOKUP .xlsx

Vidéos associées

Explication étape par étape des arguments

Comme montré dans la capture d'écran ci-dessus, la fonction VLOOKUP est utilisée pour trouver un e-mail basé sur un numéro d'identification donné. Je vais maintenant fournir une explication détaillée de l'utilisation de VLOOKUP dans cet exemple en décomposant chaque argument étape par étape.

Étape1 : Démarrer la fonction VLOOKUP

Sélectionnez une cellule (H6 dans ce cas) pour sortir le résultat, puis démarrez la fonction VLOOKUP en tapant le contenu suivant dans la Barre de formule.

=VLOOKUP(

Étape2 : Spécifier la valeur de recherche

Tout d'abord, spécifiez la valeur de recherche (ce que vous recherchez) dans la fonction VLOOKUP. Ici, je fais référence à la cellule G6 qui contient un certain numéro d'identification1005.

=VLOOKUP(G6

Étape3 : Spécifier la plage de tableau

Ensuite, spécifiez une plage de cellules contenant à la fois la valeur que vous recherchez et la valeur que vous souhaitez retourner. Dans ce cas, je sélectionne la plage B6:E12. La formule apparaît maintenant comme suit :

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Étape4 : Spécifier la colonne à partir de laquelle vous souhaitez retourner une valeur

Ensuite, spécifiez la colonne à partir de laquelle vous souhaitez retourner une valeur.

Dans cet exemple, comme je dois retourner l'e-mail basé sur un numéro d'identification, ici j'entre un numéro4 pour indiquer à VLOOKUP de retourner une valeur de la quatrième colonne de la plage de données.

=VLOOKUP(G6,B6:E12,4

Étape5 : Trouver une correspondance approximative ou exacte

Enfin, déterminez si vous recherchez une correspondance approximative ou exacte.

• Pour trouver une correspondance exacte, vous devez utiliser FALSE comme dernier argument.
• Pour trouver une correspondance approximative, utilisez TRUE comme dernier argument, ou laissez-le simplement vide.

Dans cet exemple, j'utilise FALSE pour une correspondance exacte. La formule ressemble maintenant à ceci :

=VLOOKUP(G6,B6:E12,4,FALSE

Appuyez sur la touche Entrée pour obtenir le résultat

En expliquant chaque argument un par un dans l'exemple ci-dessus, la syntaxe et les arguments de la fonction VLOOKUP sont maintenant beaucoup plus faciles à comprendre.

Syntaxe et arguments

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Lookup_value (requis) : La valeur (une valeur réelle ou une référence de cellule) que vous recherchez. Rappelez-vous que cette valeur doit être dans la première colonne de la table_array.
• Table_array (requis) : Une plage de cellules contient à la fois la colonne de la valeur de recherche et la colonne de la valeur de retour.
• Col_index (requis) : Un entier représente le numéro de colonne qui contient la valeur de retour. Il commence par le numéro1 pour la colonne la plus à gauche de la table_array.
• Range_lookup (facultatif) : Une valeur logique qui détermine si vous souhaitez que VLOOKUP trouve une correspondance approximative ou exacte.
  • Correspondance approximative - Définissez cet argument sur TRUE, 1 ou laissez-le vide.
    Important: Pour trouver une correspondance approximative, les valeurs dans la première colonne de la table_array doivent être triées par ordre croissant au cas où VLOOKUP retournerait le mauvais résultat.
  • Correspondance exacte - Définissez cet argument sur FALSE ou0.

Exemples

Cette section présente quelques exemples pour vous aider à avoir une compréhension plus complète de la fonction VLOOKUP.

Exemple1 : Correspondance exacte vs. correspondance approximative dans VLOOKUP

Si vous êtes confus à propos de la correspondance exacte et de la correspondance approximative lors de l'utilisation de VLOOKUP, cette section peut vous aider à dissiper cette confusion.

Correspondance exacte dans VLOOKUP

Dans cet exemple, je vais trouver les noms correspondants basés sur les scores listés dans la plage E6:E8, donc j'entre la formule suivante dans la cellule F6 et je fais glisser la poignée de remplissage automatique vers le bas jusqu'à F8. Dans cette formule, le dernier argument est spécifié comme FALSE pour effectuer une recherche de correspondance exacte.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Cependant, comme le score98 n'existe pas dans la première colonne de la plage de données, VLOOKUP retourne un résultat d'erreur #N/A.

Correspondance approximative dans VLOOKUP

En utilisant toujours l'exemple ci-dessus, si vous changez le dernier argument en TRUE, VLOOKUP effectuera une recherche de correspondance approximative. Si aucune correspondance n'est trouvée, il trouvera la prochaine valeur la plus grande qui est inférieure à la valeur de recherche et retournera le résultat correspondant.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Comme le score98 n'existe pas, VLOOKUP trouve la prochaine valeur la plus grande qui est inférieure à98, qui est95, et retourne le nom du score95 comme résultat le plus proche.

Exemple2 : Utiliser VLOOKUP avec plusieurs critères

Cette section montre comment utiliser VLOOKUP avec plusieurs conditions dans Excel. Comme montré dans la capture d'écran ci-dessous, si vous essayez de localiser un salaire basé sur un nom fourni (dans la cellule H5) et un département (dans la cellule H6), suivez les étapes ci-dessous pour le faire.

Étape1 : Ajouter une colonne d'assistance pour concaténer les valeurs des colonnes de recherche

Dans ce cas, nous devons créer une colonne d'assistance pour concaténer les valeurs de la colonne des Noms et de la colonne des Départements.

1. Ajoutez une colonne d'assistance à gauche de votre plage de données et donnez un en-tête à cette colonne. Voir la capture d'écran :
   
   
2. Dans cette colonne d'assistance, sélectionnez la première cellule sous l'en-tête, entrez la formule suivante dans la Barre de formule, et appuyez sur Entrée.
   

   =C6&" "&D6

   
   Notes : Dans cette formule, nous utilisons un esperluette (&) pour joindre le texte dans deux colonnes afin de produire un seul morceau de texte.

   • C6 est le prénom de la colonne des Noms à joindre, D6 est le premier département de la colonne des Départements à joindre.
   • Les valeurs de ces deux cellules sont concaténées avec un espace entre elles.
3. Sélectionnez cette cellule de résultat, puis faites glisser la Poignée de remplissage automatique vers le bas pour appliquer cette formule à d'autres cellules dans la même colonne.
   
   

Étape2 : Appliquer la fonction VLOOKUP avec les critères donnés

Sélectionnez une cellule où vous souhaitez sortir le résultat (ici je sélectionne I7), entrez la formule suivante dans la Barre de formule, puis appuyez sur Entrée.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Résultat

Erreurs courantes de VLOOKUP et solutions

Cette section répertorie les erreurs courantes que vous pouvez rencontrer lors de l'utilisation de VLOOKUP et fournit les solutions pour les corriger.

Erreur #N/A retournée

L'erreur la plus courante avec VLOOKUP est l'erreur #N/A, ce qui signifie qu'Excel n'a pas pu trouver la valeur que vous recherchiez. Voici quelques raisons pour lesquelles VLOOKUP peut retourner une erreur #N/A.

Raison1 : La valeur de recherche n'est pas dans la première colonne de la table_array

Une des limitations de VLOOKUP d'Excel est qu'il ne vous permet de rechercher que de gauche à droite. Ainsi, les valeurs de recherche doivent être dans la première colonne de la table_array.

Comme montré dans la capture d'écran ci-dessous, je veux retourner un nom basé sur la fonction donnée. Ici, la valeur de recherche (sales manager) est dans la deuxième colonne de la table_array et la valeur de retour est à gauche de la colonne de recherche, donc VLOOKUP retourne une erreur #N/A.

Solutions

Vous pouvez appliquer l'une des solutions suivantes pour corriger cette erreur.

• Réorganiser les colonnes
  
  Vous pouvez réorganiser les colonnes pour placer la colonne de recherche dans la première colonne de la table_array.
• Utiliser les fonctions INDEX et MATCH ensemble
  
  Ici, nous utilisons les fonctions INDEX et MATCH ensemble comme alternative à VLOOKUP pour résoudre ce problème.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Utiliser la fonction XLOOKUP (disponible dans Excel365, Excel2021 et versions ultérieures)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Raison2 : La valeur de recherche n'est pas trouvée dans la colonne de recherche (correspondance exacte)

Une des raisons les plus courantes pour lesquelles VLOOKUP retourne une erreur #N/A est que la valeur que vous recherchez n'est pas trouvée.

Comme montré dans l'exemple ci-dessous, nous allons trouver le nom basé sur le score donné de98 dans E6. Cependant, ce score n'existe pas dans la première colonne de la plage de données, donc VLOOKUP retourne un résultat d'erreur #N/A.

Solutions

Pour corriger cette erreur, vous pouvez essayer l'une des solutions suivantes.

• Si vous voulez que VLOOKUP recherche la prochaine valeur la plus grande qui est inférieure à la valeur de recherche, changez le dernier argument FALSE (correspondance exacte) en TRUE (correspondance approximative). Pour plus d'informations, veuillez consulter l'Exemple1 : Correspondance exacte vs. correspondance approximative utilisant VLOOKUP.
• Pour éviter de changer le dernier argument et obtenir un rappel au cas où la valeur de recherche n'est pas trouvée, vous pouvez inclure la fonction VLOOKUP dans la fonction IFERROR :
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Raison3 : La valeur de recherche est inférieure à la plus petite valeur dans la colonne de recherche (correspondance approximative)

Comme montré dans la capture d'écran ci-dessous, vous effectuez une recherche de correspondance approximative. La valeur que vous recherchez (le numéro d'identification1001 dans ce cas) est inférieure à la plus petite valeur1002 dans la colonne de recherche, donc VLOOKUP retourne une erreur #N/A.

Solutions

Voici deux solutions pour vous.

• Assurez-vous que la valeur de recherche est supérieure ou égale à la plus petite valeur dans la colonne de recherche.
• Si vous voulez qu'Excel vous rappelle que la valeur de recherche n'a pas été trouvée, il suffit de nicher la fonction VLOOKUP dans la fonction IFERROR comme suit :
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Raison4 : Les nombres sont formatés en texte

Comme vous pouvez le voir dans la capture d'écran ci-dessous, le résultat d'erreur #N/A dans cet exemple est dû à une incompatibilité de type de données entre la cellule de recherche (G6) et la colonne de recherche (B6:B12) du tableau original. Ici, la valeur dans G6 est un nombre, et les valeurs dans la plage B6:B12 sont des nombres formatés en texte.

Solutions

Pour résoudre ce problème, vous devez convertir la valeur de recherche en nombre. Voici deux méthodes pour vous.

• Appliquer la fonctionnalité Convertir en nombre
  
  Cliquez sur la cellule que vous souhaitez convertir le texte en nombre, sélectionnez ce bouton  à côté de la cellule puis sélectionnez Convertir en nombre.
  
• Appliquer un outil pratique pour convertir en lot entre texte et nombre
  
  La fonctionnalité Convertir entre texte et nombres de Kutools pour Excel vous aide à convertir facilement une plage de cellules de texte en nombre et vice versa. Obtenez un essai gratuit complet de30 jours maintenant !

Raison5 : La table_array n'est pas constante lors du glissement de la formule VLOOKUP vers d'autres cellules

Comme montré dans la capture d'écran ci-dessous, il y a deux valeurs de recherche dans E6 et E7. Après avoir obtenu le premier résultat dans F6, faites glisser la formule VLOOKUP de la cellule F6 à F7, un résultat d'erreur #N/A est retourné. C'est parce que les références de cellules (B6:C12) sont relatives par défaut, et ajustées au fur et à mesure que vous descendez dans les lignes. La plage de tableau a été déplacée vers le bas à B7:C13, qui ne contient plus le score de recherche73.

Solution

Vous devez verrouiller la plage de tableau pour la garder constante en ajoutant un signe $ avant les lignes et les colonnes dans les références de cellules. Pour en savoir plus sur la référence absolue dans Excel, consultez ce tutoriel : Référence absolue Excel (comment faire et utiliser).

Erreur #VALUE retournée

Les conditions suivantes peuvent amener VLOOKUP à retourner un résultat d'erreur #VALUE.

Raison1 : La valeur de recherche dépasse255 caractères

Comme montré dans la capture d'écran ci-dessous, la valeur de recherche dans la cellule H4 dépasse255 caractères, donc VLOOKUP retourne un résultat d'erreur #VALUE.

Solutions

Pour contourner cette limitation, vous pouvez appliquer une fonction de recherche différente qui peut gérer des chaînes plus longues. Essayez l'une des formules suivantes.

• INDEX et MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Fonction XLOOKUP (disponible dans Excel365, Excel2021 et versions ultérieures) :
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Raison2 : L'argument col_index est inférieur à1

L'index de colonne spécifie le numéro de colonne dans la plage de tableau qui contient la valeur que vous souhaitez retourner. Cet argument doit être un nombre positif qui correspond à une colonne valide dans la plage de tableau.

Si vous entrez un index de colonne inférieur à1 (c'est-à-dire zéro ou négatif), VLOOKUP ne pourra pas localiser la colonne dans la plage de tableau.

Solution

Pour résoudre ce problème, assurez-vous que l'argument d'index de colonne dans votre formule VLOOKUP est un nombre positif qui correspond à une colonne valide dans la plage de tableau.

Erreur #REF retournée

Cette section répertorie une raison pour laquelle VLOOKUP retourne une erreur #REF et fournit des solutions à ce problème.

Raison : L'argument col_index est supérieur au nombre de colonnes

Comme vous pouvez le voir dans la capture d'écran ci-dessous, la plage de tableau n'a que4 colonnes. Cependant, l'index de colonne que vous avez spécifié dans la formule VLOOKUP est5, ce qui est supérieur au nombre de colonnes dans la plage de tableau. En conséquence, VLOOKUP ne pourra pas localiser la colonne et retournera finalement une erreur #REF.

Solutions

• Spécifiez un numéro de colonne correct
  Assurez-vous que l'argument d'index de colonne dans votre formule VLOOKUP est un nombre qui correspond à une colonne valide dans la plage de tableau.
• Obtenez automatiquement le numéro de colonne basé sur l'en-tête de colonne spécifié
  Si le tableau contient de nombreuses colonnes, vous pouvez avoir du mal à déterminer le numéro d'index de colonne correct. Ici, vous pouvez nicher la fonction MATCH dans la fonction VLOOKUP pour trouver la position de la colonne basée sur un certain en-tête de colonne.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Note : Dans la formule ci-dessus, la fonction MATCH("Email",B5:E5,0) est utilisée pour obtenir le numéro de colonne de la colonne "Email" dans la plage de données B6:E12. Ici, le résultat est4, qui est utilisé comme col_index dans la fonction VLOOKUP.

Valeur incorrecte retournée

Si vous constatez que VLOOKUP ne retourne pas le résultat correct, cela peut être causé par les raisons suivantes

Raison1 : La colonne de recherche n'est pas triée par ordre croissant

Si vous avez défini le dernier argument sur TRUE (ou laissé vide) pour une correspondance approximative, et que la colonne de recherche n'est pas triée par ordre croissant, la valeur résultante peut être incorrecte.

Solution

Trier la colonne de recherche par ordre croissant peut vous aider à résoudre ce problème. Pour ce faire, veuillez suivre les étapes ci-dessous :

1. Sélectionnez les cellules de données dans la colonne de recherche, allez à l'onglet Données, cliquez sur Trier du plus petit au plus grand dans le groupe Trier et Filtrer.
2. Dans la boîte de dialogue Avertissement de tri, sélectionnez l'option Développer la sélection, et cliquez sur OK.

Raison2 : Une colonne est insérée ou supprimée

Comme montré dans la capture d'écran ci-dessous, la valeur que je voulais initialement retourner est dans la quatrième colonne de la plage de tableau, donc je spécifie le numéro d'index de colonne comme4. Comme une nouvelle colonne est insérée, la colonne de résultat devient la cinquième colonne de la plage de tableau, ce qui amène VLOOKUP à retourner le résultat d'une mauvaise colonne.

Solutions

Voici deux solutions pour vous.

• Vous pouvez changer manuellement le numéro d'index de colonne pour correspondre à la position de la colonne de retour. La formule ici devrait être changée en :

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Si vous souhaitez toujours retourner le résultat d'une certaine colonne, comme la colonne Email dans cet exemple. La formule suivante peut aider à faire correspondre automatiquement l'index de colonne basé sur l'en-tête de colonne donné, peu importe si des colonnes sont insérées ou supprimées de la plage de tableau.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Autres notes de fonction

• VLOOKUP recherche uniquement la valeur de gauche à droite.
  La valeur de recherche est dans la colonne la plus à gauche, et la valeur de résultat doit être dans n'importe quelle colonne à droite de la colonne de recherche.
• Si vous laissez le dernier argument vide, VLOOKUP utilise par défaut la correspondance approximative.
• VLOOKUP effectue une recherche insensible à la casse.
• Pour plusieurs correspondances, VLOOKUP retourne uniquement la première correspondance qu'il trouve dans la table_array, basée sur l'ordre des lignes dans la table_array.

Articles associés