Comment combiner VLOOKUP() avec IF() dans Excel ?

Si vous gérez de grands ensembles de données dans Excel, vous savez à quel point il est difficile de les organiser et de les analyser efficacement. Mais j'ai une solution pour vous : Vous pouvez imbriquer la fonction VLOOKUP() dans une instruction IF() pour créer des consultations dynamiques basées sur des conditions spécifiques. Cette approche vous permet de consulter différents tableaux en fonction d'une condition et de gérer les erreurs de manière plus élégante.

Plus précisément, VLOOKUP() recherche des points de données spécifiques dans un tableau et les instructions IF() vous permettent de prendre des décisions conditionnelles sur la base de ces données. Ces deux fonctions sont, comme nous le savons, extrêmement importantes. Dans cet article, nous allons ajouter un nouvel outil à la boîte à outils : Vous apprendrez comment tirer parti de la puissance de ces deux fonctions pour effectuer des recherches conditionnelles, et nous l'illustrerons par des exemples pratiques.

La réponse rapide : Comment combiner VLOOKUP() et IF()

Pour créer une consultation conditionnelle, commencez par une instruction IF() et utilisez VLOOKUP() à l'intérieur de celle-ci pour renvoyer des résultats différents en fonction d'une condition. Nous pouvons utiliser le code suivant pour vérifier si un produit est en stock en fonction de sa quantité. Il vous suffit de suivre les étapes suivantes :

• Commencez par la fonction IF(): =IF()

• À l'intérieur de la fonction IF(), utilisez VLOOKUP().

=IF(VLOOKUP(C2, $A$2:$B$6, 2, FALSE) > 0, "In Stock", "Out of Stock")

Comprendre les instructions VLOOKUP() et IF() dans Excel

VLOOKUP() vous aide à trouver des données dans un tableau, tandis que les instructions IF() vous permettent de prendre des décisions en fonction de ces données. Ensemble, ils permettent d'analyser les informations contenues dans les feuilles de calcul. Examinons chaque fonction séparément, puis regroupons-les. 

Qu'est-ce que VLOOKUP() dans Excel ?

VLOOKUP() recherche une valeur spécifique dans la première colonne d'une plage et renvoie une valeur d'une autre colonne de la même ligne. Voici la syntaxe qui vous permettra d'utiliser la formule VLOOKUP() pour vos grands ensembles de données.

=VLOOKUP(search_key, range, index, is_sorted)

Décortiquons cette syntaxe et comprenons-la :

• Search_key est la valeur que vous souhaitez rechercher. Il peut s'agir d'un nombre, d'un texte ou d'une référence à une cellule contenant la valeur recherchée.

• Range définit la plage de cellules qui contient les données. La première colonne de cette plage doit contenir l'adresse search_key. 

• Index est le numéro de la colonne de l'intervalle dont vous voulez extraire la valeur. La première colonne est 1, la deuxième est 2, et ainsi de suite.

• Is_sorted est une valeur logique (TRUE ou FALSE). Vous pouvez utiliser TRUE (ou 1) pour les chiffres et FALSE (ou 0) pour le texte. Si TRUE, VLOOKUP() suppose que la première colonne de la plage est triée par ordre croissant et renvoie la correspondance la plus proche. Si FALSE, VLOOKUP() recherche une correspondance exacte. Si l'argument n'est pas spécifié, TRUE est la valeur par défaut. 

Comprenons-le à l'aide d'un exemple. J'ai ici une liste de produits avec leurs identifiants et leurs prix. Je souhaite trouver les prix de produits spécifiques en me basant sur leur nom.

Liste des produits avec leur numéro d'identification et leur prix. Source : Image par l'auteur.

Pour commencer, je cherche à connaître le prix d'une tablette en particulier. Pour ce faire, j'introduis une valeur search_key (dans mon cas, Tablet) pour trouver le prix dans n'importe quelle cellule. 

Saisissez la valeur pour en connaître le prix. Source : Image par l'auteur.

Ensuite, je sélectionne une cellule et je tape =VLOOKUP().

Tapez la formule VLOOKUP(). Source : Image par l'auteur.

Ensuite, je sélectionne la cellule dans laquelle search_key a été saisi.

Sélection de la colonne où se trouve le produit. Source : Image par l'auteur.

Ensuite, je sélectionne la plage du tableau. 

Sélection de l'étendue du tableau. Source : Image par l'auteur.

En comptant à partir de la gauche, j'introduis le numéro d'une colonne dont je veux extraire les données. Ici, je veux connaître le prix, j'entre donc 2.

Sélection de l'index des colonnes. Source : Image par l'auteur.

Ensuite, je tape FALSE pour obtenir la correspondance exacte.

Tapez FALSE pour la correspondance exacte. Source : Image par l'auteur.

Après avoir rempli toutes les valeurs, j'appuie sur Entrée :

La fonction VLOOKUP() permet d'obtenir le prix du produit. Source : Image par l'auteur.

Comme vous pouvez le voir dans l'image, VLOOKUP() récupère avec succès le prix.

Qu'est-ce que IF() dans Excel ?

IF() comparent les valeurs et les vérifient par rapport à la condition spécifiée. Voici la syntaxe :

=IF(logical_test, [value_if_true], [value_if_false])

Examinons les éléments clés pour le comprendre : 

• Logical_test est la valeur ou l'expression que vous voulez évaluer comme TRUE ou FALSE. C'est la condition que vous voulez vérifier.

• Value_if_true renvoie la valeur si logical_test est TRUE.

• Value_if_false renvoie la valeur si logical_test est FALSE.

Prenons un exemple. Ici, je veux donner des remarques aux étudiants en fonction de leurs notes. Je prépare donc la feuille avec deux colonnes : ÉTUDIANTS et GRADES. 

Une feuille contenant la liste des étudiants avec leurs notes. Source : Image par l'auteur.

Je sélectionne une cellule et je tape =IF(). Mon objectif est de vérifier si le total des nombres est supérieur à 50 et d'imprimer Excellent, ou, si la note est inférieure à 50, d'imprimer Bad. Après avoir spécifié les conditions, j'appuie sur Entrée pour obtenir les résultats.

Attribution de remarques à tous les élèves à l'aide de l'instruction IF. Source : Image par l'auteur.

Ensuite, je copie la formule dans la dernière cellule remplie en la faisant glisser. Vous pouvez voir les résultats. J'ai attribué des remarques à tous les étudiants avec une seule formule. 

Comment combiner VLOOKUP() avec IF() dans Excel

Prenons des exemples pratiques pour comprendre comment VLOOKUP() fonctionne avec IF().

Consultations conditionnelles

Pour créer une consultation conditionnelle, comme nous l'avons dit, commencez par une instruction IF() et utilisez VLOOKUP() à l'intérieur de celle-ci pour renvoyer différents résultats en fonction d'une condition.

1. Commencez par la fonction IF(): =IF().

2. À l'intérieur de la fonction IF(), utilisez VLOOKUP().

Prenons un nouvel exemple : J'ai ici une liste de commandes de produits avec les temps de commande correspondants. Je veux savoir si un produit spécifique a été commandé avant 12h00.

Un tableau contenant la liste des produits ainsi que l'identifiant et l'heure de la commande. Source : Image par l'auteur.

Tout d'abord, je sélectionne une colonne et j'introduis la formule suivante : 

=IF(VLOOKUP(A3, A2:C5,3, FALSE) < TIME(12, 0, 0), "Ordered Before Noon", "Ordered After Noon")

Ici, la fonction VLOOKUP() recherche Banana dans la colonne A et renvoie l'heure de commande correspondante dans la colonne B.

L'instruction IF() vérifie si le temps de commande est inférieur à 12. Si c'est le cas, il renvoie Ordered Before Noon. Dans le cas contraire, il renvoie Ordered After Noon.

Calcul du délai de livraison en combinant les formules IF() et VLOOKUP(). Source : Image par l'auteur.

Vous pouvez voir comment je peux facilement suivre les livraisons des produits souhaités à l'aide de listes de contrôle conditionnelles.

Gestion des erreurs

Pour gérer les erreurs, commencez par une déclaration IF() et utilisez ISNA() avec VLOOKUP() à l'intérieur pour vérifier les erreurs. Par exemple, pour afficher un message personnalisé si un produit n'est pas trouvé :

1. Commencez par la fonction IF(): =IF().

2. Dans la fonction IF(), utilisez ISNA() avec VLOOKUP() pour vérifier les erreurs.

Illustrons cela par un exemple. Supposons que je dispose d'un tableau contenant les prix des produits et que je souhaite afficher un message personnalisé lorsqu'un produit n'est pas trouvé. 

Un tableau contenant une liste de produits et leurs prix. Source : Image de l'auteur.

Pour ce faire, je sélectionne une cellule et j'introduis la formule suivante pour trouver le prix :

=(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)

Ensuite, je le combine avec ISNA() et IF() pour traiter les éventuelles erreurs.

=IF(ISNA(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)), "Product Not Found", (VLOOKUP(B7, $A$2:$B$5, 2, FALSE)))

Traitement des erreurs en combinant les formules IF() et ISNA(). Source : Image de l'auteur.

Ici, ISNA() vérifie si la fonction VLOOKUP() renvoie une erreur #N/A, ce qui se produirait si elle n'était pas disponible. En d'autres termes, si ISNA() est égal à TRUE, l'instruction IF() renvoie Product Not Found; dans le cas contraire, elle renvoie le prix de l'instruction VLOOKUP().

Indexation dynamique des colonnes

Pour choisir dynamiquement l'index de colonne pour VLOOKUP(), commencez par une instruction IF() et utilisez VLOOKUP() à l'intérieur de celle-ci pour sélectionner différentes colonnes en fonction d'une condition. Par exemple, pour consulter différentes colonnes en fonction d'une valeur seuil :

1. Commencez par la fonction IF(): =IF().

2. Dans la fonction IF(), utilisez VLOOKUP() pour vérifier le seuil et sélectionner la colonne.

Ici, j'ai un tableau de produits dont la colonne A contient les noms des produits, la colonne B contient les prix des produits et la colonne C contient la quantité en stock. Je souhaite consulter le prix du produit ou la quantité en stock selon que le prix est supérieur ou inférieur à un certain seuil, par exemple 50.

Un tableau contenant une liste de produits, leurs identifiants et leurs prix. Source : Image par l'auteur.

Je sélectionne une cellule et j'introduis la formule suivante :

=IF(VLOOKUP(B9, $B$9:$D$14, 2, FALSE) > 50, VLOOKUP(B9,$B$9:$D$14, 3, FALSE), VLOOKUP(B9,$B$9:$D$14, 2, FALSE))

Application de IF avec VLOOKUP() imbriqué. Source : Image par l'auteur.

Voici comment fonctionne la formule : 

• (VLOOKUP(B9, $B$9:$D$14, 2, FALSE) recherche le produit dans B9 à partir de la colonne A et renvoie le prix à partir de la colonne C.

• La déclaration IF() est vérifiée si le prix est supérieur à $50.

• Si c'est le cas, notre code renvoie la quantité en stock : VLOOKUP(B9,$B$9:$D$14, 3, FALSE).

• S'il est faux, notre code renvoie l'identifiant du produit : VLOOKUP(B9,$B$9:$D$14, 2, FALSE)).

Si vous travaillez avec un très grand ensemble de données, vous pouvez copier la formule en la faisant glisser vers la dernière cellule remplie.

Afficher les résultats. Source : Image par l'auteur.

Et c'est tout. Comme vous pouvez le constater, nous pouvons récupérer les informations souhaitées en fonction de conditions spécifiques à l'aide d'une formule combinée.

Techniques avancées utilisant VLOOKUP() et IF()

Maintenant que vous avez acquis une compréhension de base de la combinaison des déclarations IF() avec VLOOKUP(), nous allons apprendre quelques techniques avancées à partir d'exemples que j'ai essayés par moi-même.

Combinaison de plusieurs critères

Si vous recherchez des données en fonction de plusieurs critères, vous pouvez combiner plusieurs fonctions VLOOKUP() dans une instruction IF() pour vérifier si toutes les conditions sont remplies. 

J'ai ici un tableau contenant les données d'achat des clients et leur statut d'adhérent. Et je veux vérifier si un client était éligible à un programme de fidélité, ce qui nécessite au moins 500 dans l'achat total ($) et Gold dans l'état d'adhésion.

Un tableau contenant les coordonnées des clients. Source : Image par l'auteur.

Je crée une autre colonne nommée Éligibilité, qui indique les critères d'éligibilité. Ensuite, je tape la formule suivante et j'appuie sur Entrée : 

=IF(AND(VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500, VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold"), "Eligible", "Not Eligible")

Vérifiez les critères d'éligibilité des clients en combinant plusieurs critères. Source : Image par l'auteur.

Voici comment fonctionne cette formule : 

• VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500 vérifie si le total des achats du client est au moins égal à $500.

• VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold") vérifie si le statut de membre de John est Gold.

• AND a combiné les conditions pour s'assurer que les deux sont vraies.

• IF() renvoie Eligible lorsque les deux conditions sont remplies. Dans le cas contraire, il renvoie Not Eligible.

Utilisation de VLOOKUP() avec IF() pour les calculs

Vous pouvez utiliser VLOOKUP() pour trouver une valeur, puis appliquer une instruction IF() pour effectuer des calculs basés sur cette valeur. 

Ici, je prépare une feuille avec les produits et leurs prix. Je souhaite ensuite appliquer une réduction de 10 % aux produits d'une valeur supérieure à 100 $.

Un tableau contenant une liste de produits avec leurs prix. Source : Image par l'auteur.

Je crée donc une autre colonne nommée DISCOUNT et j'écris la formule suivante dans cette colonne pour afficher la réduction.

=IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9, VLOOKUP(A2, $A$2:$B$10, 2, FALSE))

Voici comment fonctionne cette formule :

• VLOOKUP(A2, $A$2:$B$10, 2, FALSE) a récupéré le prix du produit qui est de 56.

• =IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, ..., ...) vérifié si le prix du produit est supérieur à 100. 

• Comme la condition est vraie, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9 a appliqué une réduction de 10 %. 

• (VLOOKUP(A2, $A$2:$B$10, 2, FALSE)) a imprimé les prix réels des produits pour lesquels la condition de réduction n'était pas vraie.

C'est ainsi que j'ai obtenu le résultat final de la formule du produit, à savoir 135. Pour obtenir les résultats souhaités pour tous les produits, vous pouvez faire glisser la formule vers le bas et la copier dans la dernière cellule remplie.

L'utilisation de VLOOKUP() avec IF permet de calculer les remises sur les produits dont les prix sont supérieurs à 100. Source : Image par l'auteur.

Traitement de grands ensembles de données

Vous pouvez également combiner VLOOKUP() avec la fonction IF() pour rationaliser la recherche de données et la gestion des erreurs lorsque vous travaillez avec d'énormes ensembles de données.

Voici notre dernier exemple : J'ai une feuille contenant des informations sur les employés et je souhaite récupérer le département d'un employé et traiter les cas où l'identifiant de l'employé n'existe pas.

Un tableau contenant une liste d'employés avec leur ID et leur département. Source : Image par l'auteur.

J'ai donc sélectionné une cellule et j'ai tapé la formule suivante : 

=IFERROR(VLOOKUP(E3, $A$2:$C$18, 3, FALSE), "Not Found")

Après avoir appuyé sur Entrée, je fais glisser la formule pour la copier également dans d'autres cellules.

Récupération du département du salarié par l'intermédiaire de l'ID et traitement des erreurs si le salarié n'est pas trouvé. Source : Image par l'auteur.

Vous pouvez voir les résultats ci-dessus. Voici comment fonctionne cette formule : 

• Le site VLOOKUP(E3, $A$2:$C$18, 3, FALSE) récupère le département de l'employé.

• IFERROR gère les erreurs. Au lieu d'afficher un message d'erreur (#N/A), il affichera un message amical et personnalisé.

En d'autres termes, vous n'avez plus besoin de vous plonger dans les feuilles. En effet, lorsque vous combinez les déclarations VLOOKUP() et IF(), vous pouvez facilement traiter les ensembles de données les plus volumineux dans Excel. 

Réflexions finales

En combinant VLOOKUP() avec les déclarations IF(), vous pouvez créer des feuilles de calcul plus précises et plus résistantes aux erreurs. Ne manquez pas d'expérimenter les exemples que j'ai partagés pour voir comment ces techniques peuvent simplifier les tâches de gestion des données et améliorer vos compétences en Excel.

Si vous souhaitez améliorer vos compétences, consultez notre cours Introduction à Excel, puis passez au cursus Excel Fundamentals pour maîtriser les bases. Une fois que vous serez à l'aise avec ces fonctions, vous pourrez améliorer vos capacités d'analyse grâce à notre cours sur l 'analyse des données dans Excel. Mais si vous êtes plutôt du côté de la finance, consultez notre cours Modélisation financière dans Excel pour intégrer des informations financières. 

En outre, notre cours Préparation des données dans Excel vous aidera à rationaliser votre processus de nettoyage des données, et le cours Visualisation des données dans Excel vous aidera à présenter vos données de manière convaincante.