Comment comparer deux colonnes dans Excel à laide de VLOOKUP

Lorsque vous travaillez avec de grands ensembles de données dans Excel, vous devrez peut-être comparer deux colonnes pour trouver des similitudes ou des différences.

VLOOKUP est une fonction puissante qui vous permet de rechercher des données correspondantes entre deux colonnes. La fonction vous permet de rechercher des valeurs dans une colonne qui apparaissent dans une autre.

Cet article vous montre comment comparer deux colonnes à l'aide de VLOOKUP, afin que vous puissiez analyser efficacement vos données.

Table des matières

• Principes de base de la fonction Vlookup
  • Fonction de recherche V
• Préparation de vos données pour VLOOKUP
  • Formatage des données
• Comment utiliser VLOOKUP pour comparer deux colonnes
  • Gestion des autres erreurs
• Alternatives à VLOOKUP pour la comparaison de colonnes
  • 1. Utilisation des fonctions Index et Match
  • 2. Utiliser XLOOKUP dans Excel
• Cinq conseils pour améliorer les performances RECHERCHEV
  • 1. Limitez votre plage de recherche
  • 2. Utilisez des références absolues
  • 3. Triez vos données
  • 4. Utilisez une double RECHERCHEV
  • 5. Utilisez la mise en forme conditionnelle
• Utilisations avancées de VLOOKUP
• Notre dernier mot

Principes de base de la fonction Vlookup

Supposons que vous ayez une feuille de calcul avec deux listes d'éléments dans la colonne A et la colonne B. Vous souhaitez rechercher les éléments de la liste 1 qui apparaissent également dans la liste 2.

Vous pouvez imaginer que travailler manuellement sur des listes de milliers d'éléments prendrait énormément de temps. Heureusement, Excel apporte VLOOKUP à la rescousse !

Le terme RECHERCHEV signifie recherche verticale. La fonction comparera deux colonnes, trouvera des correspondances entre elles et retournera les valeurs associées.

Fonction de recherche V

Voici une syntaxe de base de la fonction RECHERCHEV :

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

Voici une décomposition des éléments :

• lookup_value : la valeur que vous souhaitez rechercher dans la première colonne de table_array.

• table_array : la plage de cellules contenant les données dans lesquelles vous souhaitez effectuer la recherche.

• col_index_num : le numéro de colonne dans le tableau_table dont vous voulez renvoyer la valeur.

• range_lookup : Facultatif. C'est soit TRUE (correspondance approximative) soit FALSE (correspondance exacte). La valeur par défaut est TRUE.

Préparation de vos données pour VLOOKUP

Avant d'utiliser RECHERCHEV pour comparer deux colonnes dans Excel , vous devez préparer vos données.

Créez deux colonnes distinctes dans votre feuille de calcul où vous souhaitez comparer les valeurs. Cet article utilise la colonne A et la colonne B pour nos exemples.

Voici les exemples de données utilisés dans cet article :

Voici l'exemple de tableau avec en-tête à ajouter à la section intitulée "Préparer vos données pour VLOOKUP":

Liste 1 Liste 2
Article 1 Article 3
Article 2
Article 4 Article 3 Article 1
Article 4 Article 5

Formatage des données

Il est important de s'assurer que les données des deux colonnes sont formatées de la même manière. VLOOKUP est sensible à la casse, ce qui signifie que les majuscules et les minuscules sont importantes.

Des erreurs de correspondance peuvent se produire dans le résultat final si le formatage est incohérent.

C'est aussi une bonne idée de supprimer les valeurs en double ou les cellules vides pour minimiser le risque d'erreurs.

Pour supprimer les valeurs en double :

1. Sélectionnez la colonne.

2. Accédez à l'onglet Données dans le ruban supérieur.

3. Cliquez sur Supprimer les doublons dans la section Outils de données.

Le bouton peut être un peu difficile à voir. Cette image vous aidera :

Pour supprimer les cellules vides :

1. Sélectionnez la colonne.

2. Accédez à l'onglet Accueil dans le ruban supérieur.

3. Développez le menu "Rechercher et sélectionner".

4. Choisissez "Aller à Spécial".

5. Sélectionnez "Vides" dans les options.

6. Cliquez sur "Supprimer" dans la section Cellules.

Comment utiliser VLOOKUP pour comparer deux colonnes

Une fois les données préparées, vous pouvez écrire la formule VLOOKUP pour comparer deux colonnes dans Excel et identifier les correspondances. Suivez ces étapes:

1. Sélectionnez une cellule dans une nouvelle colonne où vous souhaitez afficher les résultats de la comparaison (par exemple, la cellule C2).

2. Tapez la formule suivante : =VLOOKUP(A2, B:B, 1, FALSE))

3. Appuyez sur Entrée pour appliquer la formule.

4. Dans le cas d'une valeur correspondante, la valeur sera affichée dans la même ligne de la colonne de résultat (par exemple, la colonne C).

5. Faites glisser la formule vers le bas depuis C2 pour la copier sur autant de cellules que nécessaire.

Si vous utilisez nos exemples de données, placez vos résultats de comparaison dans la troisième colonne. La formule trouvera trois correspondances parmi les quatre éléments de la liste 1.

Notez que l'élément 2 s'affiche sous la forme #N/A. Excel nous dit que la correspondance n'est pas applicable, c'est-à-dire qu'elle est introuvable.

C'est correct et c'est une information utile sur les valeurs manquantes. Cependant, certains utilisateurs d'Excel peuvent penser qu'il y a un problème de données ou une erreur de fonction.

Il est recommandé d'afficher une indication différente indiquant qu'aucune correspondance n'a été trouvée. Cela pourrait simplement être un blanc.

Pour ce faire, combinez la fonction VLOOKUP avec la fonction IFNA comme ceci :

=IFNA(RECHERCHEV(A1, B:B, 1, FAUX), "")

La fonction IFNA détecte l'erreur #N/A et remplace la sortie par un espace vide (""). Vous pouvez également utiliser la fonction ISNA ou avoir une logique supplémentaire avec la fonction IF.

Gestion des autres erreurs

VLOOKUP peut parfois générer d'autres erreurs dans vos résultats de comparaison. Le #REF ! Une erreur dans Excel est un véritable problème avec vos données.

Cela se produit généralement lorsque votre plage spécifiée est incorrecte. Notre exemple a référencé la colonne entière, mais vous pouvez également utiliser des plages de cellules verticales.

Assurez-vous que la plage de recherche à laquelle vous faites référence couvre toutes les valeurs que vous souhaitez comparer.

Alternatives à VLOOKUP pour la comparaison de colonnes

Il existe deux principales fonctions de recherche alternatives lors de la comparaison de deux colonnes dans Excel pour trouver des correspondances.

1. Utilisation des fonctions Index et Match

Au lieu d'utiliser VLOOKUP, vous pouvez comparer deux colonnes dans Excel en combinant les fonctions INDEX et MATCH.

Cette méthode offre un moyen plus flexible de rechercher des données et est particulièrement utile lorsque vous travaillez avec des colonnes non adjacentes ou lorsque l'index de colonne peut changer.

Voici la syntaxe à mettre dans la colonne résultat :

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Return_range : la plage de cellules contenant les données que vous souhaitez renvoyer.

• Lookup_value : la valeur que vous souhaitez rechercher dans la plage de recherche.

• Lookup_range : la plage de cellules dans laquelle vous souhaitez trouver la valeur de recherche.

En utilisant les mêmes données dans les exemples précédents, nous remplaçons la formule VLOOKUP comme suit :

=(INDICE(B:B, CORRESPONDANCE(A2, B:B, 0)))

La fonction MATCH renvoie la position relative de la valeur de recherche dans la plage de recherche et la fonction INDEX renvoie la valeur correspondante de la plage de retour.

Les résultats sur les mêmes données seront les mêmes que ceux de RECHERCHEV que nous avons utilisés précédemment. Cette image montre les fonctions utilisées :

Vous pouvez également remplacer l'erreur #N/A par un message personnalisé. Voici un exemple d'utilisation de la fonction SIERREUR.

=SIERREUR(INDEX(B:B, CORRESPONDANCE(A2, B:B, 0)), "Non trouvé")

2. Utiliser XLOOKUP dans Excel

Pour les utilisateurs d'Excel 365 ou d'Excel 2019, XLOOKUP est un test logique alternatif à VLOOKUP pour trouver des valeurs communes dans deux colonnes.

XLOOKUP offre plusieurs avantages. Vous pouvez l'utiliser pour rechercher des données à la fois horizontalement et verticalement, travailler avec des colonnes non adjacentes et spécifier des valeurs personnalisées pour les erreurs.

La syntaxe de XLOOKUP est :

=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

• lookup_value : la valeur que vous souhaitez rechercher dans la plage de recherche.

• lookup_range : la plage de cellules dans laquelle vous souhaitez trouver la valeur de recherche.

• return_range : la plage de cellules contenant les données que vous souhaitez renvoyer.

• Ajoutez des valeurs d'erreur personnalisées, un mode de correspondance et des paramètres de mode de recherche selon vos besoins.

XLOOKUP trouvera la valeur de recherche dans la plage de recherche et renverra la valeur correspondante à partir de la plage de retour.

La formule est particulièrement utile lorsque vous avez deux listes intégrées dans des ensembles de données de plusieurs colonnes. Dans nos exemples précédents, les listes se trouvaient dans la première colonne et la deuxième colonne, mais votre feuille peut contenir plus de données que cela.

Voici un exemple avec deux listes dans la première et la troisième colonne d'une feuille de calcul :

La formule XLOOKUP ressemble à ceci :

=XRECHERCHE(A2,C:C,C:C,””)

Cette image montre le résultat avec la comparaison dans la colonne D. La première valeur est présente mais la seconde est manquante.

Notez que je n'ai pas de formules d'erreur supplémentaires, mais la valeur manquante est affichée sous forme de blanc. C'est parce que j'utilise le quatrième paramètre qui est une valeur d'erreur personnalisée. Dans ce cas, il s'agit d'une chaîne vide.

En prime, je vais vous montrer comment utiliser XLOOKUP pour comparer les lignes. Si vous avez deux listes qui se trouvent dans la ligne 1 et la ligne 2, l'utilisation la plus simple de la formule ressemble à ceci :

=XRECHERCHE(B1, 2:2, 2:2, "")

Cette image montre les résultats sur les deux lignes.

Avec sa fonctionnalité et sa flexibilité améliorées, XLOOKUP est une excellente alternative à VLOOKUP pour comparer des listes dans Excel afin de déterminer si des correspondances sont présentes.

Cinq conseils pour améliorer les performances RECHERCHEV

L'optimisation de votre fonction VLOOKUP peut vous aider à éviter de longs temps d'attente et à améliorer la réactivité globale de votre feuille de calcul Excel.

Voici six trucs et astuces que vous pouvez appliquer pour améliorer les performances de votre VLOOKUP dans Excel.

1. Limitez votre plage de recherche

J'ai utilisé des colonnes entières dans les exemples pour les rendre simples. Si vous travaillez avec une grande quantité de données, évitez de le faire. L'utilisation de colonnes complètes peut ralentir le processus de calcul d'Excel.

Au lieu de cela, essayez d'utiliser la plage exacte requise pour vos données (par exemple, A1:A100). Cela réduit le nombre de cellules que votre fonction VLOOKUP doit évaluer.

2. Utilisez des références absolues

Lorsque vous spécifiez une plage de données (par exemple, les cellules de B2 à B5), utilisez des références absolues. Cela garantit que la formule est cohérente et que les données existent lorsque vous les copiez dans plusieurs cellules.

Voici un exemple:

=RECHERCHEV(A2, $B$2:$B$5, 1, FAUX)

3. Triez vos données

Si vous savez que les données de la colonne de recherche sont triées par ordre croissant, vous pouvez utiliser VLOOKUP avec TRUE ou 1 pour l'argument 'range_lookup'.

Cela entraînera Excel pour effectuer une correspondance approximative, ce qui est plus rapide qu'une correspondance exacte dans toutes les cellules. Cependant, soyez prudent lorsque vous utilisez cette option, car un tri incorrect peut entraîner des résultats incorrects.

4. Utilisez une double RECHERCHEV

Vous pouvez utiliser deux fonctions VLOOKUP pour accélérer le processus de recherche dans Excel.

Le premier VLOOKUP déterminera si la valeur de recherche existe en définissant 'col_index_num' sur 1 et 'range_lookup' sur TRUE.

S'il renvoie TRUE, un deuxième VLOOKUP récupère la valeur souhaitée avec le 'range_lookup' défini sur TRUE.

5. Utilisez la mise en forme conditionnelle

Vous pouvez utiliser la mise en forme conditionnelle dans votre feuille de calcul Excel pour mettre en surbrillance les valeurs correspondantes ou manquantes dans la colonne spécifiée. Vous pouvez également appliquer des couleurs à des valeurs uniques. Cela facilite la lecture de vos données.

Vous trouverez le menu de mise en forme conditionnelle dans le groupe de styles de l'onglet Accueil.

Utilisations avancées de VLOOKUP

Les tests T pour échantillons appariés sont utilisés pour comparer les moyennes de deux échantillons liés. Cette vidéo montre leur utilisation dans Excel.

Si vous avez des données ou des variables supplémentaires que vous devez référencer lors de votre analyse, vous pouvez utiliser VLOOKUP pour récupérer les valeurs nécessaires à partir d'une autre table ou feuille de calcul.

Par exemple, vous pouvez utiliser VLOOKUP pour récupérer des informations démographiques ou des conditions de traitement pour chaque observation appariée dans votre ensemble de données.

Notre dernier mot

À présent, vous avez une solide compréhension de l'utilisation de VLOOKUP. Cette fonction puissante vous permet d'identifier rapidement les différences et les valeurs correspondantes entre deux listes, ce qui rend l'analyse des données plus efficace et précise.

Vous avez également vu des exemples d'autres fonctions de recherche et de référence, telles que XLOOKUP, INDEX et MATCH. L'ajout de ces éléments à vos compétences renforcera encore vos capacités d'analyse de données.

Continuez à pratiquer et à affiner vos compétences VLOOKUP, et vous deviendrez bientôt un expert dans la comparaison de colonnes dans Excel, ce qui vous fera gagner du temps et augmentera votre productivité.