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

Par exemple, vous avez listé tous les scores des étudiants et les scores du dernier semestre dans deux feuilles de calcul comme le montre la capture d'écran ci-dessous. Maintenant, vous souhaitez comparer les scores dans les deux feuilles de calcul et mettre en surbrillance les lignes dans la feuille Score si les scores sont supérieurs à ceux du dernier semestre. Dans cette situation, vous pouvez appliquer la mise en forme conditionnelle basée sur la fonction VLOOKUP dans Excel.

1Dans la feuille Score, sélectionnez les scores des étudiants à l'exception des en-têtes (dans mon cas, je sélectionne B3:C26), puis cliquez sur Accueil > Mise en forme conditionnelle > Nouvelle règleVoir la capture d'écran :

2Dans la boîte de dialogue Nouvelle règle de mise en forme, veuillez procéder comme suit :
(1) Cliquez pour sélectionner Utiliser une formule pour déterminer les cellules à mettre en forme dans la Sélectionner un type de règle liste déroulante ;
(2) Dans la Mettre en forme les valeurs où cette formule est vraie zone, veuillez entrer cette formule =VLOOKUP($B3,'Score of Last Semester'!$B$2:$C$26,2,FALSE) < Score!$C3;
(3) Cliquez sur le bouton Format bouton.

Remarque : dans la formule ci-dessus,

• $B3 est la première cellule contenant le nom de l'étudiant dans la feuille Score ;
• 'Score of Last Semester'!$B$2:$C$26 est le tableau des scores du dernier semestre dans la feuille Score of Last Semester ;
• 2 signifie rechercher des valeurs dans la deuxième colonne du tableau des scores du dernier semestre.
• Score!$C3 est la première cellule de score dans la feuille Score.

3Dans la boîte de dialogue Format de cellule, allez dans l'onglet Remplissage onglet, sélectionnez une couleur de remplissage, puis cliquez sur OK > OK pour fermer les deux dialogues.

Maintenant, vous verrez si le score d'un étudiant dans la feuille Score est supérieur à celui dans la feuille Score of Last Semester la ligne de cet étudiant sera automatiquement mise en surbrillance. Voir la capture d'écran :

Vous pouvez également utiliser la fonction VLOOKUP pour faire correspondre des valeurs dans deux feuilles de calcul, puis appliquer la mise en forme conditionnelle basée sur les résultats de VLOOKUP et les correspondances dans Excel. Veuillez procéder comme suit :

Remarque : Faites glisser la barre de défilement y pour voir l'image ci-dessus.

Par exemple, j'ai une liste de gagnants dans Sheet1 et la liste des étudiants dans Sheet2 comme le montre la capture d'écran ci-dessous. Maintenant, je vais faire correspondre la liste des gagnants et la liste des étudiants avec la fonction VLOOKUP, puis mettre en surbrillance les lignes correspondantes dans la liste des gagnants.

1Sélectionnez la liste des gagnants à l'exception des en-têtes, puis cliquez sur Accueil > Mise en forme conditionnelle > Nouvelle règle.

2Dans la boîte de dialogue Nouvelle règle de mise en forme, veuillez procéder comme suit :
(1) Dans la Sélectionner un type de règle liste déroulante, veuillez cliquer pour sélectionner Utiliser une formule pour déterminer les cellules à mettre en forme;
(2) Dans la Mettre en forme les valeurs où cette formule est vraie zone, veuillez entrer cette formule =NOT(ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE)));
(3) Cliquez sur le bouton Format bouton.

Remarque : Dans la formule ci-dessus,

• $C3 est le premier nom dans la liste des gagnants ;
• Sheet2!$B$2:$C$24 est la liste des étudiants dans Sheet2 ;
• 1 signifie rechercher la valeur correspondante dans la première colonne de la liste des étudiants.

Si vous devez mettre en surbrillance les valeurs non correspondantes, vous pouvez utiliser cette formule =ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE))

3Dans la boîte de dialogue Format de cellule, allez dans l'onglet Remplissage onglet, cliquez pour sélectionner une couleur de remplissage, puis cliquez sur OK > OK pour fermer les deux dialogues.

Maintenant, vous verrez si un nom dans la liste des gagnants correspond (ou ne correspond pas) à la liste des étudiants, la ligne de ce nom sera automatiquement mise en surbrillance.

Si vous avez installé Kutools pour Excel, vous pouvez utiliser sa fonction Sélectionner les cellules identiques et différentes pour facilement appliquer la mise en forme conditionnelle basée sur VLOOKUP et les résultats correspondants dans Excel. Veuillez procéder comme suit :

Kutools pour Excel - Comprend plus de 300 outils pratiques pour Excel. Essai gratuit complet de 60 jours, aucune carte de crédit requise ! Obtenez-le maintenant

1Cliquez sur Kutools > Sélectionner > Sélectionner les cellules identiques et différentes pour activer cette fonctionnalité.

2Dans la boîte de dialogue Sélectionner les cellules identiques et différentes, veuillez procéder comme suit :
(1) Sélectionnez la colonne Nom de la liste des gagnants dans la zone Rechercher des valeurs dans boîte, sélectionnez la colonne Nom de la liste des étudiants dans la Selon boîte, et il est facultatif de cocher l'option Mes données contiennent des en-têtes en fonction de vos besoins.
(2) Cochez l'option Chaque ligne dans la section Basé sur section ;
(3) Cochez l'option Valeurs identiques ou Valeurs différentes en fonction de vos besoins dans la section Trouver section ;
(4) Cochez l'option Couleur de fond de remplissage option, et sélectionnez une couleur de remplissage dans la liste déroulante ci-dessous ;
(5) Cochez l'option Sélectionner la ligne entière option.

3. Cliquez sur le bouton Ok pour appliquer cette fonctionnalité.

Maintenant, vous verrez que toutes les lignes avec des valeurs correspondantes (ou non correspondantes) sont mises en surbrillance et sélectionnées en une seule fois, et simultanément, une boîte de dialogue apparaît et indique combien de lignes ont été sélectionnées.