Plus de 20 exemples VLOOKUP pour les utilisateurs débutants et avancés d'Excel

La fonction VLOOKUP est l'une des fonctions les plus populaires d'Excel. Ce didacticiel présentera comment utiliser la fonction VLOOKUP dans Excel avec des dizaines d'exemples de base et avancés étape par étape.

Introduction de la fonction RECHERCHEV - Syntaxe et arguments

Dans Excel, la fonction VLOOKUP est une fonction puissante pour la plupart des utilisateurs d'Excel, elle vous permet de rechercher une valeur dans la partie la plus à gauche de la plage de données et de renvoyer une valeur correspondante dans la même ligne à partir d'une colonne que vous avez spécifiée comme capture d'écran suivante illustrée .

La syntaxe de la fonction RECHERCHEV:

Arguments:

Notes de fonction:

Exemples de RECHERCHEV de base

Dans cette section, nous parlerons de certaines formules Vlookup que vous avez fréquemment utilisées.

2.1 Correspondance exacte et correspondance approximative VLOOKUP

 2.1.1 Faire une correspondance exacte VLOOKUP

Normalement, si vous recherchez une correspondance exacte avec la fonction VLOOKUP, il vous suffit d'utiliser FALSE comme dernier argument.

Par exemple, pour obtenir les scores mathématiques correspondants en fonction des numéros d'identification spécifiques, procédez comme suit:

Veuillez copier et coller la formule ci-dessous dans une cellule vide (ici, je sélectionne G2), puis appuyez sur la touche « Entrée » pour obtenir le résultat :

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Comment fonctionne la formule RECHERCHEV ?

Tout d'abord, il recherche l'ID - C1005 dans la colonne la plus à gauche du tableau. Il va de haut en bas et trouve la valeur dans la cellule A6.

Dès qu'il trouve la valeur, il se tourne vers la droite vers la troisième colonne et extrait la valeur qu'elle contient.

Ainsi, vous obtiendrez le résultat comme illustré ci-dessous :

 2.1.2 Faire une correspondance approximative VLOOKUP

La correspondance approximative est utile pour rechercher des valeurs entre des plages de données. Si la correspondance exacte n'est pas trouvée, la recherche V approximative renverra la plus grande valeur qui est inférieure à la valeur de recherche.

Par exemple, si vous disposez de la plage de données suivante et que les commandes spécifiées ne figurent pas dans la colonne Commandes, comment obtenir la remise la plus proche dans la colonne B ?

Étape 1 : Appliquez la formule RECHERCHEV et remplissez-la dans d'autres cellules

Copiez et collez la formule suivante dans une cellule où vous souhaitez placer le résultat, puis faites glisser la poignée de recopie vers le bas pour appliquer cette formule à d'autres cellules.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Résultat:

Maintenant, vous obtiendrez les correspondances approximatives basées sur les valeurs données, voir capture d'écran :

2.2 Effectuez une RECHERCHEV sensible à la casse dans Excel

Par défaut, la fonction RECHERCHEV effectue une recherche insensible à la casse, ce qui signifie qu'elle traite les caractères minuscules et majuscules comme identiques. Parfois, vous devrez peut-être effectuer une recherche sensible à la casse dans Excel, la fonction RECHERCHEV normale peut ne pas le résoudre. Dans ce cas, vous pouvez utiliser des fonctions alternatives telles que INDEX et MATCH avec la fonction EXACT, ou les fonctions RECHERCHE et EXACT.

Par exemple, j'ai la plage de données suivante dont la colonne ID contient une chaîne de texte avec des majuscules ou des minuscules, maintenant, je veux renvoyer le score mathématique correspondant du numéro d'identification donné.

Étape 1 : Appliquez n'importe quelle formule et remplissez-la dans d'autres cellules

Veuillez copier et coller l'une des formules ci-dessous dans une cellule vide où vous souhaitez obtenir le résultat. Ensuite, sélectionnez la cellule de formule, faites glisser la poignée de remplissage vers les cellules où vous souhaitez remplir cette formule.

Formule 1 : Après avoir collé la formule, veuillez appuyer sur les touches « Ctrl » + « Maj » + « Entrée ».

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formule 2 : Après avoir collé la formule, veuillez appuyer sur la touche « Entrée ».

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Résultat:

Ensuite, vous obtiendrez les résultats corrects dont vous avez besoin. Voir capture d'écran :

2.3 Valeurs VLOOKUP de droite à gauche dans Excel

La fonction VLOOKUP recherche toujours une valeur dans la colonne la plus à gauche d'une plage de données et renvoie la valeur correspondante d'une colonne à droite. Si vous souhaitez effectuer une RECHERCHEV inversée, ce qui signifie rechercher une valeur spécifique dans la colonne de droite et renvoyer sa valeur correspondante dans la colonne de gauche, comme illustré ci-dessous :

Cliquez pour connaître les détails étape par étape de cette tâche…

2.4 VLOOKUP la deuxième, nième ou dernière valeur correspondante dans Excel

Normalement, si plusieurs valeurs correspondantes sont trouvées lors de l'utilisation de la fonction Vlookup, seul le premier enregistrement correspondant sera renvoyé. Dans cette section, je vais parler de la façon d'obtenir la deuxième, nième ou dernière valeur correspondante dans une plage de données.

 2.4.1 RECHERCHEV et renvoyer la 2e ou la nième valeur correspondante

Supposons que vous ayez une liste de noms dans la colonne A, la formation qu'ils ont achetée dans la colonne B. Maintenant, vous cherchez à trouver la 2e ou la nième formation achetée par le client donné. Voir capture d'écran :

Ici, la fonction VLOOKUP peut ne pas résoudre cette tâche directement. Mais, vous pouvez utiliser la fonction INDEX comme alternative.

Étape 1 : Appliquer et remplir la formule dans d'autres cellules

Par exemple, pour obtenir la deuxième valeur correspondante selon les critères donnés, appliquez la formule suivante dans une cellule vide et appuyez simultanément sur les touches « Ctrl » + « Maj » + « Entrée » pour obtenir le premier résultat. Sélectionnez ensuite la cellule de formule et faites glisser la poignée de recopie vers le bas jusqu'aux cellules où vous souhaitez remplir la formule.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Résultat:

Désormais, toutes les secondes valeurs correspondantes basées sur les noms donnés ont été affichées en même temps.

 2.4.2 VLOOKUP et renvoie la dernière valeur correspondante

Si vous voulez vlookup et renvoyer la dernière valeur correspondante comme illustré ci-dessous, ceci VLOOKUP et renvoie la dernière valeur correspondante tutoriel peut vous aider à obtenir la dernière valeur correspondante dans les détails.

2.5 VLOOKUP faisant correspondre des valeurs entre deux valeurs ou dates données

Parfois, vous souhaiterez peut-être rechercher des valeurs entre deux valeurs ou dates et renvoyer les résultats correspondants, comme indiqué dans la capture d'écran ci-dessous. Dans ce cas, vous pouvez utiliser la fonction RECHERCHE au lieu de la fonction RECHERCHEV avec une table triée.

 2.5.1 VLOOKUP correspondant à des valeurs entre deux valeurs ou dates données avec une formule

Étape 1 : Organisez les données et appliquez la formule suivante

Votre table d'origine doit être une plage de données triées. Et puis, copiez ou entrez la formule suivante dans une cellule vide. Ensuite, faites glisser la poignée de remplissage pour remplir cette formule dans les autres cellules dont vous avez besoin.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Résultat:

Et maintenant, vous obtiendrez tous les enregistrements correspondants en fonction de la valeur donnée, voir capture d'écran :

 2.5.2 RECHERCHEV faisant correspondre les valeurs entre deux valeurs ou dates données avec une fonctionnalité pratique

Si vous avez du mal à vous souvenir et à comprendre la formule ci-dessus, je vais vous présenter ici un outil simple - "Kutools pour Excel", avec sa fonction "RECHERCHE entre deux valeurs", vous pouvez renvoyer l'élément correspondant en fonction de la valeur ou de la date spécifique entre deux valeurs ou dates en toute simplicité.

1. Cliquez sur « Kutools » > « Super RECHERCHE » > « RECHERCHE entre deux valeurs » pour activer cette fonctionnalité.
2. Spécifiez ensuite les opérations à partir de la boîte de dialogue en fonction de vos données.

2.6 Utilisation de caractères génériques pour les correspondances partielles dans la fonction VLOOKUP

Dans Excel, les caractères génériques peuvent être utilisés dans la fonction VLOOKUP, qui vous permet d'effectuer une correspondance partielle sur une valeur de recherche. Par exemple, vous pouvez utiliser VLOOKUP pour renvoyer une valeur correspondante à partir d'une table basée sur une partie d'une valeur de recherche.

Supposons que j'ai une plage de données comme ci-dessous la capture d'écran, maintenant, je veux extraire le score en fonction du prénom (et non du nom complet). Comment pourrait résoudre cette tâche dans Excel?

Étape 1 : Appliquer la formule et remplir les autres cellules

Veuillez copier ou saisir la formule suivante dans une cellule vide, puis faites glisser la poignée de remplissage pour remplir cette formule dans les autres cellules dont vous avez besoin :

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Résultat:

Et tous les scores correspondants ont été renvoyés comme illustré ci-dessous :

2.7 Valeurs VLOOKUP d'une autre feuille de calcul

Habituellement, vous devrez peut-être travailler avec plus d'une feuille de calcul, la fonction VLOOKUP peut être utilisée pour rechercher des données d'une autre feuille comme sur une feuille de calcul.

Par exemple, vous avez deux feuilles de calcul, comme illustré ci-dessous, pour rechercher et renvoyer les données correspondantes de la feuille de calcul que vous avez spécifiée, procédez comme suit:

Étape 1 : Appliquer la formule et remplir les autres cellules

Veuillez entrer ou copier la formule ci-dessous dans une cellule vide où vous souhaitez obtenir les éléments correspondants. Ensuite, faites glisser la poignée de recopie vers les cellules auxquelles vous souhaitez appliquer cette formule.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Résultat:

Vous obtiendrez les résultats correspondants selon vos besoins, voir capture d'écran :

2.8 Valeurs VLOOKUP d'un autre classeur

Cette section parlera de la recherche et renverra les valeurs correspondantes d'un classeur différent à l'aide de la fonction VLOOKUP.

Par exemple, supposons que vous ayez deux classeurs. Le premier classeur contient une liste de produits et leurs coûts respectifs. Dans le deuxième classeur, vous souhaitez extraire le coût correspondant pour chaque article de produit comme illustré ci-dessous.

Étape 1 : Appliquer la formule

Ouvrez les deux classeurs que vous souhaitez utiliser, puis appliquez la formule suivante dans une cellule où vous souhaitez placer le résultat dans le deuxième classeur. Ensuite, faites glisser et copiez cette formule dans les autres cellules dont vous avez besoin.

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Résultat:

2.9 Renvoyer un texte vide ou spécifique au lieu d'une erreur 0 ou #N/A

Habituellement, lorsque vous utilisez la fonction VLOOKUP pour renvoyer une valeur correspondante, si la cellule correspondante est vide, elle renverra 0. Et si la valeur correspondante n'est pas trouvée, vous obtiendrez une valeur d'erreur de #N/A comme indiqué dans le capture d'écran ci-dessous. Si vous souhaitez afficher une cellule vide ou une valeur spécifique au lieu de 0 ou #N/A, ceci VLOOKUP pour renvoyer une valeur vide ou spécifique au lieu de 0 ou N/A tutoriel peut vous rendre service.

Exemples de RECHERCHEV avancée

3.1 Recherche bidirectionnelle (RECHERCHEV en ligne et en colonne)

Parfois, vous devrez peut-être effectuer une recherche en 2 dimensions, ce qui signifie rechercher une valeur à la fois dans la ligne et dans la colonne en même temps. Par exemple, si vous disposez de la plage de données suivante et que vous devrez peut-être obtenir la valeur d'un produit particulier au cours d'un trimestre spécifié. Cette section présentera une formule pour traiter ce travail dans Excel.

Dans Excel, vous pouvez utiliser une combinaison de fonctions VLOOKUP et MATCH pour effectuer une recherche bidirectionnelle.

Veuillez appliquer la formule suivante dans une cellule vide, puis appuyez sur la touche « Entrée » pour obtenir le résultat.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 Valeur de correspondance VLOOKUP basée sur deux critères ou plus

Il vous est facile de rechercher la valeur correspondante en fonction d'un critère, mais si vous avez deux critères ou plus, que pouvez-vous faire ?

 3.2.1 Valeur de correspondance VLOOKUP basée sur deux critères ou plus avec des formules

Dans ce cas, les fonctions RECHERCHE ou MATCH et INDEX d'Excel peuvent vous aider à résoudre ce travail rapidement et facilement.

Par exemple, j'ai le tableau de données ci-dessous, pour renvoyer le prix correspondant en fonction du produit et de la taille spécifiques, les formules suivantes peuvent vous aider.

Étape 1 : Appliquez l’une des formules ci-dessous

Formule 1 : Saisissez la formule suivante et appuyez sur « Entrée ».

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formule 2 : Saisissez la formule suivante et appuyez sur « Ctrl » + « Maj » + « Entrée ».

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Résultat:

 3.2.2 Valeur de correspondance RECHERCHEV basée sur deux critères ou plus avec Kutools pour Excel

Il peut être difficile de mémoriser les formules complexes mentionnées ci-dessus, qui doivent être appliquées à plusieurs reprises, ce qui peut nuire à votre efficacité au travail. Cependant, « Kutools for Excel » propose une fonction de « Recherche multi-conditions » qui vous permet d'obtenir le résultat correspondant en fonction d'une ou plusieurs conditions en quelques clics seulement.

1. Cliquez sur « Kutools » > « Super LOOKUP » > « Recherche multi-conditions » pour activer cette fonctionnalité.
2. Spécifiez ensuite les opérations à partir de la boîte de dialogue en fonction de vos données.

3.3 VLOOKUP pour renvoyer plusieurs valeurs avec un ou plusieurs critères

Dans Excel, la fonction VLOOKUP recherche une valeur et ne renvoie la première valeur correspondante que si plusieurs valeurs correspondantes sont trouvées. Parfois, vous voudrez peut-être renvoyer toutes les valeurs correspondantes dans une ligne, dans une colonne ou dans une seule cellule. Cette section explique comment renvoyer les valeurs correspondantes multiples avec une ou plusieurs conditions dans un classeur.

 3.3.1 VLOOKUP toutes les valeurs correspondantes basées sur une ou plusieurs conditions horizontalement

En supposant que vous disposiez d'un tableau de données contenant le pays, la ville et les noms dans la plage A1: C14, et maintenant, vous souhaitez renvoyer horizontalement tous les noms provenant de "US", comme illustré ci-dessous. Pour résoudre cette tâche, veuillez cliquez ici pour obtenir le résultat étape par étape.

 3.3.2 VLOOKUP toutes les valeurs correspondantes basées sur une ou plusieurs conditions verticalement

Si vous avez besoin de Vlookup et de renvoyer verticalement toutes les valeurs correspondantes en fonction de critères spécifiques, comme illustré ci-dessous, s'il vous plaît cliquez ici pour obtenir la solution dans les détails.

 3.3.3 VLOOKUP toutes les valeurs correspondantes basées sur une ou plusieurs conditions dans une seule cellule

Si vous souhaitez effectuer une recherche V et renvoyer plusieurs valeurs correspondantes dans une seule cellule avec un séparateur spécifié, la nouvelle fonction de TEXTJOIN peut vous aider à résoudre ce travail rapidement et facilement.

3.4 RECHERCHEV pour renvoyer la ligne entière d'une cellule correspondante

Dans cette section, je vais parler de la façon de récupérer la ligne entière d'une valeur correspondante en utilisant la fonction VLOOKUP.

Étape 1 : Appliquez la formule suivante

Copiez ou saisissez la formule ci-dessous dans une cellule vide où vous souhaitez afficher le résultat, puis appuyez sur la touche « Entrée » pour obtenir la première valeur. Faites ensuite glisser la cellule de formule vers la droite jusqu'à ce que les données de la ligne entière soient affichées.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Résultat:

Maintenant, vous pouvez voir que toutes les données de la ligne sont renvoyées. Voir capture d'écran :

3.5 RECHERCHEV imbriquée dans Excel

Parfois, vous devrez peut-être rechercher des valeurs qui sont liées entre elles dans plusieurs tables. Dans ce cas, vous pouvez imbriquer plusieurs fonctions VLOOKUP ensemble pour obtenir la valeur finale.

Par exemple, j'ai une feuille de calcul qui contient deux tables distinctes. Le premier tableau répertorie tous les noms de produits avec leur vendeur correspondant. Le deuxième tableau répertorie les ventes totales de chaque vendeur. Maintenant, si vous souhaitez trouver les ventes de chaque produit, comme indiqué dans la capture d'écran suivante, vous pouvez imbriquer la fonction VLOOKUP pour accomplir cette tâche.

La formule générique pour la fonction RECHERCHEV imbriquée est :

Étape 1 : Appliquer et remplir la formule suivante

Veuillez appliquer la formule suivante dans une cellule vide, puis faites glisser la poignée de remplissage vers les cellules auxquelles vous souhaitez appliquer cette formule.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Résultat:

Maintenant, vous obtiendrez le résultat comme indiqué dans la capture d'écran suivante :

3.6 Vérifier si la valeur existe sur la base d'une liste de données dans une autre colonne

La fonction VLOOKUP peut également vous aider à vérifier si des valeurs existent en fonction de la liste de données dans une autre colonne. Par exemple, si vous souhaitez rechercher les noms dans la colonne C et simplement renvoyer Oui ou Non si le nom est trouvé ou non dans la colonne A, comme illustré ci-dessous.

Étape 1 : Appliquez la formule suivante

Veuillez appliquer la formule suivante dans une cellule vide, puis faites glisser la poignée de remplissage vers les cellules que vous souhaitez remplir cette formule.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Résultat:

Et vous obtiendrez le résultat dont vous avez besoin, voir capture d'écran :

3.7 VLOOKUP et somme de toutes les valeurs correspondantes dans les lignes ou les colonnes

Lorsque vous travaillez avec des données numériques, vous devrez peut-être extraire les valeurs correspondantes d'un tableau et additionner les nombres dans plusieurs colonnes ou lignes. Cette section présentera quelques formules qui peuvent vous aider à accomplir cette tâche.

 3.7.1 VLOOKUP et somme de toutes les valeurs correspondantes dans une ligne ou plusieurs lignes

Supposons que vous ayez une liste de produits avec des ventes sur plusieurs mois, comme illustré dans la capture d'écran suivante. Maintenant, vous devez additionner toutes les commandes de tous les mois en fonction des produits donnés.

Étape 1 : Appliquez la formule suivante

Veuillez copier ou saisir la formule suivante dans une cellule vide, puis appuyez simultanément sur les touches « Ctrl » + « Maj » + « Entrée » pour obtenir le premier résultat. Ensuite, faites glisser la poignée de recopie vers le bas pour copier cette formule dans les cellules souhaitées.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Résultat:

Toutes les valeurs d'une ligne de la première valeur correspondante ont été additionnées, voir capture d'écran :

 3.7.2 RECHERCHEV et somme de toutes les valeurs correspondantes dans une colonne ou plusieurs colonnes

Si vous souhaitez additionner la valeur totale pour les mois spécifiques, comme indiqué dans la capture d'écran ci-dessous. La fonction RECHERCHEV normale peut ne pas vous aider, ici, vous devez appliquer les fonctions SUM, INDEX et MATCH ensemble pour créer une formule.

Étape 1 : Appliquez la formule suivante

Appliquez la formule ci-dessous dans une cellule vide, puis faites glisser la poignée de remplissage vers le bas pour copier cette formule dans d'autres cellules.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Résultat:

Maintenant, les premières valeurs correspondantes basées sur le mois spécifique dans une colonne ont été additionnées, voir capture d'écran :

 3.7.3 RECHERCHEV et addition des premières valeurs correspondantes ou de toutes les valeurs correspondantes avec Kutools pour Excel

Peut-être que les formules ci-dessus sont difficiles à retenir pour vous, dans ce cas, je recommanderai une fonctionnalité puissante - "Lookup and Sum" de "Kutools for Excel", avec cette fonctionnalité, vous pouvez Vlookup et additionner la première correspondance ou toutes les valeurs correspondantes dans les lignes ou les colonnes aussi facilement que possible.

1. Cliquez sur « Kutools » > « Super RECHERCHE » > « RECHERCHE et somme » pour activer cette fonctionnalité.
2. Spécifiez ensuite les opérations à partir de la boîte de dialogue en fonction de vos besoins.

 3.7.4 RECHERCHEV et somme de toutes les valeurs correspondantes dans les lignes et les colonnes

Si vous souhaitez additionner les valeurs lorsque vous devez faire correspondre à la fois la colonne et la ligne, par exemple, pour obtenir la valeur totale du produit Pull au mois de mars, comme illustré ci-dessous.

Ici, vous pouvez utiliser la fonction SUMPRODCT pour accomplir cette tâche.

Veuillez appliquer la formule suivante dans une cellule, puis appuyez sur la touche « Entrée » pour obtenir le résultat, voir capture d'écran :

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VLOOKUP pour fusionner deux tables basées sur des colonnes clés

Dans votre travail quotidien, lors de l'analyse des données, vous devrez peut-être rassembler toutes les informations nécessaires dans un seul tableau basé sur une ou plusieurs colonnes clés. Pour accomplir cette tâche, vous pouvez utiliser les fonctions INDEX et MATCH au lieu de la fonction VLOOKUP.

 3.8.1 VLOOKUP pour fusionner deux tables basées sur une colonne clé

Par exemple, vous avez deux tables, la première table contenant les données sur les produits et les noms, et la deuxième table contient les données sur les produits et les commandes, maintenant, vous souhaitez combiner ces deux tables en faisant correspondre la colonne de produit commune dans une table.

Étape 1 : Appliquez la formule suivante

Veuillez appliquer la formule suivante dans une cellule vide. Ensuite, faites glisser la poignée de remplissage vers les cellules auxquelles vous souhaitez appliquer cette formule

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Résultat:

Maintenant, vous obtiendrez une table fusionnée avec la colonne de commande jointe à la première table en fonction des données de la colonne clé.

 3.8.2 VLOOKUP pour fusionner deux tables basées sur plusieurs colonnes clés

Si les deux tables que vous souhaitez joindre ont plusieurs colonnes clés, pour fusionner les tables en fonction de ces colonnes communes, veuillez suivre les étapes ci-dessous.

La formule générique est:

Étape 1 : Appliquez la formule suivante

Veuillez appliquer la formule ci-dessous dans une cellule vide où vous souhaitez placer le résultat, puis appuyez simultanément sur les touches « Ctrl » + « Maj » + « Entrée » pour obtenir la première valeur correspondante, voir capture d'écran :

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Étape 2 : Remplir la formule dans d'autres cellules

Ensuite, sélectionnez la première cellule de formule et faites glisser la poignée de recopie pour copier cette formule dans d'autres cellules selon vos besoins :

3.9 VLOOKUP correspondant à des valeurs sur plusieurs feuilles de calcul

Avez-vous déjà eu besoin d'effectuer une RECHERCHEV sur plusieurs feuilles de calcul dans Excel ? Par exemple, si vous avez trois feuilles de calcul avec des plages de données et que vous souhaitez récupérer des valeurs spécifiques en fonction des critères de ces feuilles, vous pouvez suivre le didacticiel pas à pas Valeurs RECHERCHEV sur plusieurs feuilles de calcul pour accomplir cette tâche.

Les valeurs correspondantes VLOOKUP conservent le formatage des cellules

Lors de la recherche de valeurs correspondantes, la mise en forme de cellule d'origine telle que la couleur de la police, la couleur d'arrière-plan, le format des données, etc. ne sera pas conservée. Pour conserver la mise en forme des cellules ou des données, cette section présentera quelques astuces pour résoudre les tâches.

4.1 VLOOKUP valeur correspondante et conserver la couleur de la cellule, le formatage de la police

Comme nous le savons tous, la fonction RECHERCHEV normale ne peut récupérer la valeur correspondante que dans une autre plage de données. Cependant, il peut arriver que vous souhaitiez obtenir la valeur correspondante avec la mise en forme de la cellule, telle que la couleur de remplissage, la couleur de police et le style de police. Dans cette section, nous expliquerons comment récupérer les valeurs correspondantes tout en préservant la mise en forme source dans Excel.

Veuillez suivre les étapes suivantes pour rechercher et renvoyer sa valeur correspondante avec le formatage de la cellule:

Étape 1 : Copiez le code 1 dans le module Sheet Code

1. Dans la feuille de calcul contenant les données à rechercher, faites un clic droit sur l'onglet de la feuille et sélectionnez « Afficher le code » dans le menu contextuel. Voir capture d'écran :
   
2. Dans la fenêtre « Microsoft Visual Basic pour Applications » ouverte, veuillez copier le code VBA ci-dessous dans la fenêtre Code.
3. Code VBA 1: VLOOKUP pour obtenir le formatage de la cellule avec la valeur de recherche

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Étape 2 : Copiez le code 2 dans la fenêtre Module

1. Toujours dans la fenêtre « Microsoft Visual Basic pour Applications », cliquez sur « Insertion » > « Module », puis copiez le code VBA 2 ci-dessous dans la fenêtre « Module ».
2. Code VBA 2: VLOOKUP pour obtenir le formatage de la cellule avec la valeur de recherche

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Étape 3 : Sélectionnez l'option pour VBAproject

1. Après avoir inséré les codes ci-dessus, cliquez sur « Outils » > « Références » dans la fenêtre « Microsoft Visual Basic pour Applications ». Cochez ensuite la case « Microsoft Scripting Runtime » dans la boîte de dialogue « Références – VBAProject ». Voir les captures d'écran :
2. Cliquez ensuite sur « OK » pour fermer la boîte de dialogue, puis enregistrez et fermez la fenêtre de code.

Étape 4 : Tapez la formule pour obtenir le résultat

1. Maintenant, revenez à la feuille de calcul, appliquez la formule suivante. Et puis, faites glisser la poignée de remplissage vers le bas pour obtenir tous les résultats avec leur mise en forme. Voir capture d'écran :
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 Conserver le format de date d'une valeur retournée VLOOKUP

Lorsque vous utilisez la fonction RECHERCHEV pour rechercher et renvoyer une valeur au format de date, le résultat renvoyé peut s'afficher sous la forme d'un nombre. Pour conserver le format de date dans le résultat renvoyé, vous devez inclure la fonction VLOOKUP dans la fonction TEXT.

Étape 1 : Appliquez la formule suivante

Veuillez appliquer la formule ci-dessous dans une cellule vide. Ensuite, faites glisser la poignée de recopie pour copier cette formule dans d'autres cellules.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Résultat:

Toutes les dates correspondantes ont été renvoyées comme illustré ci-dessous :

4.3 Renvoyer le commentaire de cellule de VLOOKUP

Avez-vous déjà eu besoin de récupérer à la fois les données de cellule correspondantes et son commentaire associé à l'aide de VLOOKUP dans Excel, comme indiqué dans la capture d'écran suivante ? Si tel est le cas, la fonction définie par l'utilisateur fournie ci-dessous peut vous aider à accomplir cette tâche.

Étape 1 : Copiez le code dans un module

1. Maintenez les touches « ALT » + « F11 » enfoncées pour ouvrir la fenêtre « Microsoft Visual Basic pour Applications ».
2. Cliquez sur « Insertion » > « Module », puis copiez et collez le code suivant dans la fenêtre « Module ».
   Code VBA: Vlookup et retourne la valeur correspondante avec le commentaire de la cellule:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Ensuite, enregistrez et fermez la fenêtre de code.

Étape 2 : Tapez la formule pour obtenir le résultat

1. Maintenant, entrez la formule suivante et faites glisser la poignée de remplissage pour copier cette formule dans d'autres cellules. Il renverra simultanément les valeurs correspondantes et les commentaires, voir capture d'écran :
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 Numéros VLOOKUP stockés sous forme de texte

Par exemple, j'ai une plage de données où le numéro d'identification dans la table d'origine est au format numérique et le numéro d'identification dans les cellules de recherche est stocké sous forme de texte, vous pouvez rencontrer une erreur #N/A lors de l'utilisation de la fonction VLOOKUP normale. Dans ce cas, pour récupérer les informations correctes, vous pouvez encapsuler les fonctions TEXTE et VALEUR dans la fonction RECHERCHEV. Voici la formule pour y parvenir :

Étape 1 : Appliquer et remplir la formule suivante

Veuillez appliquer la formule suivante dans une cellule vide, puis faites glisser la poignée de remplissage vers le bas pour copier cette formule.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Résultat:

Maintenant, vous obtiendrez les résultats corrects comme illustré ci-dessous :