Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
S’enregistrer  \/ 
x

or

Fonction RECHERCHEV avec quelques exemples basiques et avancés dans Excel

Dans Excel, la fonction RECHERCHEV est une fonction puissante pour la plupart des utilisateurs d'Excel, qui est utilisée pour rechercher une valeur dans la partie la plus à gauche de la plage de données et renvoyer une valeur correspondante dans la même ligne à partir d'une colonne que vous avez spécifiée comme ci-dessous capture d'écran . Ce didacticiel explique comment utiliser la fonction RECHERCHEV avec quelques exemples de base et avancés dans Excel.

Table des matières:

1. Introduction de la fonction RECHERCHEV - Syntaxe et arguments

2. Exemples de RECHERCHEV de base

3. Exemples de RECHERCHEV avancée

4. Les valeurs correspondantes VLOOKUP conservent le formatage des cellules

5. Téléchargez les fichiers d'exemple RECHERCHEV


Introduction de la fonction RECHERCHEV - Syntaxe et arguments

La syntaxe de la fonction RECHERCHEV:

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

Arguments:

Lookup_value: La valeur que vous souhaitez rechercher. Il doit être dans la première colonne de la plage table_array.

Tableau_table: La plage de données ou la table dans laquelle se trouvent la colonne de valeur de recherche et la colonne de valeur de résultat.

Col_index_num: Le nombre de colonnes à partir de laquelle la valeur correspondante sera renvoyée. Il commence par 1 dans la colonne la plus à gauche du tableau.

Range_lookup: Valeur logique qui détermine si cette fonction RECHERCHEV renvoie une correspondance exacte ou une correspondance approximative.

  • Correspondance approximative - 1 / VRAI: Si aucune correspondance exacte n'est trouvée, la formule recherche la correspondance la plus proche - la plus grande valeur qui est inférieure à la valeur de recherche. Dans ce cas, vous devez trier la colonne de recherche par ordre croissant.
    = VLOOKUP (valeur_recherche, tableau_table, index_col, TRUE)
    = VLOOKUP (valeur_recherche, tableau_table, index_col, 1)
  • Correspondance exacte - 0 / FAUX: Ceci est utilisé pour rechercher une valeur exactement égale à la valeur de recherche. Si aucune correspondance exacte n'est trouvée, la valeur d'erreur # N / A sera renvoyée.
    = VLOOKUP (valeur_recherche, tableau_table, index_col, FALSE)
    = VLOOKUP (valeur_recherche, tableau_table, index_col, 0)

À noter :

  • 1. La fonction Vlookup recherche uniquement la valeur de gauche à droite.
  • 2. S'il existe plusieurs valeurs correspondantes basées sur la valeur de recherche, seule la première correspondance sera renvoyée à l'aide de la fonction Vlookup.
  • 3. Il renverra la valeur d'erreur # N / A si la valeur de recherche ne peut pas être trouvée.

Exemples de RECHERCHEV de base

1. Faire une correspondance exacte Vlookup et une correspondance approximative Vlookup

Faites une correspondance exacte Vlookup dans Excel

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

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

1. Appliquez la formule ci-dessous dans une cellule vide où vous souhaitez obtenir le résultat:

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

2. Et puis, faites glisser la poignée de remplissage vers les cellules que vous souhaitez remplir cette formule, et vous obtiendrez les résultats dont vous avez besoin. Voir la capture d'écran:

À noter :

  • 1. Dans la formule ci-dessus, F2 est la valeur que vous souhaitez renvoyer sa valeur correspondante, A2: D7 est le tableau de la table, le nombre 3 est le numéro de colonne à partir duquel votre valeur correspondante est renvoyée et le FAUX fait référence à la correspondance exacte.
  • 2. Si la valeur de votre critère n'est pas trouvée dans la plage de données, une valeur d'erreur # N / A s'affichera.

Faites une correspondance approximative Vlookup dans Excel

La correspondance approximative est utile pour rechercher des valeurs entre des plages de données. Si la correspondance exacte n'est pas trouvée, le Vlookup approximatif retournera la plus grande valeur qui est plus petite que la valeur de recherche.

Par exemple, si vous disposez des données de plage suivantes, les commandes spécifiées ne sont pas dans la colonne Commandes, comment obtenir sa remise la plus proche dans la colonne B?

1. Entrez la formule suivante dans une cellule où vous souhaitez placer le résultat:

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

2. Ensuite, faites glisser la poignée de remplissage vers les cellules pour appliquer cette formule, et vous obtiendrez les correspondances approximatives basées sur les valeurs données, voir capture d'écran:

À noter :

  • 1. Dans la formule ci-dessus, D2 est la valeur que vous souhaitez renvoyer ses informations relatives, A2: B9 est la plage de données, le nombre 2 indique le numéro de colonne que votre valeur correspondante est renvoyée et le VRAI fait référence à la correspondance approximative.
  • 2. La correspondance approximative renverra la plus grande valeur qui est inférieure à votre valeur de recherche spécifique.
  • 3. Pour utiliser la fonction Vlookup pour obtenir une valeur de correspondance approximative, vous devez trier la colonne la plus à gauche de la plage de données dans l'ordre croissant, sinon elle renverra un résultat erroné.

2. Faites un Vlookup sensible à la casse dans Excel

Par défaut, la fonction Vlookup 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 faire une recherche sensible à la casse dans Excel, les fonctions Index, Match et Exact ou les fonctions Lookup et Exact peuvent vous rendre service.

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é.

Formule 1: Utilisation des fonctions EXACT, INDEX, MATCH

1. Veuillez entrer ou copier la formule matricielle ci-dessous dans une cellule vide où vous souhaitez obtenir le résultat:

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

2. Puis appuyez Ctrl + Maj + Entrée touches simultanément pour obtenir le premier résultat, puis sélectionnez la cellule de formule, faites glisser la poignée de recopie vers les cellules que vous souhaitez remplir cette formule, puis vous obtiendrez les résultats corrects dont vous avez besoin. Voir la capture d'écran:

À noter :

  • 1. Dans la formule ci-dessus, A2: A10 est la colonne qui contient les valeurs spécifiques dans lesquelles vous souhaitez rechercher, F2 est la valeur de recherche, C2: C10 est la colonne à partir de laquelle le résultat sera renvoyé.
  • 2. Si plusieurs correspondances sont trouvées, cette formule renverra toujours la première correspondance.

Formule 2: Utilisation des fonctions Recherche et Exact

1. Veuillez appliquer la formule ci-dessous dans une cellule vide où vous souhaitez obtenir le résultat:

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

2. Ensuite, faites glisser la poignée de remplissage vers les cellules dans lesquelles vous souhaitez copier cette formule, et vous obtiendrez les valeurs correspondantes en respectant la casse, comme illustré ci-dessous:

À noter :

  • 1. Dans la formule ci-dessus, A2: A10 est la colonne qui contient les valeurs spécifiques dans lesquelles vous souhaitez rechercher, F2 est la valeur de recherche, C2: C10 est la colonne à partir de laquelle le résultat sera renvoyé.
  • 2. Si plusieurs correspondances sont trouvées, cette formule renverra toujours la dernière correspondance.

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 à partir d'une colonne vers la droite. Si vous voulez faire un Vlookup inversé, ce qui signifie rechercher une valeur spécifique à 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…


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 expliquer comment obtenir la deuxième, la nième ou la dernière valeur correspondante avec la fonction Vlookup.

Vlookup et renvoyer la deuxième ou la nième valeur correspondante

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

1. Pour obtenir la deuxième ou la nième valeur correspondante en fonction des critères donnés, veuillez appliquer la formule matricielle suivante dans une cellule vide:

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

2. Puis appuyez Ctrl + Maj + Entrée clés ensemble pour obtenir le premier résultat, puis sélectionnez la cellule de formule, faites glisser la poignée de recopie vers les cellules que vous souhaitez remplir cette formule, et toutes les secondes valeurs correspondantes basées sur les noms donnés ont été affichées en même temps, voir capture d'écran:

Remarque:

  • Dans cette formule, A2: A14 est la plage avec toutes les valeurs de recherche, B2: B14 est la plage des valeurs correspondantes que vous souhaitez renvoyer, E2 est la valeur de recherche et le dernier nombre 2 indique la deuxième valeur correspondante que vous souhaitez obtenir, si vous souhaitez renvoyer la troisième valeur correspondante, il vous suffit de la changer en 3 selon vos besoins.

Vlookup et retourne la dernière valeur correspondante

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


5. Vlookup correspondant aux 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 illustré ci-dessous, dans ce cas, vous pouvez utiliser la fonction RECHERCHE et une table triée.

Vlookup correspondant aux valeurs entre deux valeurs ou dates données avec une formule

1. Tout d'abord, 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:

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

2. Ensuite, faites glisser la poignée de remplissage pour remplir cette formule vers les autres cellules dont vous avez besoin, et maintenant, vous obtiendrez tous les enregistrements correspondants en fonction de la valeur donnée, voir capture d'écran:

À noter :

  • 1. Dans la formule ci-dessus, A2: A6 est la plage de valeurs plus petites et B2: B6 est la plage de nombres plus grands dans votre plage de données, le E2 est la valeur donnée à laquelle vous souhaitez obtenir sa valeur correspondante, C2: C6 correspond aux données de la colonne dont vous souhaitez extraire.
  • 2. Cette formule peut également être utilisée pour extraire les valeurs correspondantes entre deux dates, comme illustré ci-dessous:

Vlookup faisant correspondre les valeurs entre deux valeurs ou dates données avec une fonctionnalité utile

Si vous êtes douloureux avec la formule ci-dessus, ici, je vais vous présenter un outil simple - Kutools pour Excel, Avec son RECHERCHE entre deux valeurs fonctionnalité, vous pouvez renvoyer l'élément correspondant en fonction de la valeur ou de la date spécifique entre deux valeurs ou dates sans mémoriser aucune formule.   Cliquez pour télécharger Kutools for Excel maintenant!


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, ce qui permet d'effectuer une correspondance partielle sur une valeur de recherche. Par exemple, vous pouvez utiliser Vlookup pour renvoyer la 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?

1. La fonction Vlookup normale ne fonctionne pas correctement, vous devez joindre le texte ou la référence de cellule avec un caractère générique, veuillez copier ou entrer la formule suivante dans une cellule vide:

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

2. Ensuite, faites glisser la poignée de remplissage pour remplir cette formule vers les autres cellules dont vous avez besoin, et tous les scores correspondants ont été renvoyés comme illustré ci-dessous:

À noter :

  • 1. Dans la formule ci-dessus, E2 & "*" est la valeur de recherche, la valeur dans E2 et les * caractère générique («*» indique un ou plusieurs caractères), A2: C11 est la plage de recherche, le nombre 3 la colonne qui contient la valeur à renvoyer.
  • 2. Vlookup lorsque vous utilisez des caractères génériques, vous devez définir le mode de correspondance exacte avec FALSE ou 0 pour le dernier argument de la fonction Vlookup.

Conseils:

1. Recherchez et renvoyez les valeurs correspondantes se terminant par une valeur spécifique, veuillez appliquer cette formule: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Pour rechercher et renvoyer la valeur correspondante basée sur une partie de la chaîne de texte, que le texte spécifié soit devant, derrière ou au milieu de la chaîne de texte, il vous suffit de joindre deux * caractères autour de la référence de cellule ou du texte. Veuillez faire avec cette formule: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


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 à partir d'une autre feuille de la même manière que 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:

1. Veuillez entrer ou copier la formule ci-dessous dans une cellule vide où vous souhaitez obtenir les éléments correspondants:

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

2. Ensuite, faites glisser la poignée de remplissage vers les cellules auxquelles vous souhaitez appliquer cette formule, et vous obtiendrez les résultats correspondants selon vos besoins, voir capture d'écran:

Note : Dans la formule ci-dessus:

  • A2 représente la valeur de recherche;
  • Fiche technique est le nom de la feuille de calcul à partir de laquelle vous souhaitez rechercher des données, (Si le nom de la feuille contient des espaces ou des caractères de ponctuation, vous devez placer des guillemets simples autour du nom de la feuille, sinon, vous pouvez utiliser directement le nom de la feuille comme = RECHERCHEV (A2, Fiche technique! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 est la plage de données de la fiche technique pour laquelle nous recherchons des données;
  • le nombre 3 est le numéro de colonne qui contient les données correspondantes que vous souhaitez renvoyer.

8. Valeurs Vlookup d'un autre classeur

Cette section parlera de la recherche et retournera les valeurs correspondantes d'un autre classeur à l'aide de la fonction Vlookup.

Par exemple, le premier classeur contient les listes de produits et de coûts.Vous souhaitez maintenant extraire le coût correspondant dans le deuxième classeur en fonction de l'article de produit, comme illustré ci-dessous.

1. Pour récupérer le coût relatif d'un autre classeur, ouvrez d'abord les deux classeurs que vous souhaitez utiliser, puis appliquez la formule suivante dans une cellule dans laquelle vous souhaitez placer le résultat:

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

2. Ensuite, faites glisser et copiez cette formule dans d'autres cellules dont vous avez besoin, voir capture d'écran:

À noter :

  • 1. Dans la formule ci-dessus:
    B2 représente la valeur de recherche;
    [Liste de produits.xlsx] Sheet1 est le nom du classeur et de la feuille de calcul à partir desquels vous souhaitez rechercher des données, (la référence au classeur est placée entre crochets et le classeur entier + la feuille est placé entre guillemets simples);
    A2: B6 est la plage de données dans la feuille de calcul d'un autre classeur où nous recherchons des données;
    le nombre 2 est le numéro de colonne qui contient les données correspondantes que vous souhaitez renvoyer.
  • 2. Si le classeur de recherche est fermé, le chemin d'accès complet du fichier du classeur de recherche sera affiché dans la formule comme illustré ci-dessous:

9. Vlookup et renvoie un texte vide ou spécifique au lieu de la valeur d'erreur 0 ou # N / A

Normalement, lorsque vous appliquez la fonction vlookup pour renvoyer la valeur correspondante, si votre cellule correspondante est vide, elle retournera 0, et si votre valeur correspondante n'est pas trouvée, vous obtiendrez une valeur d'erreur # N / A comme illustré ci-dessous. Au lieu d'afficher la valeur 0 ou # N / A avec une cellule vide ou une autre valeur que vous aimez, ceci Vlookup pour renvoyer une valeur vide ou spécifique au lieu de 0 ou N / A le tutoriel peut vous faire une faveur étape par étape.


Exemples de RECHERCHEV avancée

1. Recherche bidirectionnelle avec la fonction Vlookup (Vlookup en ligne et en colonne)

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

Formule 1: Utilisation des fonctions RECHERCHEV et MATCH

Dans Excel, vous pouvez utiliser une combinaison de fonctions RECHERCHEV et MATCH pour effectuer une recherche bidirectionnelle, veuillez appliquer la formule suivante dans une cellule vide, puis appuyez sur Entrer clé pour obtenir le résultat.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Note : Dans la formule ci-dessus:

  • H1: la valeur de recherche dans la colonne sur laquelle vous souhaitez obtenir la valeur correspondante;
  • A2: E6: la plage de données comprenant les en-têtes de ligne;
  • H2: la valeur de recherche dans la ligne sur laquelle vous souhaitez obtenir la valeur correspondante;
  • A1: E1: les cellules des en-têtes de colonne.

Formule 2: Utilisation des fonctions INDEX et MATCH

Voici une autre formule qui peut également vous aider à effectuer une recherche en 2 dimensions, veuillez appliquer la formule ci-dessous, puis appuyez sur Entrer clé pour obtenir le résultat dont vous avez besoin.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Note : Dans la formule ci-dessus:

  • B2: E6: la plage de données à partir de laquelle renvoyer l'élément correspondant;
  • H1: la valeur de recherche dans la colonne sur laquelle vous souhaitez obtenir la valeur correspondante;
  • A2: A6: les en-têtes de ligne contiennent le produit que vous souhaitez rechercher.
  • H2: la valeur de recherche dans la ligne sur laquelle vous souhaitez obtenir la valeur correspondante;
  • B1: E1: les en-têtes de colonne contiennent le trimestre que vous souhaitez rechercher.

2. Valeur de correspondance Vlookup basée sur deux critères ou plus

Il est facile pour vous de rechercher la valeur correspondante en fonction d'un critère, mais si vous avez deux critères ou plus, que pouvez-vous faire? Les fonctions LOOKUP ou MATCH et INDEX d'Excel peuvent vous aider à résoudre ce problème 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.

Formule 1: Utilisation de la fonction LOOKUP

Veuillez appliquer la formule ci-dessous dans une cellule où vous souhaitez obtenir le résultat, puis appuyez sur la touche Entrée, voir capture d'écran:

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

À noter :

  • 1. Dans la formule ci-dessus:
    A2: A12 = G1: signifie rechercher les critères de G1 dans la plage A2: A12;
    B2: B12 = G2: signifie rechercher les critères de G2 dans la plage B2: B12;
    D2: D12: la plage dont vous souhaitez renvoyer la valeur correspondante.
  • 2. Si vous avez plus de deux critères, il vous suffit de joindre les autres critères dans la formule, tels que: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Formule 2: Utilisation des fonctions INDEXT et MATCH

La combinaison de la fonction Index et Match peut également être utilisée pour renvoyer la valeur correspondante en fonction de plusieurs critères. Veuillez copier ou saisir la formule suivante:

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

Ensuite, appuyez simultanément sur les touches Ctrl + Maj + Entrée pour obtenir la valeur relative dont vous avez besoin. Voir la capture d'écran:

À noter :

  • 1. Dans la formule ci-dessus:
    A2: A12 = G1: signifie rechercher les critères de G1 dans la plage A2: A12;
    B2: B12 = G2: signifie rechercher les critères de G2 dans la plage B2: B12;
    D2: D12: la plage dont vous souhaitez renvoyer la valeur correspondante.
  • 2. Si vous avez plus de deux critères, il vous suffit de joindre les nouveaux critères dans la formule, tels que: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup pour renvoyer plusieurs valeurs correspondantes avec une ou plusieurs conditions

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 souhaiterez peut-être renvoyer toutes les valeurs correspondantes dans une ligne, dans une colonne ou dans une seule cellule. Cette section explique comment renvoyer les multiples valeurs correspondantes avec une ou plusieurs conditions dans un classeur.

Recherchez toutes les valeurs correspondantes en fonction d'une ou plusieurs conditions horizontalement

Recherchez toutes les valeurs correspondantes en fonction d'une condition horizontalement:

Pour Vlookup et renvoyer toutes les valeurs correspondantes en fonction d'une valeur spécifique horizontalement, la formule générique est:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
importante: m est le numéro de ligne de la première cellule de la plage de retour moins 1.
      n est le numéro de colonne de la première cellule de formule moins 1.

1. Veuillez appliquer la formule ci-dessous dans une cellule vide, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir la première valeur correspondante, voir capture d'écran:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Ensuite, sélectionnez la première cellule de formule et faites glisser la poignée de remplissage vers les cellules de droite jusqu'à ce que la cellule vide s'affiche et que tous les éléments correspondants aient été extraits, voir capture d'écran:

Conseils:

S'il y a des valeurs correspondantes en double dans la liste renvoyée, pour ignorer les doublons, veuillez utiliser ces formules, puis appuyez sur Entrer pour obtenir le premier résultat: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Continuez à saisir cette formule: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") dans une cellule à côté du premier résultat, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le deuxième résultat, puis faites glisser cette formule vers les cellules de droite pour obtenir toutes les autres valeurs correspondantes jusqu'à ce que la cellule vide s'affiche, voir capture d'écran:


Recherchez toutes les valeurs correspondantes en fonction de deux conditions ou plus horizontalement:

Pour Vlookup et renvoyer toutes les valeurs correspondantes en fonction de valeurs plus spécifiques horizontalement, la formule générique est:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
importante: m est le numéro de ligne de la première cellule de la plage de retour moins 1.
      n est le numéro de colonne de la première cellule de formule moins 1.

1. Appliquez la formule suivante dans une cellule vide dans laquelle vous souhaitez afficher le résultat:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Ensuite, sélectionnez la cellule de formule et faites glisser la poignée de remplissage vers les cellules de droite jusqu'à ce que la cellule vide s'affiche et que toutes les valeurs correspondantes basées sur les critères spécifiques aient été renvoyées, voir capture d'écran:

importante: Pour plus de critères, il vous suffit de joindre lookup_value et lookup_range dans la formule, comme: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


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

Recherchez toutes les valeurs correspondantes en fonction d'une condition verticalement:

Pour Vlookup et renvoyer toutes les valeurs correspondantes en fonction d'une valeur spécifique verticalement, la formule générique est:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
importante: m est le numéro de ligne de la première cellule de la plage de retour moins 1.
      n est le numéro de ligne de la première cellule de formule moins 1.

1. Copiez ou tapez la formule suivante dans une cellule où vous souhaitez obtenir le résultat, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir la première valeur correspondante, voir capture d'écran:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Ensuite, sélectionnez la première cellule de formule et faites glisser la poignée de remplissage vers d'autres cellules jusqu'à ce que la cellule vide s'affiche et que tous les éléments correspondants aient été répertoriés dans une colonne, voir capture d'écran:

Conseils:

Pour ignorer les doublons dans les valeurs correspondantes renvoyées, veuillez utiliser ces formules: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Puis appuyez Ctrl + Maj + Entrée clés ensemble pour obtenir la première valeur correspondante, puis faites glisser cette cellule de formule vers d'autres cellules jusqu'à ce que la cellule vide s'affiche, et vous obtiendrez le résultat dont vous avez besoin:


Recherchez toutes les valeurs correspondantes en fonction de deux conditions ou plus verticalement:

Pour Vlookup et renvoyer toutes les valeurs correspondantes en fonction de valeurs plus spécifiques verticalement, la formule générique est:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
importante: m est le numéro de ligne de la première cellule de la plage de retour moins 1.
      n est le numéro de ligne de la première cellule de formule moins 1.

1. Copiez la formule ci-dessous dans une cellule vide, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le premier élément correspondant.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Faites ensuite glisser la cellule de formule vers d'autres cellules jusqu'à ce que la cellule vide s'affiche, voir capture d'écran:

importante: Pour plus de critères, il vous suffit de joindre lookup_value et lookup_range dans la formule, comme: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup toutes les valeurs correspondantes basées sur deux conditions ou plus dans une seule cellule

Si vous souhaitez Vlookup 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.

Recherchez toutes les valeurs correspondantes basées sur une condition dans une seule cellule:

Veuillez appliquer la formule simple ci-dessous dans une cellule vide, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le résultat:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Conseils:

Pour ignorer les doublons dans les valeurs correspondantes renvoyées, veuillez utiliser ces formules: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Recherchez toutes les valeurs correspondantes basées sur deux conditions ou plus dans une seule cellule:

Pour gérer plusieurs conditions lors du renvoi de toutes les valeurs correspondantes dans une seule cellule, veuillez appliquer la formule ci-dessous, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le résultat:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

À noter :

1. La fonction TEXTJOIN est uniquement disponible dans Excel 2019 et Office 365.

2. Si vous utilisez Excel 2016 et les versions antérieures, veuillez utiliser la fonction définie par l'utilisateur des articles ci-dessous:


4. Vlookup pour renvoyer la ligne entière ou entière d'une cellule correspondante

Dans cette section, je vais expliquer comment récupérer la ligne entière d'une valeur correspondante à l'aide de la fonction Vlookup.

1. Veuillez copier ou taper la formule ci-dessous dans une cellule vide où vous souhaitez afficher le résultat, puis appuyez sur Entrer key pour obtenir la première valeur.

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

2. Ensuite, faites glisser la cellule de formule vers la droite jusqu'à ce que les données de la ligne entière soient affichées, voir capture d'écran:

importante: Dans la formule ci-dessus, F2 est la valeur de recherche sur laquelle vous souhaitez renvoyer toute la ligne, A1: D12 est la plage de données que vous souhaitez utiliser, A1 indique le premier numéro de colonne dans votre plage de données.

Conseils:

Si plusieurs lignes sont trouvées en fonction de la valeur correspondante, pour renvoyer toutes les lignes correspondantes, veuillez appliquer cette formule: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), puis appuyez sur Ctrl + Maj + Entrée touches ensemble pour obtenir le premier résultat, puis faites glisser la poignée de remplissage vers les cellules, voir capture d'écran:

Et puis faites glisser la poignée de remplissage vers le bas à travers les cellules pour obtenir toutes les lignes correspondantes, comme illustré ci-dessous:


5. Faire plusieurs fonctions Vlookup (Vlookup imbriqué) dans Excel

Parfois, vous voudrez peut-être rechercher des valeurs dans plusieurs tables, si l'une des tables contient la valeur de recherche donnée, comme illustré ci-dessous, dans ce cas, vous pouvez combiner une ou plusieurs fonctions Vlookup avec la fonction IFERROR pour effectuer plusieurs recherches.

La formule générique de la fonction Vlookup imbriquée est:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Remarque:

  • valeur de recherche: la valeur que vous recherchez;
  • Table1, Table2, Table3, ...: les tables dans lesquelles la valeur de recherche et la valeur de retour existent;
  • avec: le numéro de colonne de la table à partir de laquelle vous souhaitez renvoyer la valeur correspondante.
  • 0: Ceci est utilisé pour une correspondance exacte.

1. Veuillez appliquer la formule suivante dans une cellule vide où vous souhaitez mettre le résultat:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Ensuite, faites glisser la poignée de remplissage vers les cellules auxquelles vous souhaitez appliquer cette formule, et toutes les valeurs correspondantes ont été renvoyées comme illustré ci-dessous:

À noter :

  • 1. Dans la formule ci-dessus, J3 est la valeur que vous recherchez; A3: B7, D3: E7, G3: H7 sont les plages de table dans lesquelles la valeur de recherche et la valeur de retour existent; Le nombre 2 est le numéro de colonne de la plage à partir duquel renvoyer la valeur correspondante.
  • 2. Si la valeur de recherche ne peut pas être trouvée, une valeur d'erreur s'affiche, pour remplacer l'erreur par un texte lisible, veuillez utiliser cette formule: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup pour vérifier si la valeur existe en fonction 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 d'une autre liste, par exemple, si vous souhaitez rechercher les noms dans la colonne C et renvoyer simplement Oui ou Non si le nom est trouvé ou non dans la colonne A comme ci-dessous capture d'écran montré.

1. Veuillez appliquer la formule suivante dans une cellule vide:

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

2. Ensuite, faites glisser la poignée de remplissage vers les cellules que vous souhaitez remplir cette formule, et vous obtiendrez le résultat dont vous avez besoin, voir capture d'écran:

importante: Dans la formule ci-dessus, C2 est la valeur de recherche que vous souhaitez vérifier; A2: A10 est la liste de la plage à partir de laquelle les valeurs de recherche seront trouvées; le nombre 1 est le numéro de colonne à partir duquel vous souhaitez extraire la valeur de votre plage.


7. Vlookup et additionnez toutes les valeurs correspondantes en lignes ou en colonnes

Si vous travaillez avec des données numériques, parfois, lors de l'extraction des valeurs correspondantes du tableau, vous devrez peut-être également additionner les nombres dans plusieurs colonnes ou lignes. Cette section présentera quelques formules pour terminer ce travail dans Excel.

Vlookup et additionnez toutes les valeurs correspondantes dans une ou plusieurs lignes

Supposons que vous ayez une liste de produits avec des ventes pendant plusieurs mois, comme illustré ci-dessous, vous devez maintenant additionner toutes les commandes de tous les mois en fonction des produits donnés.

Vlookup et additionnez les premières valeurs correspondantes dans une ligne:

1. Veuillez copier ou entrer la formule suivante dans une cellule vide, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le premier résultat.

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

2. Ensuite, faites glisser la poignée de remplissage vers le bas pour copier cette formule dans les autres cellules dont vous avez besoin, et toutes les valeurs d'une ligne de la première valeur correspondante ont été additionnées, voir capture d'écran:

importante: Dans la formule ci-dessus: H2 est la cellule contenant la valeur que vous recherchez; A2: F9 est la plage de données (sans en-têtes de colonne) qui comprend la valeur de recherche et les valeurs correspondantes; Le nombre 2,3,4,5,6 {} sont les numéros de colonne utilisés pour calculer le total de la plage.


Vlookup et additionnez toutes les valeurs correspondantes sur plusieurs lignes:

La formule ci-dessus ne peut additionner les valeurs dans une ligne que pour la première valeur correspondante. Si vous souhaitez additionner toutes les correspondances sur plusieurs lignes, veuillez utiliser la formule suivante, puis faites glisser la poignée de remplissage vers les cellules que vous souhaitez appliquer cette formule, et vous obtiendrez le résultat souhaité dont vous avez besoin, voir capture d'écran:

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

importante: Dans la formule ci-dessus: H2 est la valeur de recherche que vous recherchez; A2: A9 sont les en-têtes de ligne qui contiennent la valeur de recherche; B2: F9 la plage de données des valeurs numériques que vous souhaitez additionner.


Vlookup et additionnez toutes les valeurs correspondantes dans une colonne ou plusieurs colonnes

Vlookup et additionnez les premières valeurs correspondantes dans une colonne:

Si vous souhaitez additionner la valeur totale pour les mois spécifiques, comme indiqué dans la capture d'écran ci-dessous.

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, maintenant, les premières valeurs correspondantes basées sur le mois spécifique dans une colonne ont été additionnées, voir capture d'écran:

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

importante: Dans la formule ci-dessus: H2 est la valeur de recherche que vous recherchez; B1: F1 correspond aux en-têtes de colonne contenant la valeur de recherche; B2: F9 la plage de données des valeurs numériques que vous souhaitez additionner.


Vlookup et additionnez toutes les valeurs correspondantes dans plusieurs colonnes:

Pour Vlookup et additionner toutes les valeurs correspondantes dans plusieurs colonnes, vous devez utiliser la formule suivante:

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

importante: Dans la formule ci-dessus: H2 est la valeur de recherche que vous recherchez; B1: F1 correspond aux en-têtes de colonne contenant la valeur de recherche; B2: F9 la plage de données des valeurs numériques que vous souhaitez additionner.


Recherchez et additionnez la première ou toutes les valeurs correspondantes avec une fonctionnalité puissante

Peut-être que les formules ci-dessus sont difficiles à retenir pour vous, dans ce cas, je recommanderai une fonctionnalité pratique - Recherche et somme of Kutools pour Excel, avec cette fonctionnalité, vous pouvez obtenir le résultat aussi facilement que possible.    Cliquez pour télécharger Kutools for Excel maintenant!


Vlookup et additionnez toutes les valeurs correspondantes en lignes et en 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.

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))

importante: Dans la formule ci-dessus: B2: F9 est la plage de données des valeurs numériques que vous souhaitez additionner; B1: F1 is les en-têtes de colonne contiennent la valeur de recherche sur laquelle vous souhaitez additionner; I2 est la valeur de recherche dans les en-têtes de colonne que vous recherchez; A2: A9 les en-têtes de ligne contiennent-ils la valeur de recherche sur laquelle vous souhaitez additionner; H2 est la valeur de recherche dans les en-têtes de ligne que vous recherchez.


8. Vlookup pour fusionner deux tables basées sur une ou plusieurs 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 résoudre ce problème, la fonction Vlookup peut également vous rendre service.

Vlookup pour fusionner deux tables basées sur une colonne clé

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

Formule 1: Utilisation de la fonction RECHERCHEV

Pour fusionner les deux tableaux en un seul basé sur une colonne clé, appliquez la formule suivante dans une cellule vide où vous souhaitez obtenir le résultat, puis faites glisser la poignée de remplissage vers les cellules que vous souhaitez appliquer cette formule, vous allez obtenir une table fusionnée avec la colonne de commande se joignant aux données de la première table en fonction des données de la colonne clé.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

importante: Dans la formule ci-dessus, A2 est la valeur que vous recherchez, E2: F8 est la table à rechercher, le numéro 2 est le numéro de colonne de la table à partir de laquelle récupérer la valeur.

Formule 2: Utilisation des fonctions INDEX et MATCH

Si vos données communes dans le côté droit et les données renvoyées dans la colonne de gauche dans la deuxième table, pour fusionner la colonne de commande, la fonction Vlookup est incapable de faire le travail. Pour rechercher de droite à gauche, vous pouvez utiliser les fonctions INDEX et MATCH pour remplacer la fonction Vlookup.

Veuillez copier ou entrer la formule ci-dessous dans une cellule vide, puis copiez la formule dans la colonne, et la colonne de commande a été jointe au premier tableau, voir capture d'écran:

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

importante: Dans la formule ci-dessus, A2 est la valeur de recherche que vous recherchez, E2: E8 est la plage de données que vous souhaitez renvoyer, F2: F8 est la plage de recherche qui contient la valeur de recherche.


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 basées sur ces colonnes communes, les fonctions INDEX et MATCH peuvent vous aider.

La formule générique pour fusionner deux tables basées sur plusieurs colonnes clés est:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Veuillez appliquer la formule ci-dessous dans une cellule vide où vous souhaitez placer le résultat, puis appuyez sur Ctrl + Maj + Entrée clés ensemble 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)

importante: Dans la formule ci-dessus, ce que les références de cellule représentent comme illustré ci-dessous:

2Ensuite, 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:

Capsules: Dans Excel 2016 et les versions ultérieures, vous pouvez également utiliser le Requête d'alimentation fonctionnalité pour fusionner deux ou plusieurs tables en une seule basée sur des colonnes clés. Veuillez cliquer pour connaître les détails étape par étape.

9. Vlookup correspondant aux valeurs sur plusieurs feuilles de calcul

Avez-vous déjà essayé de valeurs Vlookup sur plusieurs feuilles de calcul? Supposons que je dispose des trois feuilles de calcul suivantes avec une plage de données et que je souhaite maintenant obtenir une partie des valeurs correspondantes en fonction des critères de ces trois feuilles de calcul pour obtenir le résultat comme ci-dessous. Dans ce cas, le Valeurs Vlookup sur plusieurs feuilles de calcul le tutoriel peut vous faire une faveur étape par étape.


Les valeurs correspondantes VLOOKUP conservent le formatage des cellules

1. Vlookup pour obtenir le formatage des cellules (couleur de cellule, couleur de police) ainsi que la valeur de recherche

Comme nous le savons tous, la fonction Vlookup normale ne peut que nous aider à renvoyer la valeur correspondante d'une autre plage de données, mais parfois, vous voudrez peut-être renvoyer la valeur correspondante avec le formatage de la cellule, comme la couleur de remplissage, la couleur de police, le style de police comme ci-dessous la capture d'écran montrée. Cette section explique comment obtenir la mise en forme de la cellule avec la valeur renvoyée dans Excel.

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

1. Dans la feuille de calcul contient les données que vous souhaitez Vlookup, cliquez avec le bouton droit sur l'onglet de la feuille et sélectionnez Voir le code dans le menu contextuel. Voir la capture d'écran:

2. Dans le ouvert Microsoft Visual Basic pour applications , veuillez copier ci-dessous le code VBA dans la fenêtre Code.

Code VBA 1: Vlookup pour obtenir le formatage des cellules avec la valeur de recherche

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

3. Toujours dans le Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module, puis copiez le code VBA 2 ci-dessous dans la fenêtre Module.

Code VBA 2: Vlookup pour obtenir le formatage des cellules avec la valeur de recherche

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. Après avoir inséré les codes ci-dessus, cliquez sur Outils > Nos références et Microsoft Visual Basic pour applications la fenêtre. Puis vérifiez le Microsoft Script Runtime case à cocher dans le Références - VBAProject boite de dialogue. Voir les captures d'écran:

5. Puis clique OK pour fermer la boîte de dialogue, puis enregistrez et fermez la fenêtre de code, maintenant, revenez en arrière dans la feuille de calcul, puis appliquez cette formule: =LookupKeepFormat(E2,$A$1:$C$10,3) dans une cellule vide dans laquelle vous souhaitez afficher le résultat, puis appuyez sur la touche Entrée. Voir la capture d'écran:

importante: Dans la formule ci-dessus, E2 est la valeur que vous rechercherez, A1: C10 est la plage de table et le nombre 3 est le numéro de colonne de la table pour laquelle la valeur correspondante doit être renvoyée.

6. Ensuite, sélectionnez la première cellule de résultat et faites glisser la poignée de recopie vers le bas pour obtenir tous les résultats ainsi que leur mise en forme. Voir la capture d'écran.


2. Conserver le format de date à partir d'une valeur renvoyée par Vlookup

Normalement, lorsque vous utilisez la fonction Vloook pour rechercher et renvoyer la valeur de format de date correspondante, certains formats de nombres seront affichés comme ci-dessous. Pour conserver le format de date d'un résultat renvoyé, vous devez inclure la fonction TEXT dans la fonction Vlookup.

Veuillez appliquer la formule ci-dessous dans une cellule vide, puis faites glisser la poignée de remplissage pour copier cette formule dans d'autres cellules, et toutes les dates correspondantes ont été renvoyées comme illustré ci-dessous:

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

importante: Dans la formule ci-dessus, E2 est la valeur de l'apparence, A2: C9 est la plage de recherche, le nombre 3 est le numéro de colonne dont vous voulez que la valeur soit renvoyée, mm/dd/yyy est le format de date que vous souhaitez conserver.


3. Vlookup et retourne la valeur correspondante avec le commentaire de la cellule

Avez-vous déjà essayé de Vlookup pour renvoyer non seulement les données de cellule correspondantes, mais également le commentaire de cellule dans Excel, comme illustré ci-dessous? Pour résoudre cette tâche, la fonction définie par l'utilisateur ci-dessous peut vous rendre service.

1. Maintenez le ALT + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. Cliquez insérer > 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, entrez cette formule: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) dans une cellule vide pour localiser le résultat, puis faites glisser la poignée de recopie pour copier cette formule dans d'autres cellules, maintenant, les valeurs correspondantes ainsi que les commentaires sont renvoyés à la fois, voir capture d'écran:

importante: Dans la formule ci-dessus, D2 est la valeur de recherche que vous souhaitez renvoyer sa valeur correspondante, A2: B9 est la table de données que vous souhaitez utiliser, le nombre 2 est le numéro de colonne qui contient la valeur correspondante que vous souhaitez renvoyer.


4. Traitez le texte et les nombres réels dans Vlookup

Par exemple, j'ai une plage de données, le numéro d'identification dans la table d'origine est au format numérique, dans la cellule de recherche qui est stockée sous forme de texte, lors de l'application de la fonction Vlookup normale, un résultat d'erreur # N / A est affiché comme ci-dessous capture d'écran montré. Dans ce cas, comment pouvez-vous obtenir les informations correctes si le numéro de recherche et le numéro d'origine dans la table ont un format de données différent?

Pour traiter le texte et les nombres réels dans la fonction Vlookup, appliquez la formule suivante dans une cellule vide, puis faites glisser la poignée de remplissage vers le bas pour copier cette formule, et vous obtiendrez les résultats corrects comme illustré ci-dessous:

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

À noter :

  • 1. Dans la formule ci-dessus, D2 est la valeur de recherche que vous souhaitez renvoyer sa valeur correspondante, A2: B8 est la table de données que vous souhaitez utiliser, le nombre 2 est le numéro de colonne qui contient la valeur correspondante que vous souhaitez renvoyer.
  • 2. Cette formule fonctionne également bien si vous ne savez pas où vous avez des nombres et où vous avez du texte.

Télécharger des fichiers d'exemple RECHERCHEV

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes et conservation des données; Contenu des cellules divisées; Combiner les lignes en double et la somme / moyenne... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Formules favorites et insérer rapidement, Plages, graphiques et images; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Regroupement du tableau croisé dynamique par numéro de semaine, jour de la semaine et plus encore ... Afficher les cellules déverrouillées et verrouillées par différentes couleurs; Mettre en évidence les cellules qui ont une formule / un nom...
kte tab 201905
  • 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!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.