Passer au contenu principal

Fonction Excel RECHERCHEV

Le Fonction RECHERCHEV Excel est un outil puissant qui vous aide à rechercher une valeur spécifiée en faisant correspondre verticalement la première colonne d'un tableau ou d'une plage, puis en renvoyant une valeur correspondante à partir d'une autre colonne de 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 explication étape par étape des arguments, exemples utiles et solutions aux erreurs courantes que vous pouvez rencontrer lors de l'utilisation de la fonction VLOOKUP.


Related Videos


Explication pas à pas des arguments

Comme le montre la capture d'écran ci-dessus, la fonction VLOOKUP est utilisée pour trouver un e-mail en fonction d'un numéro d'identification donné. Je vais maintenant fournir une explication détaillée de la façon d'utiliser VLOOKUP dans cet exemple en décomposant chaque argument étape par étape.

Étape 1 : Démarrer la fonction VLOOKUP

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

=VLOOKUP(
Étape 2 : Spécifiez 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'identification 1005.

=VLOOKUP(G6

Notes: la valeur de recherche doit se trouver dans la première colonne de la plage de données.
Étape 3 : Spécifiez le tableau de la table

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

=VLOOKUP(G6,B6:E12

Notes: Si vous souhaitez copier la fonction VLOOKUP pour rechercher plusieurs valeurs dans la même colonne et obtenir des résultats différents, vous devez utiliser des références absolues en ajoutant le signe dollar, comme ceci :
=VLOOKUP(G6,$B$6:$E$12
Étape 4 : Spécifiez la colonne à partir de laquelle vous souhaitez renvoyer une valeur

Spécifiez ensuite la colonne à partir de laquelle vous souhaitez renvoyer une valeur.

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

=VLOOKUP(G6,B6:E12,4

Étape 5 : Trouver une correspondance approximative ou exacte

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

  • Pour trouver un correspondance exacte, vous devez utiliser FAUX comme dernier argument.
  • Pour trouver un correspondance approximative, utilisation VRAI 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])

  • Valeur_de recherche (obligatoire) : la valeur (une valeur réelle ou une référence de cellule) que vous recherchez. N'oubliez pas que cette valeur doit se trouver dans la première colonne de table_array.
  • Tableau_tableau (obligatoire) : une plage de cellules contient à la fois la colonne de la valeur de recherche et la colonne de la valeur de retour.
  • Col_index (obligatoire) : un entier représente le numéro de colonne qui contient la valeur de retour. Il commence par le numéro 1 pour la colonne la plus à gauche du tableau_table.
  • Range_lookup (facultatif) : une valeur logique qui détermine si vous voulez que VLOOKUP trouve une correspondance approximative ou une correspondance exacte.
    • Correspondance approximative - Définissez cet argument sur VRAI, 1 ou laissez-le vide.
      Important: Pour trouver une correspondance approximative, les valeurs de la première colonne de table_array doivent être triées par ordre croissant au cas où RECHERCHEV renvoie le mauvais résultat.
    • Correspondance exacte - Définissez cet argument sur FAUX or 0.

Exemples

Cette section présente quelques exemples pour vous aider à mieux comprendre la fonction VLOOKUP.

Exemple 1 : Correspondance exacte et correspondance approximative dans VLOOKUP

Si vous êtes confus au sujet 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 en fonction des scores répertoriés dans la plage E6: E8, donc j'entre la formule suivante dans la cellule F6 et fais glisser la poignée de remplissage automatique vers F8. Dans cette formule, le dernier argument est spécifié comme FAUX pour effectuer une recherche de correspondance exacte.

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

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

Notes: Ici, j'ai verrouillé le tableau de table ($B$6:$C$12) dans la fonction VLOOKUP afin de référencer rapidement un cohérent ensemble de données par rapport à plusieurs valeurs de recherche.
Correspondance approximative dans VLOOKUP

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

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

Puisque le score 98 n'existe pas, VLOOKUP trouve la valeur la plus grande suivante qui est inférieure à 98, qui est 95, et renvoie le nom du score 95 comme résultat le plus proche.

Notes:
  • Dans ce cas de correspondance approximative, les valeurs de la première colonne de table_array doivent être triées par ordre croissant. Sinon, RECHERCHEV risque de ne pas renvoyer la valeur correcte.
  • Ici, j'ai verrouillé le tableau de table ($B$6:$C$12) dans la fonction VLOOKUP afin de référencer rapidement un ensemble cohérent de données par rapport à plusieurs valeurs de recherche.

Exemple 2 : Utiliser VLOOKUP avec plusieurs critères

Cette section montre comment utiliser VLOOKUP avec plusieurs conditions dans Excel. Comme indiqué dans la capture d'écran ci-dessous, si vous essayez de localiser un salaire en fonction d'un nom fourni (dans la cellule H5) et d'un service (dans la cellule H6), suivez les étapes ci-dessous pour y parvenir.

Étape 1 : Ajoutez 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 Nom, Prénom colonne et le Département colonne.

  1. Ajoutez une colonne d'assistance à gauche de votre plage de données et donnez un en-tête à cette colonne. Voir 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 le Barre de formuleet appuyez sur Entrer.
    =C6&" "&D6
    Notes: Dans cette formule, nous utilisons une esperluette (&) pour joindre le texte en deux colonnes afin de produire un seul morceau de texte.
    • C6 est le prénom du Nom, Prénom colonne à joindre, D6 est le premier département de Département colonne à rejoindre.
    • 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 le Poignée de remplissage automatique vers le bas pour appliquer cette formule à d'autres cellules de la même colonne.
Étape 2 : Appliquer la fonction VLOOKUP avec les critères donnés

Sélectionnez une cellule dans laquelle vous souhaitez afficher le résultat (ici, je sélectionne I7), entrez la formule suivante dans le champ Barre de formule, puis appuyez sur Entrer.

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

Notes:
  • La colonne d'assistance doit être utilisée comme première colonne de la plage de données.
  • Maintenant, la colonne des salaires est la cinquième colonne de la plage de données, nous utilisons donc le nombre 5 comme index de colonne dans la formule.
  • Nous devons joindre les critères dans I5 et I6 (I5& " "&I6) de la même manière que la colonne d'assistance et utilisez la valeur concaténée comme valeur de recherche argument dans la formule.
  • Vous pouvez également mettre les deux conditions directement dans l'argument lookup_value et les séparer par un espace (si les conditions sont du texte, n'oubliez pas de les mettre entre guillemets).
    =VLOOKUP("Albee IT",B6:F12,5,FALSE)
  • Une meilleure alternative - recherche avec plusieurs critères en quelques secondes
    Le Recherche multi-conditions caractéristique de Kutools for Excel peut vous aider à rechercher facilement avec plusieurs critères en quelques secondes. Obtenez un essai gratuit complet de 30 jours maintenant !

Erreurs courantes de RECHERCHEV 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.

  Présentation des erreurs courantes RECHERCHEV :
          
         Raison 1 : la valeur de recherche n'est pas dans la première colonne  
     Raison 2 : la valeur de recherche est introuvable  
  ------  Raison 3 : la valeur de recherche est inférieure à la plus petite valeur  
     Raison 4 : les nombres sont formatés en tant que texte  
       Raison 5 : Table_array n'est pas constant  
         
  ------  Raison 1 : la valeur de recherche dépasse 255 caractères  
   Raison 2 : Col_index est inférieur à 1  
         
  ------  Raison 1 : Col_index est supérieur au nombre de colonnes  
   
         
  ------  Raison 1 : la colonne de recherche n'est pas triée par ordre croissant  
   Raison 2 : Une colonne est insérée ou supprimée  
         

#N/A erreur renvoyé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 RECHERCHEV peut renvoyer l'erreur #N/A.

Raison 1 : la valeur de recherche n'est pas dans la première colonne de table_array

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

Comme le montre la capture d'écran ci-dessous, je souhaite renvoyer un nom basé sur le titre du poste donné. Ici la valeur de recherche (directeur commercial) est dans la deuxième colonne de table_array et la valeur de retour est à gauche de la colonne de recherche, donc VLOOKUP renvoie l'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 table_array.
  • Utilisez 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))
  • Utilisez la fonction XLOOKUP (disponible dans Excel 365, Excel 2021 et versions ultérieures)
    =XLOOKUP(F6,C6:C12,B6:B12)

Raison 2 : la valeur de recherche est introuvable dans la colonne de recherche (correspondance exacte)

L'une des raisons les plus courantes pour lesquelles VLOOKUP renvoie l'erreur #N/A est que la valeur que vous recherchez est introuvable.

Comme le montre l'exemple ci-dessous, nous allons trouver le nom basé sur le score donné de 98 en E6. Cependant, ce score n'existe pas dans la première colonne de la plage de données, donc VLOOKUP renvoie le 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, modifiez le dernier argument FAUX (correspondance exacte) à VRAI (correspondance approximative). Pour plus d'informations, veuillez consulter Exemple 1 : Correspondance exacte ou correspondance approximative à l'aide de VLOOKUP.
  • Pour éviter de modifier le dernier argument et obtenir un rappel au cas où la valeur de recherche n'est pas trouvée, vous pouvez inclure la fonction RECHERCHEV dans la fonction SIERREUR :
    =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Raison 3 : la valeur de recherche est inférieure à la plus petite valeur de la colonne de recherche (correspondance approximative)

Comme le montre la capture d'écran ci-dessous, vous effectuez une recherche de correspondance approximative. La valeur que vous recherchez (le numéro d'identification 1001 dans ce cas) est inférieure à la plus petite valeur 1002 dans la colonne de recherche, par conséquent, VLOOKUP renvoie l'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 de la colonne de recherche.
  • Si vous souhaitez qu'Excel vous rappelle que la valeur de recherche n'a pas été trouvée, imbriquez simplement la fonction RECHERCHEV dans la fonction SIERREUR comme suit :
    =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Raison 4 : les nombres sont formatés en tant que 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) de la table d'origine. Ici, la valeur dans G6 est un nombre et les valeurs dans la plage B6:B12 sont des nombres formatés en tant que texte.

Conseil : Si un nombre est converti en texte, un petit triangle vert s'affiche dans le coin supérieur gauche de la cellule.

Solutions

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

  • Appliquer la fonctionnalité Convertir en nombre
    Cliquez sur la cellule dont vous souhaitez convertir le texte en nombre, sélectionnez ce bouton  à côté de la cellule, puis sélectionnez Convertir en nombre.
  • Appliquez un outil pratique pour convertir par lots entre le texte et le nombre
    Le Convertir entre texte et nombre caractéristique de Kutools for Excel vous aide à convertir facilement une plage de cellules de texte en nombre et vice versa. Obtenez un essai gratuit complet de 30 jours maintenant !

Raison 5 : Le tableau_table n'est pas constant lorsque vous faites glisser la formule VLOOKUP vers d'autres cellules

Comme le montre la capture d'écran ci-dessous, il existe deux valeurs de recherche dans E6 et E7. Après avoir obtenu le premier résultat dans F6, faites glisser la formule RECHERCHEV de la cellule F6 à F7, un résultat d'erreur #N/A est renvoyé. En effet, les références de cellule (B6:C12) sont relatives par défaut et ajustées au fur et à mesure que vous vous déplacez dans les lignes. Le tableau de table a été déplacé vers B7:C13, qui ne contient plus le score de recherche 73.

Solution

Vous devez verrouiller le tableau de table pour le garder constant en ajoutant un $ signe avant les lignes et les colonnes dans les références de cellule. Pour en savoir plus sur la référence absolue dans Excel, jetez un œil à ce tutoriel : Référence absolue Excel (comment faire et utiliser).

Erreur #VALUE renvoyée

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

Raison 1 : la valeur de recherche dépasse 255 caractères

Comme indiqué dans la capture d'écran ci-dessous, la valeur de recherche dans la cellule H4 dépasse 255 caractères, donc VLOOKUP renvoie 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 CORRESPONDANCE:
    =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
  • Fonction RECHERCHEX (disponible dans Excel 365, Excel 2021 et versions ultérieures) :
    =XLOOKUP(H4,B5:B11,E5:E11)

Raison 2 : L'argument col_index est inférieur à 1

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

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

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 le tableau de la table.

Erreur #REF renvoyée

Cette section répertorie une des raisons pour lesquelles VLOOKUP renvoie l'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, le tableau du tableau ne comporte que 4 colonnes. Cependant, l'index de colonne que vous avez spécifié dans la formule VLOOKUP est 5, ce qui est supérieur au nombre de colonnes dans le tableau de table. Par conséquent, VLOOKUP ne pourra pas localiser la colonne et renverra 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 le tableau de la table.
  • Obtenir automatiquement le numéro de colonne en fonction de l'en-tête de colonne spécifié
    Si la table contient de nombreuses colonnes, vous pouvez rencontrer des difficultés pour déterminer le bon numéro d'index de colonne. Ici, vous pouvez imbriquer la fonction MATCH dans la fonction VLOOKUP pour trouver la position de la colonne en fonction d'un certain en-tête de colonne.
    =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
    Notes: Dans la formule ci-dessus, le EQUIV("Courriel",B5:E5, 0) La fonction est utilisée pour obtenir le numéro de colonne du "Email" dans la plage de dates B6:E12. Ici, le résultat est 4, qui est utilisé comme col_index dans la fonction VLOOKUP.

Valeur incorrecte renvoyée

Si vous trouvez que VLOOKUP ne renvoie pas le bon résultat, cela peut être dû aux raisons suivantes

Raison 1 : la colonne de recherche n'est pas triée par ordre croissant

Si vous avez défini le dernier argument sur VRAI (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, accédez à la Données onglet, cliquez sur Trier du plus petit au plus grand dans l' Trier et filtrer groupe.
  2. Dans le Avertissement de tri boîte de dialogue, sélectionnez le Élargir la sélection option, et cliquez OK.

Raison 2 : Une colonne est insérée ou supprimée

Comme indiqué dans la capture d'écran ci-dessous, la valeur que je voulais à l'origine renvoyer se trouve dans la quatrième colonne du tableau de la table. Je spécifie donc le numéro col_index sur 4. Lorsqu'une nouvelle colonne est insérée, la colonne de résultat devient la cinquième colonne de la table. tableau, ce qui fait que VLOOKUP renvoie le résultat d'une mauvaise colonne.

Solutions

Voici deux solutions pour vous.

  • Vous pouvez modifier manuellement le numéro d'index de la colonne pour qu'il corresponde à 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 renvoyer le résultat d'une certaine colonne, telle que la colonne Email dans cet exemple. La formule suivante peut aider à faire correspondre automatiquement l'index de colonne en fonction de l'en-tête de colonne donné, que des colonnes soient insérées ou supprimées du tableau de table.
    =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 se trouve dans la colonne la plus à gauche et la valeur du résultat doit se trouver dans n'importe quelle colonne à droite de la colonne de recherche.
  • Si vous laissez le dernier argument vide, RECHERCHEV utilise une correspondance approximative par défaut.
  • VLOOKUP effectue une recherche insensible à la casse.
  • Pour plusieurs correspondances, VLOOKUP renvoie uniquement la première correspondance qu'il trouve dans le tableau de table, en fonction de l'ordre des lignes dans le tableau de table.

Meilleurs outils de productivité bureautique

Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples    VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule    Gestionnaire de classeurs et de feuilles   |  Centre de ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules    Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

onglet kte 201905


Office Tab apporte une interface à onglets à Office et facilite grandement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!