Comment appliquer une mise en forme conditionnelle basée sur une fonction VLOOKUP dans Excel ?

Supposons que vous teniez une liste des notes des élèves pour le semestre en cours et un semestre précédent, et que vous souhaitiez identifier rapidement les élèves ayant progressé. Dans ce scénario, vous voulez comparer la note de chaque élève ce semestre avec son résultat du semestre précédent et mettre visuellement en évidence ceux qui ont obtenu de meilleurs résultats.

Pour mettre en surbrillance une plage de lignes dans la feuille de calcul Score lorsque la note actuelle d’un élève est supérieure à celle du semestre dernier, vous pouvez utiliser la fonction RECHERCHEV dans une règle de mise en forme conditionnelle. Cette approche est idéale pour suivre les performances académiques, surveiller les jalons d’un projet ou réaliser toute analyse comparative reposant sur des mesures répétées nécessitant une comparaison.

1. Dans la feuille de calcul Score, sélectionnez la plage des notes des élèves à évaluer (sans les en-têtes ; dans cet exemple, sélectionnez B3:C26). Rendez-vous dans l’onglet Accueil, cliquez sur Utiliser la mise en forme conditionnelle, puis choisissez Nouvelle règle.

2. Dans la boîte de dialogue Nouvelle règle de mise en forme, procédez comme suit :

1. Sélectionnez Utiliser une formule pour déterminer les cellules à mettre en forme.
2. Saisissez la formule suivante dans la zone Mettre en forme les valeurs pour lesquelles cette formule est vraie:

   =VLOOKUP($B3,'Score of Last Semester'!$B$2:$C$26,2,FALSE) < Score!$C3

3. Cliquez sur le bouton Format pour choisir la mise en forme souhaitée.

Remarque :Dans cette formule,

• $B3 fait référence au nom du premier élève dans la feuille Score. Lorsque vous appliquez la mise en forme conditionnelle à plusieurs lignes, Excel ajuste automatiquement cette référence pour chaque ligne.
• ‹Score du semestre dernier›!$B$2:$C$26 définit la plage de recherche des notes du semestre précédent. Ajustez cette plage si votre liste est plus longue ou commence et se termine sur des lignes différentes.
• 2 indique que les valeurs à récupérer dans la plage de recherche se trouvent dans sa deuxième colonne.
• Score!$C3 fait référence à la note actuelle de l’élève dans la feuille de calcul mise en forme.

3. Dans la boîte de dialogue Définir le format des cellules, accédez à l’onglet Remplissage, choisissez une couleur de mise en évidence, puis cliquez sur OK pour appliquer les modifications et fermer la boîte de dialogue.

Une fois appliquée, la mise en forme mettra automatiquement en évidence chaque ligne d’élève dont la note du semestre en cours est supérieure à celle du Enregistrement précédent, permettant ainsi d’identifier facilement les tendances positives en un coup d’œil.

Si certaines lignes ne sont pas mises en évidence comme prévu, vérifiez que les deux feuilles utilisent des noms identiques (sans espaces supplémentaires ni orthographe incohérente) et que les plages référencées incluent toutes les données nécessaires. Si RECHERCHEV ne trouve pas de correspondance, elle renvoie une erreur ; il est donc essentiel d’assurer une cohérence dans la disposition des données pour obtenir des résultats précis.

Vous souhaitez peut-être identifier visuellement si des éléments d’une liste figurent dans une autre feuille — par exemple, mettre en évidence les participants d’une liste de gagnants qui apparaissent également dans une liste principale. En combinant la mise en forme conditionnelle avec RECHERCHEV, vous obtenez une mise en évidence en temps réel des éléments correspondants ou non correspondants, une solution particulièrement utile dans de nombreux scénarios de gestion d’événements ou de participants.

Remarque : Faites glisser la barre de défilement verticale pour faire apparaître l’image ci-dessus.

Dans ce cas d’usage, vous avez peut-être une liste de gagnants sur la Feuil1 et une liste complète des élèves sur la Feuil2. Pour surligner les plages nommées dans la liste des gagnants qui figurent également dans la liste principale, procédez comme suit :

1. Sélectionnez les noms dans la liste des gagnants (sans les en-têtes), puis accédez à Accueil > Utiliser la mise en forme conditionnelle > Nouvelle règle.

2. Dans la boîte de dialogue « Nouvelle règle de mise en forme », procédez comme suit :

1. Dans Sélectionner un type de règle, choisissez Utiliser une formule pour déterminer les cellules à mettre en forme.
2. Saisissez la formule suivante dans la zone Mettre en forme les valeurs pour lesquelles cette formule est vraie:

   =NOT(ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE)))

3. Cliquez sur le bouton Format pour définir le style de mise en évidence.

Remarque :

• $C3 fait référence au prénom dans la liste des gagnants ; assurez-vous que votre sélection corresponde bien à la disposition réelle de vos données.
• Sheet2!$B$2:$C$24 est la table de recherche contenant la liste complète des élèves. Ajustez la plage selon vos besoins pour l’adapter à votre feuille.
• 1 indique que la fonction RECHERCHEV effectuera sa recherche dans la première colonne de la plage limitée.

Si vous devez Surligner les plages nommées de la liste des gagnants qui ne sont pasprésents dans la liste des élèves, utilisez la formule inverse :

=ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE))

3. Dans la boîte de dialogue Définir le format des cellules, accédez à l’onglet Remplissage, choisissez votre couleur de fond préférée, puis cliquez sur OK OK pour terminer.

Une fois la règle appliquée, tous les noms répondant à vos critères de correspondance ou de non-correspondance seront automatiquement mis en évidence. Cela rend la vérification des erreurs, la validation des participants ou la conciliation des listes beaucoup plus efficace.

Conseil : en cas de résultats inattendus, vérifiez soigneusement vos plages de recherche pour détecter d’éventuels espaces superflus ou valeurs incohérentes susceptibles d’interférer avec la logique de correspondance exacte de RECHERCHEV. Envisagez d’utiliser la fonction SUPPRESPACE dans des colonnes auxiliaires pour normaliser vos données si nécessaire.

Si vous disposez de Kutools pour Excel, vous bénéficiez d’une flexibilité et d’une simplicité accrues pour comparer des listes et appliquer la mise en forme conditionnelle. Grâce à la fonctionnalité Sélectionner les cellules identiques/différentes, mettez en évidence les valeurs correspondantes ou non correspondantes en quelques clics seulement — et réduisez ainsi le risque d’erreurs courantes liées à la saisie manuelle de formules.

Kutools pour Excel comprend plus de 300 outils pratiques pour Excel, offrant un soutien robuste pour comparer et mettre en forme vos données. C’est particulièrement avantageux si vous travaillez régulièrement avec des listes étendues ou des jeux de données variés. Testez toutes les fonctionnalités gratuitement pendant 60 jours, sans carte bancaire requise.

1. Cliquez sur Kutools > Sélectionner > Sélectionner les cellules identiques/différentes pour lancer l’outil.

2. Dans la boîte de dialogue « Sélectionner les cellules identiques/différentes », configurez vos paramètres comme suit :

1. Dans la zone Rechercher les valeurs dans, sélectionnez la colonne « Nom » de la liste des gagnants.
2. Dans la zone Selon, sélectionnez la colonne « Nom » de la liste des élèves.
3. Si vos données contiennent des en-têtes, cochez l’option Inclure les titres selon vos besoins.
4. Sous Basé sur, choisissez Chaque ligne pour une comparaison ligne par ligne.
5. Dans la section Rechercher, sélectionnez soit Valeurs identiques, soit Valeur différente, selon que vous souhaitez mettre en évidence les correspondances ou les écarts.
6. Cochez Couleur d'arrière-plan de remplissage et choisissez la couleur de votre choix pour la mise en évidence.
7. Si vous souhaitez mettre en évidence la ligne entière, sélectionnez Sélectionner la ligne entière.

3. Cliquez sur OK pour exécuter l’opération. L’outil mettra immédiatement en évidence et sélectionnera les lignes contenant des valeurs identiques (ou différentes). Une boîte de dialogue s’affiche ensuite avec le nombre de lignes sélectionnées, vous permettant d’évaluer rapidement les résultats de la comparaison.

Cette méthode est particulièrement avantageuse avec de longues listes, car elle élimine le travail manuel consistant à écrire et à déboguer des formules. De plus, la rétroaction visuelle et la boîte de dialogue récapitulative réduisent les suppositions et les erreurs potentielles.