Comment utiliser la fonction VLOOKUP pour plusieurs critères dans Google Sheets (étapes faciles)

Lorsque vous devez vous référer à des données provenant de plusieurs feuilles ou tables, les chercher physiquement peut être fastidieux. Google Sheets vous aide à surmonter ce problème avec sa fonction VLOOKUP.

En utilisant la fonction VLOOKUP, ou recherche verticale, vous pouvez demander à Google Sheets de rechercher automatiquement des valeurs et de récupérer des données correspondantes dans une autre table. Cette table peut être sur la même feuille ou sur une feuille différente.

Le seul problème est que la syntaxe de la fonction VLOOKUP vous permet uniquement de rechercher une colonne à la fois.

Donc, si vous avez plusieurs critères ou si vous souhaitez vérifier plusieurs colonnes interdépendantes en même temps, vous devez contourner cette limitation.

Cela peut être fait en associant la fonction VLOOKUP à d’autres fonctions de Google Sheets, même la fonction IMPORTRANGE.

Syntaxe d’une fonction VLOOKUP régulière

En général, une fonction VLOOKUP a la syntaxe suivante:

=VLOOKUP(critère_de_recherche, plage, index, [trié])

Ici,

• critère_de_recherche est la valeur clé que vous souhaitez rechercher dans une autre table. Il peut s’agir soit d’une valeur, soit d’une référence à une cellule contenant la valeur.
• plage est la plage de cellules (dans la table source) dans laquelle vous souhaitez que la fonction VLOOKUP recherche le critère_de_recherche. Assurez-vous de bien vérifier que cette plage contient la colonne avec le critère_de_recherche en tant que première colonne. Assurez-vous également qu’elle contient la colonne avec la valeur cible que vous souhaitez récupérer.
• index est le numéro de colonne dans la plage contenant la valeur cible que vous souhaitez récupérer. Notez que la première colonne dans la plage a un index de 1, la deuxième colonne a un index de 2, et ainsi de suite.
• trié est un paramètre facultatif. Il peut être soit VRAI, soit FAUX. Il indique si la colonne de recherche doit être triée ou non.

Par exemple, dans l’image suivante, nous avons deux tables. L’une contient des informations personnelles sur les employés et l’autre contient leurs ventes totales pour un mois donné.

Les deux tables ont une colonne commune, l’identifiant de l’employé, qui peut être considéré comme une clé ou un identifiant unique dans les deux tables.

Pour afficher le taux horaire de l’employé « E010 », vous devez le récupérer dans la table des employés à l’aide de la fonction VLOOKUP. Voici ce que vous entrerez dans la cellule F3:

=VLOOKUP(A3, $A$3:$C$8, 3, false)

Ainsi, vous obtiendrez le résultat suivant:

Quand avons-nous besoin de rechercher plusieurs critères dans Google Sheets?

Le cas précédent ne concernait que la recherche d’un seul critère, une correspondance pour l’identifiant de l’employé. Cependant, la plupart du temps, le critère est plus complexe que cela.

Il peut y avoir plusieurs raisons pour lesquelles plusieurs critères sont nécessaires pour la fonction VLOOKUP. Voici quelques exemples:

1. Vous pouvez avoir des colonnes distinctes pour le prénom et le nom de famille dans la table source. Vous devrez donc rechercher les deux colonnes pour récupérer une valeur correspondante, comme indiqué ci-dessous:

2. Vous devrez peut-être vérifier la satisfaction de 2 ou plusieurs conditions pour récupérer une valeur. Par exemple, vous pourriez avoir besoin de la somme des scores uniquement des étudiants qui ont réussi un test et étudié le français, comme indiqué ci-dessous:

3. Vous pouvez avoir besoin de rechercher une table distincte pour trouver le salaire d’un employé dans un certain département avec un certain code régional.

Il existe de nombreuses autres situations similaires aux exemples ci-dessus. Mais vous avez compris l’idée générale.

Vous pourriez dire que l’utilisation d’un FILTRE serait une option plus simple pour les situations ci-dessus. Cependant, le FILTRE ne peut pas récupérer de données provenant d’une feuille différente.

Utiliser des fonctions SI serait également trop complexe car vous devriez alors traiter avec des fonctions SI imbriquées (et personne n’aime ça!).

Comment rechercher plusieurs critères dans Google Sheets

Prenons l’exemple suivant, où vous avez une table contenant la prime correspondante à chaque département et à chaque code régional.

Dans le tableau 2, nous devons rechercher la prime correspondant à un département particulier dans un code régional particulier et afficher la valeur récupérée dans la colonne Prime (colonne E).

Il existe deux façons de réaliser cela en utilisant la fonction VLOOKUP:

1. Utilisation d’une colonne auxiliaire
2. Utilisation de la fonction ARRAYFORMULA

Vous pouvez en savoir plus ici

Utilisation d’une colonne auxiliaire pour rechercher plusieurs critères dans Google Sheets

La première méthode consiste à utiliser une colonne auxiliaire supplémentaire, qui contiendra une combinaison des cellules des critères.

Dans notre exemple, nous pouvons insérer la colonne auxiliaire juste avant la colonne du département, de sorte qu’elle puisse être la première colonne de la plage de recherche.

La colonne auxiliaire peut contenir une combinaison du département et du code régional pour chaque ligne, séparés par un espace.

Voyons les étapes pour créer et utiliser cette colonne auxiliaire avec VLOOKUP pour obtenir la valeur de prime appropriée pour le tableau 2:

1. Insérez une nouvelle colonne juste avant la première colonne du tableau 1. Cette colonne agira comme notre colonne auxiliaire. Pour cela, vous devez cliquer avec le bouton droit sur l’en-tête de colonne de la première colonne (colonne A) et sélectionner l’option « Insérer 1 à gauche » dans le menu contextuel qui apparaît.

2. Sélectionnez la première cellule de la colonne nouvellement créée (cellule A4) et tapez la formule: =B4&" "&C4.

3. Appuyez sur la touche Entrée. La cellule A4 doit maintenant contenir le contenu de la cellule B4 suivi de l’espace et du contenu de la cellule C5.

4. Double-cliquez sur le coin de remplissage de la cellule A4 pour copier la formule sur le reste des cellules de la colonne A. Voici à quoi devrait ressembler la colonne A à ce stade:

5. Maintenant que la colonne auxiliaire est prête, nous pouvons utiliser la fonction VLOOKUP. Vous remarquerez que l’ajout d’une nouvelle colonne a décalé les contenus du tableau 2 d’une cellule vers la droite. Si vous le souhaitez, vous pouvez sélectionner les contenus et les déplacer d’une cellule vers la gauche.

6. Ensuite, sélectionnez la cellule E17 (colonne Prime du tableau 2) et tapez la formule: =VLOOKUP(B17&" "&C17, $A$4:$D$12, 4, false)

7. Appuyez sur la touche Entrée.

8. Vous devriez maintenant voir la valeur de prime correspondant à « Operations BH12 » du tableau 1.

9. Double-cliquez sur le coin de remplissage de la cellule E17 pour copier la formule sur le reste des cellules de la colonne A.

10. Vous devriez maintenant voir toutes les valeurs de prime correspondant à chaque département et à chaque code régional dans le tableau 2.

Remarque: Lorsque vous tapez la formule VLOOKUP, n’oubliez pas de verrouiller les références du deuxième paramètre en appuyant sur la touche F4. Cela garantira que la plage de recherche ne se décale pas lorsque la formule est copiée sur le reste des cellules.

Explication de la formule

Démontons la formule VLOOKUP pour comprendre chaque paramètre utilisé:

=VLOOKUP(B17&" "&C17, $A$4:$D$12, 4, false)

• B17&" "&C17: La valeur de recherche que nous avons utilisée ici est une combinaison des valeurs du département et du code régional que nous voulons rechercher, séparées par un espace (qui est exactement le format que nous avons utilisé dans notre colonne auxiliaire).
• $A$4:$D$12: Nous savons que notre plage de recherche doit toujours avoir la colonne de recherche comme première colonne. La plage de recherche commence maintenant à partir de A4 jusqu’à D12, car A4 est la première cellule de notre colonne auxiliaire.
• 4: En raison de l’ajout d’une colonne supplémentaire à gauche, notre colonne cible a maintenant été décalée d’une cellule vers la droite. Par conséquent, la colonne de prime est maintenant à l’index 4 de notre plage de recherche.
• false: Une valeur FALSE pour ce paramètre indique que la première colonne de la plage de recherche n’a pas besoin d’être triée par ordre croissant.

Étant donné que le critère_de_recherche et la colonne auxiliaire sont exactement dans le même format (département suivi d’un espace, suivi du code régional), la fonction VLOOKUP peut facilement rechercher la prime correspondante et la renvoyer.

Utilisation de la fonction ARRAYFORMULA pour rechercher plusieurs critères dans Google Sheets

Cette méthode fait plus ou moins la même chose que la première méthode. La seule différence est que cette fois, la colonne auxiliaire est créée dynamiquement, plutôt que de devoir créer physiquement une colonne supplémentaire pour elle.

La méthode utilise la fonction ARRAYFORMULA pour créer une table ‘virtuelle’ contenant les colonnes suivantes:

• Une colonne contenant une combinaison de cellules des critères.
• La colonne cible de la plage de recherche.

Appliquons cela à notre cas. Voici les étapes pour créer et utiliser la fonction ARRAYFORMULA avec VLOOKUP pour obtenir la valeur de prime appropriée pour le tableau 2:

1. Sélectionnez la cellule E17 (colonne Prime du tableau 2) et tapez la formule: =ARRAYFORMULA(VLOOKUP(B17&" "&C17, {$A$4:$A$12&" "&$B$4:$B$12, $C$4:$C$12}, 2, false))

2. Appuyez sur la touche Entrée.

3. Vous devriez maintenant voir la valeur de prime correspondant au département : Operations et au code régional : BH12 du tableau 1.

4. Double-cliquez sur le coin de remplissage de la cellule E17 pour copier la formule sur le reste des cellules de la colonne A.

5. Vous devriez maintenant voir toutes les valeurs de prime correspondant à chaque département et à chaque code régional dans le tableau 2.

Remarque: Lorsque vous tapez la formule VLOOKUP, n’oubliez pas de verrouiller les références du deuxième paramètre en appuyant sur la touche F4. Cela garantira que la plage de recherche ne se décale pas lorsque la formule est copiée sur le reste des cellules.

Explication de la formule

Démontons la formule que nous avons utilisée pour comprendre ce qui s’est exactement passé:

=ARRAYFORMULA(VLOOKUP(B17&" "&C17, {$A$4:$A$12&" "&$B$4:$B$12, $C$4:$D$12}, 2, false))

• Tout d’abord, nous utilisons ARRAYFORMULA pour créer une sorte de table virtuelle. Cette table virtuelle doit contenir les colonnes suivantes:

  • Une colonne contenant une combinaison des valeurs des cellules $A$4:$A$12 et $B$4:$B$12, séparées par un espace entre chaque valeur de cellule : $A$2:$A$9&" "&$B$2:$B$9
  • Une colonne contenant les valeurs de la colonne Prime du tableau 1 : $C$2:$D$9

• Cela est spécifié entre des accolades car nous voulons renvoyer un tableau ou une table virtuelle de cellules : { $A$2:$A$9&" "&$B$2:$B$9, $C$2:$D$9 }

Notez que nous avons utilisé la virgule comme séparateur pour la formule en matrice car nous voulons traiter les parties comme des colonnes les unes à côté des autres, comme indiqué ci-dessous :

• Si nous voulions les avoir en tant que lignes les unes au-dessus des autres, nous aurions utilisé un point-virgule à la place de la virgule.

• Ensuite, nous appliquons la fonction VLOOKUP en utilisant la formule en matrice ci-dessus comme deuxième paramètre. Le reste des paramètres sont les mêmes que précédemment :

  • critère_de_recherche: La clé de recherche est à nouveau une combinaison des valeurs de département et de code régional que nous voulons rechercher, séparées par un espace.
  • plage: La plage est maintenant le tableau ou la table virtuelle : {$A$2:$A$9&" "&$B$2:$B$9, $C$2:$D$9}
  • index: Comme la table virtuelle créée ne comporte que deux colonnes, où Prime est la deuxième colonne, nous utilisons 2 comme valeur d’index.
  • trié: Une valeur FALSE pour ce paramètre indique que la première colonne de la plage de recherche n’a pas besoin d’être triée par ordre croissant.

Étant donné que le critère_de_recherche et la première colonne du tableau renvoyé dans la plage sont exactement dans le même format (département suivi d’un espace, suivi du code régional), la fonction VLOOKUP peut facilement rechercher la prime correspondante et la renvoyer.

C’était juste un bref aperçu de deux façons d’utiliser la fonction VLOOKUP lorsque plusieurs critères doivent être pris en compte.

Nous vous encourageons à essayer les exemples que nous avons étudiés dans ce tutoriel par vous-même. Cela vous aidera à comprendre comment fonctionnent les formules et vous donnera une meilleure compréhension.

Nous espérons que vous avez trouvé ce tutoriel utile !