Comment comparer deux colonnes dans Excel (en utilisant VLOOKUP & IF)

Lorsque vous travaillez avec des données dans Excel, tôt ou tard, vous devrez comparer les données. Cela peut consister à comparer deux colonnes ou même des données dans des feuilles / classeurs différents.

Dans ce tutoriel Excel, je vais vous montrer différentes méthodes pour comparer deux colonnes dans Excel et rechercher des correspondances ou des différences.

Il existe plusieurs façons de le faire dans Excel et dans ce tutoriel, je vais vous en montrer certaines (comme la comparaison à l’aide de la formule VLOOKUP ou de la formule IF ou de la mise en forme conditionnelle).

Alors commençons!

Comparer deux colonnes (côte à côte)

C’est le type de comparaison le plus basique où vous devez comparer une cellule d’une colonne avec la cellule de la même ligne dans une autre colonne.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez simplement vérifier si la valeur de la colonne A d’une cellule spécifique est la même (ou différente) par rapport à la valeur de la cellule adjacente.

Bien sûr, vous pouvez le faire lorsque vous avez un petit ensemble de données lorsque vous en avez un grand, vous pouvez utiliser une formule de comparaison simple pour y parvenir. Et rappelez-vous, il y a toujours un risque d’erreur humaine lorsque vous faites cela manuellement.

Alors laissez-moi vous montrer quelques façons simples de le faire.

Comparez côte à côte En utilisant l’opérateur Égal à Signe

Supposons que vous ayez l’ensemble de données ci-dessous et que vous souhaitiez savoir quelles lignes ont les données correspondantes et quelles lignes ont des données différentes.

Voici une formule simple pour comparer deux colonnes (côte à côte):

=A2=B2

La formule ci-dessus vous donnera un TRUE si les deux valeurs sont les mêmes et FALSE au cas où elles ne le seraient pas.

Maintenant, si vous avez besoin de connaître toutes les valeurs qui correspondent, appliquez simplement un filtre et n’affichez que toutes les VRAIES valeurs. Et si vous voulez connaître toutes les valeurs différentes, filtrez toutes les valeurs FAUSSES (comme indiqué ci-dessous):

Comparer Côte à côte En Utilisant la fonction IF

Une autre méthode que vous pouvez utiliser pour comparer deux colonnes peut être en utilisant la fonction IF.

Ceci est similaire à la méthode ci-dessus où nous avons utilisé l’opérateur égal à (=), avec un avantage supplémentaire. Lorsque vous utilisez la fonction IF, vous pouvez choisir la valeur que vous souhaitez obtenir lorsqu’il y a des correspondances ou des différences.

Par exemple, s’il y a une correspondance, vous pouvez obtenir le texte « Correspondance » ou obtenir une valeur telle que 1. De même, en cas de non-concordance, vous pouvez programmer la formule pour vous donner le texte « Non-concordance » ou vous donner une cellule 0 ou vide.

Ci-dessous se trouve la formule IF qui renvoie ‘Match’ lorsque les deux cellules ont la valeur de cellule et ‘Not a Match’ lorsque la valeur est différente.

=IF(A2=B2,"Match","Not a Match")

La formule ci-dessus utilise la même condition pour vérifier si les deux cellules (dans la même ligne) ont ou non des données correspondantes (A2 = B2). Mais puisque nous utilisons la fonction IF, nous pouvons lui demander de renvoyer un texte spécifique au cas où la condition serait vraie ou fausse.

Une fois que vous avez les résultats de la formule dans une colonne séparée, vous pouvez rapidement filtrer les données et obtenir des lignes qui ont les données correspondantes ou des lignes avec des données non appariées.

Mettez en surbrillance les lignes avec des données correspondantes (ou des données différentes)

Une autre excellente façon de vérifier rapidement les lignes qui ont des données correspondantes (ou des données différentes) consiste à mettre en surbrillance ces lignes à l’aide d’une mise en forme conditionnelle.

Vous pouvez faire les deux – mettez en surbrillance les lignes qui ont la même valeur dans une ligne ainsi que le cas où la valeur est différente.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez mettre en surbrillance toutes les lignes dont le nom est le même.

Vous trouverez ci-dessous les étapes à suivre pour utiliser la mise en forme conditionnelle pour mettre en surbrillance des lignes avec des données correspondantes:

1. Sélectionnez l’ensemble de données entier (sauf les en-têtes)
2. Cliquez sur l’onglet Accueil
3. Dans le groupe Styles, cliquez sur Mise en forme conditionnelle
4. Dans les options qui s’affichent, cliquez sur ‘Nouvelle règle’
5. Dans la boîte de dialogue « Nouvelle règle de formatage », cliquez sur l’option – « Utilisez une formule pour déterminer les cellules à formater’
6. Dans le champ  » Formater les valeurs où cette formule est vraie « , entrez la formule: =AA2 =BB2
7. Cliquez sur le bouton Format
8. Cliquez sur l’onglet ‘Remplir’ et sélectionnez la couleur dans laquelle vous souhaitez mettre en surbrillance les lignes ayant la même valeur dans les deux colonnes
9. Cliquez sur OK

Les étapes ci-dessus mettront instantanément en surbrillance les lignes dont le nom est le nom de la colonne idem dans les deux colonnes A et B (dans la même ligne). Et dans le cas où le nom est différent, ces lignes ne seront pas surlignées.

Si vous souhaitez comparer deux colonnes et mettre en surbrillance des lignes dont les noms sont différents, utilisez la formule ci-dessous dans la boîte de dialogue Mise en forme conditionnelle (à l’étape 6).

=$A2<>$B2

Comment cela fonctionne-t-il?

Lorsque nous utilisons une mise en forme conditionnelle avec une formule, elle ne met en évidence que les cellules où la formule est vraie.

Lorsque nous utilisons $A2 =BB2, il vérifiera chaque cellule (dans les deux colonnes) et verra si la valeur d’une ligne dans la colonne A est égale à celle de la colonne B ou non.

Dans le cas où il s’agit d’une correspondance exacte, il le mettra en surbrillance dans la couleur spécifiée, et dans le cas où il ne correspond pas, ce ne sera pas le cas.

La meilleure partie de la mise en forme conditionnelle est qu’elle ne vous oblige pas à utiliser une formule dans une colonne séparée. De plus, lorsque vous appliquez la règle sur un ensemble de données, celle-ci reste dynamique. Cela signifie que si vous modifiez un nom dans l’ensemble de données, la mise en forme conditionnelle s’ajustera en conséquence.

Comparer Deux Colonnes À l’aide de VLOOKUP (Trouver des données Correspondantes / différentes)

Dans les exemples ci-dessus, je vous ai montré comment comparer deux colonnes (ou listes) lorsque nous comparons simplement des cellules côte à côte.

En réalité, ce sera rarement le cas.

Dans la plupart des cas, vous aurez deux colonnes avec des données et vous devrez savoir si un point de données dans une colonne existe dans l’autre colonne ou non.

Dans de tels cas, vous ne pouvez pas utiliser un simple signe égal à ou même une fonction IF.

Vous avez besoin de quelque chose de plus puissant

something quelque chose qui se trouve dans l’allée de VLOOKUP!

Laissez-moi vous montrer deux exemples où nous comparons deux colonnes dans Excel à l’aide de la fonction VLOOKUP pour trouver des correspondances et des différences.

Comparez Deux Colonnes à l’aide de VLOOKUP et recherchez des correspondances

Supposons que nous ayons un ensemble de données comme indiqué ci-dessous où nous avons des noms dans les colonnes A et B.

Si vous devez savoir quels sont les noms qui sont dans la colonne B qui sont également dans la colonne A, vous pouvez utiliser la formule VLOOKUP ci-dessous:

=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")

La formule ci-dessus compare les deux colonnes (A et B) et vous donne le nom au cas où le nom se trouve également dans la colonne B A, et elle renvoie « Aucune correspondance » au cas où le nom se trouve dans la colonne B et non dans la colonne A.

Par défaut, la fonction VLOOKUP renverra une erreur #N / A au cas où elle ne trouverait pas de correspondance exacte. Donc, pour éviter d’obtenir l’erreur, j’ai enveloppé la fonction VLOOKUP dans la fonction IFERROR, de sorte qu’elle ne donne « Aucune correspondance » lorsque le nom n’est pas disponible dans la colonne A.

Vous pouvez également effectuer une comparaison inverse – pour vérifier si le nom se trouve dans la colonne A ainsi que dans la colonne B. La formule ci-dessous ferait cela:

=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")

Comparez Deux Colonnes à l’aide de VLOOKUP et Trouvez les différences (Points de données manquants)

Alors que dans l’exemple ci-dessus, nous avons vérifié si les données d’une colonne étaient présentes dans une autre colonne ou non.

Vous pouvez également utiliser le même concept pour comparer deux colonnes à l’aide de la fonction VLOOKUP et trouver les données manquantes.

Supposons que nous ayons un ensemble de données comme indiqué ci-dessous où nous avons quelques noms dans les colonnes A et B.

Si vous devez savoir quels sont les noms qui sont dans la colonne B qui ne sont pas dans la colonne A, vous pouvez utiliser la formule VLOOKUP ci-dessous:

=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")

La formule ci-dessus vérifie le nom de la colonne B par rapport à tous les noms de la colonne A. Au cas où il trouverait une correspondance exacte, il renverrait ce nom, et au cas où il ne trouverait pas de correspondance exacte, il renverrait l’erreur #N /A.

Étant donné que je suis intéressé à trouver les noms manquants qui se trouvent dans la colonne B et non dans la colonne A, j’ai besoin de connaître les noms qui renvoient l’erreur #N / A.

C’est pourquoi j’ai enveloppé la fonction VLOOKUP dans les fonctions IF et ISERROR. Cette formule entière donne la valeur – « Non disponible » lorsque le nom est manquant dans la colonne A, et « Disponible » lorsqu’il est présent.

Pour connaître tous les noms manquants, vous pouvez filtrer la colonne de résultat en fonction de la valeur « Non disponible ».

Vous pouvez également utiliser la fonction de CORRESPONDANCE ci-dessous pour obtenir le même résultat:

=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")

Requêtes courantes lors de la comparaison de Deux colonnes

Voici quelques requêtes courantes que je reçois habituellement lorsque les gens essaient de comparer des données dans deux colonnes dans Excel.

Q1. Comment comparer plusieurs colonnes dans Excel dans la même ligne pour les correspondances? Comptez également le nombre total de doublons.

Ans. Nous avons donné la procédure pour comparer deux colonnes dans Excel pour la même ligne ci-dessus. Mais si vous souhaitez comparer plusieurs colonnes dans Excel pour la même ligne, consultez l’exemple

=IF(AND(A2=B2, A2=C2),"Full Match", "")

Ici, nous avons comparé les données de la colonne A, de la colonne B et de la colonne C. Après cela, j’ai appliqué la formule ci-dessus dans la colonne D et j’obtiens le résultat.

Maintenant, pour compter les doublons, vous devez utiliser la fonction Countif.

=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")

Q2. Quel opérateur utilisez-vous pour les correspondances et les différences?

Ans. Voici les opérateurs à utiliser:

• Pour trouver des correspondances, utilisez le signe égal à (=)
• Pour trouver des différences (discordances), utilisez le signe non égal à (<>)

Q3. Comment comparer deux tables différentes et extraire des données correspondantes?

Ans. Pour cela, vous pouvez utiliser la fonction VLOOKUP ou la fonction de correspondance INDEX&. Pour mieux comprendre cette chose, nous prendrons un exemple.

Ici, nous allons prendre deux tables et voulons maintenant tirer les données correspondantes. Dans la première table, vous avez un ensemble de données et dans la deuxième table, prenez la liste des fruits, puis utilisez les données de correspondance de tirage dans une autre colonne. Pour la correspondance de traction, utilisez la formule

=INDEX($B:$B,MATCH($D2,$A:$A,0))

Q4. Comment supprimer les doublons dans Excel?

Ans. Pour supprimer les données en double, vous devez d’abord trouver les valeurs en double.

Pour trouver le duplicata, vous pouvez utiliser diverses méthodes telles que la mise en forme conditionnelle, Vlookup, l’instruction If, et bien d’autres. Excel dispose également d’un outil intégré dans lequel vous pouvez simplement sélectionner les données et supprimer les doublons d’une colonne ou même de plusieurs colonnes

Q5. Je peux voir qu’il y a une valeur correspondante dans les deux colonnes. Cependant, les formules que vous avez partagées ci-dessus ne les considèrent pas comme des correspondances exactes. Pourquoi?

Ans: Excel considère quelque chose comme une correspondance exacte lorsque chaque caractère d’une cellule est égal à l’autre. Il y a de fortes chances que dans votre ensemble de données il y ait des espaces de début ou de fin.

Bien que ces espaces puissent encore faire paraître les valeurs égales à l’œil nu, pour Excel, elles sont différentes. Si vous avez un tel ensemble de données, il est préférable de se débarrasser de ces espaces (vous pouvez utiliser des fonctions Excel telles que TRIM pour cela).

Q7. Comment comparer deux colonnes qui donnent le résultat comme VRAI lorsque les valeurs entières de toutes les premières colonnes ne sont pas inférieures aux valeurs entières de la deuxième colonne. Pour résoudre ce problème, je n’ai pas besoin de mise en forme conditionnelle, de fonction Vlookup, d’instruction If et d’autres formules. J’ai besoin de la formule pour résoudre ce problème.

Ans. Vous pouvez utiliser la formule de tableau pour résoudre ce problème.

La syntaxe est {= ET (H6:H12 > I6:I12)}. Cela vous donnera « True » chaque fois que la valeur de la colonne H est supérieure à la valeur de la colonne I, sinon « False » sera le résultat.

Vous aimerez peut-être également les tutoriels Excel suivants:

• Comparer Deux Colonnes dans Excel (pour les correspondances et les différences)
• Comment Masquer Les Colonnes En Fonction De La Valeur Des Cellules dans Excel
• Comment Diviser Une Colonne en Plusieurs Colonnes dans Excel
• Comment Sélectionner d’Autres Colonnes dans Excel (ou chaque Nième Colonne)
• Comment Coller dans une Colonne Filtrée En Sautant les Cellules Cachées
• Meilleurs livres Excel (qui feront de vous un pro Excel dans 2020)

signaler cette annonce