Comparer deux listes dans Excel
Comparez deux listes Excel avec COUNTIF, XLOOKUP, la mise en forme conditionnelle ou Power Query pour repérer correspondances et valeurs manquantes.
Méthode 1 : comparer l’appartenance avec COUNTIF
Quand l’ordre n’a pas d’importance et que vous voulez seulement savoir si un élément existe, COUNTIF est direct. Si la liste 1 se trouve en A2:A100 et la liste 2 en B2:B100, saisissez =COUNTIF($B$2:$B$100,A2)>0 en C2 et recopiez vers le bas. TRUE signifie que A2 apparaît au moins une fois dans la liste 2. Enveloppez la formule dans IF quand des libellés comme Match et Missing sont plus faciles à filtrer.
Verrouillez la plage de recherche avec des signes dollar pour qu’elle ne glisse pas lors de la copie. Cette méthode considère les valeurs répétées comme présentes, mais ne prouve pas que les deux listes contiennent le même nombre d’exemplaires. Si la quantité compte, comparez =COUNTIF($A$2:$A$100,A2) et =COUNTIF($B$2:$B$100,A2) sur une liste de clés uniques.
Méthode 2 : renvoyer des détails associés avec XLOOKUP
Utilisez XLOOKUP quand une correspondance doit renvoyer un statut, un montant ou une description. Par exemple, =XLOOKUP(A2,$D$2:$D$100,$E$2:$E$100,"Missing") cherche A2 dans D et renvoie la valeur alignée de E. La correspondance exacte est la valeur par défaut, et la valeur « introuvable » explicite rend visibles les clés manquantes sans masquer les autres erreurs. XLOOKUP est disponible dans Microsoft 365 et dans Excel 2021 ou version ultérieure.
Les anciens classeurs peuvent employer =IFNA(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"Missing"). VLOOKUP exige que la clé soit dans la première colonne de son tableau, tandis que XLOOKUP peut renvoyer une valeur située de part et d’autre. Dans les deux cas, vérifiez que la clé correspond bien à une seule ligne ; sinon la première correspondance peut masquer des enregistrements sources en double.
Méthode 3 : mettre en évidence ou fusionner les écarts
Pour une revue visuelle, sélectionnez la première liste et choisissez Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule pour déterminer quelles cellules seront mises en forme. Utilisez =COUNTIF($B$2:$B$100,A2)=0 pour mettre en forme les éléments absents de la liste 2. Créez la règle inverse sur la liste 2 pour voir les omissions dans les deux sens, et vérifiez la plage « S’applique à » avant de valider la règle.
Pour des comparaisons récurrentes d’exports volumineux, Power Query est plus facile à auditer. Chargez chaque plage comme un tableau, fusionnez les requêtes sur la clé et choisissez une anti-jointure gauche (Left Anti) pour renvoyer les lignes présentes uniquement dans le premier tableau. Répétez dans l’autre sens pour obtenir un rapport complet des différences, et examinez les clés en double avant de joindre, car elles peuvent multiplier les lignes.
Là où la comparaison déraille
Une formule ligne à ligne comme =A2=B2 compare des positions, pas l’appartenance à une liste ; un seul élément inséré fait paraître différentes toutes les lignes suivantes. Les clés de texte peuvent aussi échouer à cause d’espaces invisibles : testez des correspondances connues et des absences connues.
Les types se comportent différemment selon les fonctions. XLOOKUP, MATCH et VLOOKUP traitent le nombre 100 et le texte "100" comme des valeurs différentes : une correspondance visible peut donc renvoyer #N/A. COUNTIF est plus indulgente : elle convertit le critère, et peut donc signaler une correspondance que XLOOKUP manquerait. COUNTIF lit aussi *, ? et ~ dans une clé comme des caractères génériques : une clé telle que "P*" correspond donc à tous les codes qui commencent par P. Comparez dans les deux sens avant de déclarer les listes identiques.
À vous de jouer
En C2, comptez si le SKU de A2 apparaît dans B2:B5.
| A | B | C | |
|---|---|---|---|
| 1 | List 1 | List 2 | Matches |
| 2 | P-20 | P-10 | |
| 3 | P-30 | P-20 | |
| 4 | P-50 | P-40 | |
| 5 | P-50 |
Réponse : =COUNTIF(B2:B5,A2)
Exemple vérifié
La liste 1 occupe A2:A4, la liste 2 occupe C2:C5. « Match » indique seulement que l’élément existe ; le comptage montre que la liste 2 contient deux exemplaires de Pens.
| A | B | C | |
|---|---|---|---|
| 1 | List 1 | List 2 | |
| 2 | Pens | Ink | |
| 3 | Ink | Glue | |
| 4 | Tape | Pens | |
| 5 | Pens |
| Formule | Résultat |
|---|---|
=COUNTIF($C$2:$C$5,A2)>0 | TRUE |
=IF(COUNTIF($C$2:$C$5,A4)>0,"Match","Missing") | "Missing" |
=COUNTIF($C$2:$C$5,A2) | 2 |
=IFNA(VLOOKUP(A3,$C$2:$C$5,1,FALSE),"Missing") | "Ink" |
=COUNTIF($C$2:$C$5,"P*") | 2 |
Erreurs fréquentes
- Comparer des positions de lignes alors que les listes sont triées différemment.
- Ne vérifier que A par rapport à B, et pas B par rapport à A.
- Ignorer le nombre de doublons ou les types texte et nombre incompatibles.
Questions fréquentes
Comment trouver les valeurs de A qui ne sont pas dans B ?
Utilisez =COUNTIF($B$2:$B$100,A2)=0 et recopiez vers le bas, puis filtrez sur TRUE.
Peut-on comparer des listes situées sur des feuilles différentes ?
Oui. Qualifiez la plage, par exemple =COUNTIF(Sheet2!$A$2:$A$100,A2)>0.
Pourquoi une correspondance visible renvoie-t-elle faux ?
Avec XLOOKUP, MATCH ou VLOOKUP, une valeur peut être du texte et l’autre un nombre, ou le texte peut contenir des espaces superflus ou des caractères cachés.
Quelle méthode pour un traitement mensuel répété ?
Power Query est souvent la meilleure, car une fusion documentée peut être actualisée avec des tables sources de remplacement.
Mettre la compétence en pratique
Poursuivez avec une leçon FormulaCamp vérifiée (en anglais) : XLOOKUP (en anglais) Gratuit
Ouvrir la leçon gratuite (en anglais)