Comparer des listes avec VLOOKUP
Une exigence courante dans Excel est la nécessité de comparer deux listes. Dans ce tutoriel, nous allons comparer les listes avec VLOOKUP.
Vous devrez peut-être comparer deux listes pour mettre en évidence les enregistrements manquants, mettre en évidence les enregistrements correspondants ou renvoyer une valeur.
La fonction VLOOKUP peut être utilisée pour comparer des listes dans Excel en recherchant une valeur d’une liste dans une autre. Vous pouvez ensuite prendre les mesures requises.
Ce tutoriel montre comment comparer des listes avec VLOOKUP. La fonction VLOOKUP peut être utilisée avec d’autres formules ou fonctionnalités Excel pour obtenir différents résultats liés à la comparaison de deux listes.
Combinez avec la fonction IF pour Renvoyer une Valeur
Disons que nous avons deux listes de clients comme l’image ci-dessous. Les deux listes sont identiques, à l’exception du fait que la liste 1 compte moins de clients que la liste 2.
Nous voulons comparer les deux listes et identifier les différences. Le champ ID sera utilisé pour vérifier les deux listes car il s’agit d’un champ unique.
Dans ce premier exemple, nous allons rechercher les clients de la liste 1 dans la liste 2 et renvoyer le texte « Introuvable » s’ils sont manquants. S’ils se trouvent dans la liste 2, nous garderons la cellule vide.
La formule ci-dessous utilise la fonction VLOOKUP ainsi que les fonctions IF et ISNA pour atteindre cet objectif.
Jetons un coup d’œil au rôle que joue chaque fonction dans cette formule.
VLOOKUP: La fonction VLOOKUP a été utilisée pour rechercher le client dans la deuxième liste et renvoyer l’ID client (spécifié comme 1 dans la formule).
Si la fonction VLOOKUP ne trouve pas le client, elle renvoie le message d’erreur #N/A. C’est ce qui nous intéresse car cela signifie que le client est sur la liste 1, mais pas sur la liste 2.
ISNA : La fonction ISNA est utilisée pour détecter le message #N/A ramené par VLOOKUP et le traduire en True. Si un ID est renvoyé, il renvoie False.
SI: La fonction IF affichera le texte « introuvable » si ISNA renvoie True et gardera la cellule vide si ISNA renvoie False. Cette fonction est utilisée pour prendre l’action que vous souhaitez en fonction des résultats de VLOOKUP.
Comparez les listes avec VLOOKUP pour Mettre en évidence les enregistrements manquants
Cette formule peut également être utilisée avec une mise en forme conditionnelle pour mettre en évidence les différences entre les deux listes, au lieu de renvoyer une valeur.
Cet exemple montre comment changer la couleur de la cellule de toute la ligne du tableau lorsqu’un client est absent de la deuxième liste. Le formatage de la ligne entière plutôt que d’une seule cellule permettra d’identifier plus clairement les enregistrements manquants, en particulier dans un tableau avec de nombreuses colonnes.
- Sélectionnez toutes les cellules du tableau à l’exception de la ligne d’en-tête.
- Cliquez sur Mise en forme conditionnelle dans l’onglet Accueil du ruban et sélectionnez Nouvelle règle.
- Sélectionnez Utiliser une formule pour déterminer les cellules à formater à partir de la moitié supérieure de la fenêtre. Dans la moitié inférieure, entrez la formule ci-dessous dans la case prévue à cet effet.
- Cliquez sur le bouton Format et choisissez la mise en forme que vous souhaitez appliquer à la ligne des enregistrements manquants
Comparez deux listes et Mettez en surbrillance les enregistrements correspondants
Pour comparer les deux listes et mettre en surbrillance les enregistrements correspondants, la fonction NOT devra être ajoutée à la formule précédente.
=NOT(ISNA(VLOOKUP($A2,'Customers 2'!$A:$F,1,FALSE)))
La fonction NOT est utilisée pour inverser le résultat du VLOOKUP. Ainsi, au lieu de mettre en évidence les enregistrements manquants, il met en évidence les correspondances.