Besoin d’indiquer “Yes/No” selon que chaque valeur de la Feuille B existe dans la Feuille A ? Voici des formules fiables, des correctifs aux “No” inexpliqués et des alternatives modernes (MATCH, XLOOKUP, Power Query) adaptées aux versions récentes d’Excel.
Comparer deux feuilles Excel : indiquer “Yes/No” selon la présence d’une valeur
Vue d’ensemble de la question
- Feuille A : ~6 000 valeurs en colonne A
- Feuille B : ~300 valeurs en colonne A
- Objectif : pour chaque ligne de Feuille B, écrire en colonne B Yes si la valeur de Feuille B!A existe dans Feuille A!A, sinon No.
- Problème rencontré : à partir d’une certaine ligne, la formule renvoie “No” alors que la valeur existe.
Réponse & solution immédiate
- Dans Feuille B, cellule B1, collez l’une des formules suivantes (adaptez le nom de feuille si nécessaire) :
Excel en anglais=IF(COUNTIF('Worksheet A'!$A:$A, A1)>0, "Yes", "No")Excel en français=SI(NB.SI('Worksheet A'!$A:$A; A1)>0; "Yes"; "No") - Copiez la formule vers le bas jusqu’à la dernière ligne utilisée de Feuille B.
Principe : COUNTIF / NB.SI compte les occurrences de la valeur de A1 dans la colonne A de Feuille A. Un résultat > 0 déclenche "Yes", sinon "No".
Pourquoi une série de “No” peut apparaître à partir d’une certaine ligne
Quand une formule correcte commence à renvoyer “No” au-delà d’une ligne (p. ex. au-delà de la ligne 35), l’origine est presque toujours référentielle (plage mal pointée) ou qualitative (données non nettoyées). Utilisez la checklist ci-dessous.
Checklist de diagnostic rapide
| Problème constaté | Cause probable | Correctif |
|---|---|---|
| La formule renvoie “No” au-delà d’une ligne (ex. > 35) | Formule saisie ou recopiée dans la mauvaise feuille ; plage source mal référencée | Vérifiez que la formule est bien en Feuille B et que le premier argument vise 'Worksheet A'!$A:$A. Verrouillez avec $ : $A:$A. |
| Valeurs “identiques” non détectées | Espaces ou caractères invisibles (NBSP), différences de casse, tirets, apostrophes typographiques | Nettoyez avant comparaison (voir plus bas). Exemple : =TRIM(SUBSTITUTE(A1,CHAR(160)," ")) puis comparez la version nettoyée. |
| Numéros vs texte (ex. 00123 vs 123) | Types hétérogènes : nombres dans une feuille, texte dans l’autre | Uniformisez : tout texte (formats “00000”) ou tout nombre (=VALUE(A1)). Ne mélangez pas. |
| Performance lente | Calcul sur toute la colonne d’une feuille large | Limitez la plage : 'Worksheet A'!$A$1:$A$6000 ou créez une plage dynamique (exemples plus bas). |
| Calcul figé | Mode de calcul sur “Manuel” | Activez “Automatique” (Formules → Options de calcul) et/ou appuyez sur F9. |
| Nom de feuille avec espaces | Absence d’apostrophes autour du nom de feuille | Encadrez le nom : 'Worksheet A'!A:A. |
| Plage Table non étendue | Des lignes ajoutées ne sont pas incluses | Convertissez en Table Excel (Ctrl + T) pour que la plage s’étende automatiquement. |
Nettoyer les données pour des correspondances fiables
Avant toute comparaison, normalisez les chaînes. Voici des recettes prêtes à l’emploi (créez des colonnes d’aide “Nettoyage_A” et “Nettoyage_B” si besoin).
| Symptôme | Formule de normalisation (anglais) | But |
|---|---|---|
| Espaces superflus | =TRIM(A1) | Supprime les espaces en début/fin et compresse les doubles espaces. |
| Espaces insécables (NBSP, code 160) | =TRIM(SUBSTITUTE(A1,CHAR(160)," ")) | Remplace les NBSP par des espaces normaux, puis TRIM. |
| Tabulations et retours chariot | =TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(9)," "),CHAR(10)," ")) | Nettoie tab & LF. |
| Uniformiser majuscules/minuscules | =UPPER(TRIM(A1)) | Compare en ignorant la casse. |
| Numéros stockés comme texte | =VALUE(A1) | Convertit en nombre. |
| Codes avec zéros à gauche | =TEXT(A1,"00000") | Uniformise sur 5 chiffres (adaptez le masque). |
Une fois les colonnes “nettoyées” prêtes dans A et B, comparez les colonnes nettoyées plutôt que les colonnes brutes.
Méthodes alternatives selon la version d’Excel
| Méthode | Formule (anglais) | Quand l’utiliser | Points forts |
|---|---|---|---|
COUNTIF / NB.SI | =IF(COUNTIF('Worksheet A'!$A:$A,A1)>0,"Yes","No") | Toutes versions | Simple et lisible. |
MATCH / EQUIV | =IF(ISNUMBER(MATCH(A1,'Worksheet A'!$A:$A,0)),"Yes","No") | Grandes listes | Légèrement plus rapide. |
XLOOKUP / RECHERCHEX | =IFERROR(XLOOKUP(A1,'Worksheet A'!A:A,"Yes"),"No") | Microsoft 365 | Syntaxe moderne, gère l’erreur en natif. |
| Comparaison sensible à la casse | =IF(SUMPRODUCT(--EXACT(A1,'Worksheet A'!$A:$A))>0,"Yes","No") | Données où “ABC” ≠ “abc” | Exactitude stricte. |
| Mise en forme conditionnelle | — | Contrôle visuel | Surligne doublons sans colonne supplémentaire. |
| Power Query (Merge) | — | Très grands jeux de données, flux réutilisables | Robuste, traçable, nettoyages intégrés. |
Formules prêtes à copier‑coller
Base (colonne entière)
=IF(COUNTIF('Worksheet A'!$A:$A, A1)>0, "Yes", "No")
=SI(NB.SI('Worksheet A'!$A:$A; A1)>0; "Yes"; "No")
Plage limitée (plus rapide)
=IF(COUNTIF('Worksheet A'!$A$1:$A$6000, A1)>0, "Yes", "No")
=SI(NB.SI('Worksheet A'!$A$1:$A$6000; A1)>0; "Yes"; "No")
Avec nettoyage côté B (recommandé)
- En B, créez Nettoyage_B en C1 :
=TRIM(SUBSTITUTE(A1,CHAR(160)," ")), puis recopiez. - En B1 (colonne résultat Yes/No), utilisez :
=IF(COUNTIF('Worksheet A'!$A:$A, C1)>0, "Yes", "No")
Version avec MATCH / EQUIV
=IF(ISNUMBER(MATCH(A1,'Worksheet A'!$A:$A,0)),"Yes","No")
=SI(ESTNUM(EQUIV(A1;'Worksheet A'!$A:$A;0));"Yes";"No")
Version case‑sensitive (EXACT)
=IF(SUMPRODUCT(--EXACT(A1,'Worksheet A'!$A:$A))>0,"Yes","No")
=SI(SOMMEPROD(--EXACT(A1;'Worksheet A'!$A:$A))>0;"Yes";"No")
Plage dynamique sans toute-colonne (Microsoft 365)
=LET(src,'Worksheet A'!$A:$A, last, MAX(IF(src<>"",ROW(src))), rng, INDEX(src,1):INDEX(src,last), IF(COUNTIF(rng,A1)>0,"Yes","No"))
Remarque : dans Excel FR, remplacez les virgules par des points‑virgules et ROW par LIGNE.
Tables Excel (références structurées)
Si vos données sont en TableA[ColonneA] et TableB[ColonneA] :
=IF(COUNTIF(TableA[ColonneA], [@ColonneA])>0, "Yes", "No")
=SI(NB.SI(TableA[ColonneA]; [@ColonneA])>0; "Yes"; "No")
Extraire uniquement les présents/absents (analyse rapide)
Microsoft 365 :
Liste des valeurs de B présentes dans A :
=FILTER(B!A:A, COUNTIF(A!A:A, B!A:A)>0)
Liste des valeurs de B absentes de A :
=FILTER(B!A:A, COUNTIF(A!A:A, B!A:A)=0)
FR : FILTRE au lieu de FILTER et séparateurs ;.
Mise en forme conditionnelle pour un contrôle visuel
- Sélectionnez Feuille B!A:A.
- Accueil → Mise en forme conditionnelle → Règles de mise en surbrillance des cellules → Valeurs en double.
- Dans la boîte, remplacez la plage de comparaison par
'Worksheet A'!$A:$Avia “Utiliser une formule pour déterminer…”, et utilisez :=COUNTIF('Worksheet A'!$A:$A, A1)>0
Avec Power Query : méthode robuste pour grandes listes
- Placez votre curseur dans la liste de Feuille A → Données → À partir d’un tableau/plage → créez TableA.
- Répétez pour Feuille B → créez TableB.
- Dans l’éditeur Power Query : Accueil → Fusionner des requêtes.
- Source : TableB (colonne A), Table à fusionner : TableA (colonne A).
- Type de jointure :
- Gauche externe (Left Outer) pour marquer les présents : une correspondance non nulle = “Yes”.
- Gauche anti (Left Anti) pour lister uniquement les absents.
- Développez la colonne issue de la fusion (icône “↔”). Ajoutez une colonne personnalisée :
= if [TableA.ColonneA] = null then "No" else "Yes" - Fermez & chargez. Le rafraîchissement (Ctrl+Alt+F5) mettra à jour le Yes/No.
Exemple concret
Supposons :
| Feuille A — Colonne A | Feuille B — Colonne A | Attendu en B (Yes/No) |
|---|---|---|
| Alpha | Alpha | Yes |
| Beta | beta | Yes (si nettoyage UPPER) |
| Gamma | Gamma | No (si trailing NBSP, sauf nettoyage) |
| 00123 | 123 | Yes (après harmonisation) |
| Delta | Epsilon | No |
Optimiser les performances et la fiabilité
- Limiter la plage : préférez
$A$1:$A$6000à$A:$Asur de grands classeurs. - Tables Excel : elles s’étendent automatiquement et rendent la formule autoportée (
TableA[ColonneA]). - Plages dynamiques :
INDEXpour borner jusqu’à la dernière ligne non vide sans volatilité (voir formuleLETplus haut). - Éviter les fonctions volatiles (
INDIRECT,OFFSET) si vous pouvez employerINDEX. - Nettoyer en amont avec Power Query quand les fichiers sources varient (CSV, exports ERP).
- Types cohérents : soit tout en texte, soit tout en nombres.
- Documenter : commentez la cellule B1 (clic droit → Insérer un commentaire) pour expliquer la logique aux collègues.
Variantes utiles
Afficher “Found/Missing” en anglais :
=IF(COUNTIF('Worksheet A'!$A:$A, A1), "Found", "Missing")
Compter le nombre d’occurrences exactes (au lieu de Yes/No) :
=COUNTIF('Worksheet A'!$A:$A, A1)
Signaler les doublons côté A :
=IF(COUNTIF('Worksheet A'!$A:$A, A1)>1, "Duplicate in A", "Unique in A")
Comparer deux colonnes sur plusieurs critères (ex. Colonne A + Colonne B doivent coïncider) :
=IF(COUNTIFS('Worksheet A'!$A:$A, A1, 'Worksheet A'!$B:$B, B1)>0, "Yes", "No")
Procédure pas à pas fiable pour votre cas
- Nettoyez les deux listes (au moins TRIM + remplacement du NBSP, voir recettes).
- Uniformisez les types : si vous avez des codes numériques avec zéros à gauche, choisissez “tout texte” (format personnalisé
00000) ou “tout nombre”. - Transformez vos listes en Tables (Ctrl + T) et renommez-les TableA et TableB.
- En TableB, ajoutez la colonne calculée :
=IF(COUNTIF(TableA[ColonneA], [@ColonneA])>0, "Yes", "No") - Vérifiez quelques lignes “bord” (début, milieu, fin) pour confirmer l’absence d’artefacts.
Questions fréquentes
La formule est‑elle sensible à la casse ? Non, COUNTIF/NB.SI et MATCH/EQUIV ne font pas la différence entre “ABC” et “abc”. Utilisez la variante EXACT si nécessaire.
Puis‑je comparer des feuilles dans des classeurs différents ? Oui, en préfixant la référence avec le nom du fichier entre crochets : ='[ClasseurA.xlsx]Feuille A'!$A:$A.
Pourquoi des valeurs visiblement identiques restent “No” ? Les espaces insécables, caractères invisibles (tab, retour chariot), apostrophes “courbes” et mélanges nombre/texte sont les causes les plus fréquentes ; appliquez la section Nettoyer les données.
Faut‑il éviter la référence à toute la colonne ? Sur quelques milliers de lignes, c’est acceptable ; au‑delà (feuilles lourdes, formules nombreuses), préférez une plage bornée ou une Table.
Que faire si la liste de A s’agrandit chaque semaine ? Optez pour une Table (s’étend automatiquement) ou un flux Power Query rafraîchissable.
Résumé des meilleures pratiques
- Références verrouillées (
$) et noms de feuilles encadrés d’apostrophes. - Données nettoyées avant comparaison.
- Tables Excel pour des plages auto‑extensibles.
- Alternatives
MATCH/XLOOKUPpour les gros volumes. - Power Query dès que le besoin devient récurrent ou volumineux.
Conclusion : pour marquer Yes/No selon la présence d’une valeur de Feuille B dans Feuille A, la solution la plus directe est COUNTIF/NB.SI. Si des “No” inexpliqués apparaissent au‑delà d’une ligne, vérifiez la plage, nettoyez les données (espaces, NBSP, types), et passez à des alternatives (MATCH, XLOOKUP, Power Query) lorsque le volume et la maintenance l’exigent.