Bien que les termes « Vlookup » et « comma-separated values » peuvent sembler vagues au premier abord, laissez-moi vous éclaircir avant de commencer cet article.
Dans ce tutoriel Google Sheets dédié aux astuces Vlookup, je vais répondre aux deux questions suivantes concernant Vlookup :
- Comment utiliser Vlookup dans une colonne (liste) de valeurs séparées par des virgules dans Google Sheets ?
- Comment utiliser des clés de recherche séparées par des virgules dans Vlookup ?
Comme je vais aborder ces deux problèmes liés à Vlookup, j’ai choisi ce titre.
Commençons par la première astuce Vlookup pour Google Sheets.
Vlookup dans une colonne de valeurs séparées par des virgules dans Google Sheets
Utiliser la fonction Vlookup dans une colonne de valeurs séparées par des virgules est assez simple dans Google Sheets.
Voici comment faire.
Il existe différentes approches avec Vlookup, et je vais vous présenter deux méthodes simples.
La deuxième méthode est destinée à ceux qui veulent une recherche sensible à la casse.
Utilisation du caractère générique d’astérisque avec la clé de recherche Vlookup
Il s’agit de la méthode la plus couramment utilisée pour Vlookup dans une colonne de valeurs séparées par des virgules dans Google Sheets.
Cela se fait en utilisant le caractère générique d’astérisque (*).
Voici comment procéder :
Supposons que la clé de recherche que vous souhaitez trouver dans la première colonne de la liste séparée par des virgules soit « produit 11 ».
Si vous prévoyez d’utiliser la clé de recherche dans la fonction Vlookup, utilisez-la comme « produit 11« .
Syntaxe :
VLOOKUP(clé_de_recherche, plage, index, [trié])
clé_de_recherche: "*produit 11*"
plage: B2:C8 (dans cette plage, B2:B8 contient la liste séparée par des virgules, voir l'image ci-dessous)
Formule 1:
=vlookup("*produit 11*",B2:C8,2,0)
Formule 2:
Sinon, si la clé de recherche « produit 11 » est dans une cellule, par exemple dans la cellule E2, utilisez « « &E2& »« .
=vlookup("*"&E2&"*",B2:C8,2,0)
Note:
Lorsque vous utilisez des caractères génériques comme indiqué ci-dessus pour rechercher des valeurs séparées par des virgules dans Google Sheets, vous pouvez rencontrer un problème.
Imaginez que la valeur en B2 soit « produit 10, produit 11, produit 12 » et B8 soit « produit 1, produit 3 ».
La clé de recherche « produit 1 » en E2 trouvera une correspondance dans B2, et non dans B8 !
Dans ce cas, vous devez utiliser intelligemment le caractère générique. Ajoutez simplement une virgule avant la deuxième astérisque comme indiqué ci-dessous.
Formule 3:
=vlookup("*"&E2&",*",B2:C8,2,0)
Passons à la deuxième astuce Vlookup pour Google Sheets.
Utilisation de Regexmatch avec la clé de recherche Vlookup
La méthode du caractère générique est suffisante pour Vlookup dans une colonne de valeurs séparées par des virgules dans Google Sheets.
Alors pourquoi préférer la méthode regex à l’approche du caractère générique ?
Je répondrai à cette question après un exemple.
Formule 4:
=ArrayFormula(vlookup(TRUE,{regexmatch(B2:B8,E2),C2:C8},2,0))
Comme vous pouvez le voir, la clé de recherche Vlookup ici est la valeur booléenne TRUE, et non la référence de cellule E2 (voir la formule ci-dessus).
De plus, la plage n’est pas B2:C8, mais {regexmatch(B2:B8,E2),C2:C8}, où la première colonne contient des valeurs TRUE ou FALSE en raison de la regex, et non de la liste séparée par des virgules.
Cette formule est équivalente à la deuxième formule avec des caractères génériques.
Considérez cette méthode pour une recherche Vlookup sensible à la casse dans une colonne de valeurs séparées par des virgules.
Par exemple, cela trouvera une correspondance avec « produit 11 », mais pas avec « PRODUCT 11 » ou « Product 11 ». J’espère que vous avez compris.
Il est facile de rendre cette recherche Vlookup insensible à la casse ! Voici comment faire.
Formule 5:
=ArrayFormula(vlookup(TRUE,{regexmatch(lower(B2:B8),lower(E2)),C2:C8},2,0))
Passons maintenant à la recherche de clés séparées par des virgules dans Vlookup dans Google Sheets.
Clés de recherche séparées par des virgules dans Vlookup dans Google Sheets
Abordons un scénario assez différent !
Dans ce cas, il n’y a pas de colonnes, en particulier la première colonne, dans la plage de recherche Vlookup contenant des valeurs séparées par des virgules.
À la place, les clés de recherche sont séparées par des virgules ou délimitées.
Voyons cet exemple de données avec des clés de recherche Vlookup séparées par des virgules dans la cellule E2.
Ici, je veux soit retourner les valeurs 5 et 25 dans deux cellules (dans F2:G2 ou F2:F3), soit le total 30 dans F2.
Vous ne comprenez pas ?
Les clés de recherche séparées par des virgules à utiliser dans Vlookup sont « produit 1 » et « produit 11 ».
Ces deux clés seront disponibles dans la recherche Vlookup dans la cellule B2 et B12 dans la plage B2:C12.
Les valeurs correspondantes à renvoyer de la colonne 2 sont 5 et 25.
Formule Vlookup sans somme :
=ArrayFormula(IFNA(vlookup(trim(split(E2,",")),B2:C12,2,0)))
Formule Vlookup avec somme :
=ArrayFormula(SUM(IFNA(vlookup(trim(split(E2,",")),B2:C12,2,0))))
Explication des formules
Par défaut, Vlookup considérera les clés de recherche séparées par des virgules comme un tout et risque de ne pas trouver de correspondance.
Nous devons donc diviser et supprimer les espaces des clés et effectuer la recherche. Voici les étapes :
Split et Trim des clés de recherche Vlookup
-
Split :
=split(E2,",") -
Split et Trim :
=ArrayFormula(trim(split(E2,",")))
J’ai inclus IFNA pour supprimer les valeurs d’erreur qui ne correspondent pas (#NA!) et SUM pour additionner la sortie de Vlookup.
J’espère que vous avez apprécié ces deux astuces Vlookup dans Google Sheets.
