Si vous utilisez Vlookup, alors vous devez connaître l’utilisation de la colonne dynamique dans la combinaison Vlookup Importrange dans Google Sheets.
La principale raison est que vous pouvez faire Vlookup d’un fichier Google Sheets externe à l’aide de la fonction Importrange de Google Sheets.
Dans cette utilisation, la sortie Importrange agit en tant que « range » pour Vlookup. Vlookup accepte cette combinaison sans erreur.
Qu’est-ce qu’une colonne dynamique ?
Dans Vlookup, vous devez fournir un numéro de colonne pour renvoyer une valeur ou des valeurs. Ce numéro est appelé « index ».
Supposons que vous ayez une liste de prix de produits à deux colonnes, dans laquelle la colonne A contient les noms des produits et la colonne B contient leur prix.
Si vous souhaitez obtenir le prix d’un produit en recherchant dans la colonne A, vous devez utiliser la colonne 2 en tant que colonne d’index dans Vlookup.
Si la liste des produits se trouve dans D1:E (colonne D et E) au lieu de A1:B comme ci-dessus (colonne A et B), alors encore une fois la colonne d’index sera 2. Parce que les colonnes sont comptées à partir de la première colonne dans la plage de données.
Il est conseillé de rendre ce numéro de colonne dynamique. Je veux dire, ajustez automatiquement le numéro de colonne lorsque vous insérez de nouvelles colonnes dans votre plage de données. Voici un exemple.
La formule Vlookup (Vlookup) verticale normale dans la cellule D2 (utilisant le numéro de colonne index statique) :
=vlookup("Banana",A1:B,2,false)
La formule de recherche verticale de colonne dynamique (Vlookup) dans la cellule D3 (en utilisant un numéro de colonne index dynamique) :
=vlookup("Banana",A1:B,column(B1),false)
J’ai déjà expliqué cela dans mon tutoriel – Colonne d’index dynamique dans Vlookup dans Google Sheets.
Avant de continuer à lire, je vous suggère de lire mon tutoriel – Comment utiliser Vlookup Importrange dans Google Sheets [Exemples de formules]. Cette publication contient quelques astuces géniales.
Pourquoi une colonne d’index dynamique dans Vlookup Importrange est-elle essentielle
Supposons que vous ayez utilisé les données Importrange, c’est-à-dire les données d’un fichier Google Sheets externe, en tant que plage dans Vlookup. Le problème mentionné ci-dessus de la colonne d’index peut également se poser ici.
Il y a plus de chances d’oublier de mettre à jour le numéro d’index dans Vlookup lorsque vous apportez des modifications dans le fichier source (externe).
Donc, pour créer une formule Vlookup + Importrange « configurer et oublier », cette approche de la colonne d’index dynamique est essentielle.
Comment faire référence à une colonne dynamique dans Vlookup Importrange dans Google Sheets
J’ai deux options pour le moment. Choisissez celle qui vous semble la plus pratique à suivre. Voyez cela sous les deux sous-titres différents ci-dessous.
Je vais faire de mon mieux pour rendre les choses plus faciles à comprendre.
Données d’exemple pour expliquer l’utilisation du concept de colonne d’index dynamique dans la formule Vlookup Importrange dans Google Sheets :
Noms définis en tant que colonne d’index Vlookup dans Google Sheets (Option 1)
Voyons les instructions étape par étape ci-dessous.
Paramètres dans le fichier source (FILE_A)
Étape n° 1 :
Les données d’exemple ci-dessus se trouvent dans le fichier source (appelez-le FILE_A) « Sheet1 ». Dans ce fichier, ajoutez un nouvel onglet « Sheet2 ».
Par la suite, j’importerai les données de « Sheet1 » vers un autre fichier Google Sheets (appelez-le FILE_B) et je ferai Vlookup sur le prix dans la colonne B de la source.
Cela signifie que la colonne 2 est notre colonne d’index. Je vais d’abord rendre le numéro de colonne dynamique.
Étape n° 2 :
Dans FILE_A, j’ai déjà inséré un nouvel onglet « Sheet2 ». Dans la cellule A1 de cet onglet, entrez la formule suivante.
=column(Sheet1!B1)
Il s’agit d’un onglet d’aide et d’une cellule d’aide. Nous ne voulons donc pas d’autres lignes ou colonnes dans cette feuille.
Si vous le souhaitez, vous pouvez supprimer toutes les autres lignes et colonnes de l’onglet « Sheet2 ». Supprimez toujours les lignes et colonnes inutiles dans Google Sheets pour améliorer les performances de Sheets.
Étape n° 3 :
Sélectionnez la cellule contenant la formule de colonne ci-dessus. Ensuite, allez dans le menu DONNÉES> NOMMÉES. Donnez le nom de la cellule « price ».
Paramètres dans le fichier de sortie (FILE_B)
Il est temps d’utiliser Importrange dans FILE_B.
Étape n° 1 :
Dans la cellule A1 de FILE_B, entrez l’URL de FILE_A. Puis dans la cellule A2, entrez la plage de données source Sheet1!A1:1000 et non pas Sheet1!A1:B. Pourquoi ? Parce que nous voulons utiliser des colonnes infinies. C’est alors seulement que l’ID de colonne dynamique a un effet.
Enfin, dans la cellule A3, tapez le nom de la plage nommée, c’est-à-dire « price ».
Si vous avez un doute, veuillez vérifier la capture d’écran ci-dessous.
Étape n° 2 :
Dans la cellule B4, entrez votre clé de recherche (nom du produit).
Étape n° 3 :
Maintenant, à cette étape, laissez-moi vous montrer comment obtenir une colonne dynamique dans la formule Vlookup Importrange dans Google Sheets.
Formule :
=vlookup(B4,importrange(A1,A2),importrange(A1,A3),false)
Comme d’habitude, si vous voyez l’erreur #REF!, cela signifie que vous n’avez pas autorisé l’accès à Importrange.
Dans ce cas, utilisez d’abord l’une des formules Importrange indépendamment et donnez l’autorisation d’accès. Vous pourrez ensuite supprimer cette formule et l’utiliser dans la formule combo ci-dessus.
Pour tester la sortie, allez à FILE_A et insérez une nouvelle colonne entre A et B. Revenez à FILE_B et voyez comment la formule Vlookup Importrange ajuste automatiquement le numéro de colonne d’index.
Colonne d’index dynamique dans la formule Importrange + Vlookup Array
Nous pouvons utiliser la formule ci-dessus pour obtenir le prix de plusieurs produits en une seule fois ! Voyez la formule Array dans la cellule C4, qui renvoie un résultat en tableau.
=ArrayFormula(vlookup(B4:B6,importrange(A1,A2),importrange(A1,A3),false))
Formule Match en tant que colonne d’index Vlookup dans Google Sheets (Option 2)
Il s’agit d’une autre approche pour entrer une colonne dynamique dans la formule Vlookup Importrange. Cela est basé sur la fonction Match en tant que colonne d’index. Dans ce cas, dans FILE_A, il n’est pas nécessaire de créer l’onglet d’aide « Sheet2 ».
Préparez votre FILE_B comme suit. J’ai donné des astuces utiles sur la capture d’écran elle-même.
Il s’agit de la formule Vlookup non-array + Match + Importrange. Dans cette formule, la formule Match renvoie le numéro de colonne dynamique, c’est-à-dire le numéro de colonne qui se déplace/change.
=vlookup(B4,importrange(A1,A2),match(A4,importrange(A1,A3),0),false)
Dans le cas d’une utilisation de tableau (recherche de plusieurs clés), la formule sera la suivante. En supposant que les clés de recherche se trouvent dans la plage B4:B6.
=ArrayFormula(vlookup(B4:B6,importrange(A1,A2),match(A4,importrange(A1,A3),0),false))
Ici encore, j’ai utilisé la formule Match pour agir en tant qu’ID de colonne dynamique dans Vlookup + Importrange.
Comment ?
La formule Match utilise le libellé du champ de la colonne d’index en tant que clé de recherche et renvoie sa position dans la plage. Lorsque cette colonne se déplace, le Match renverra le bon numéro de colonne.
C’est tout. Si vous avez des doutes, vous pouvez me les poser dans les commentaires. Profitez-en !
Ressources supplémentaires :
- Comment figer une cellule dans Importrange dans Google Sheets.
- Comment utiliser la fonction IMPORTRANGE avec des conditions dans Google Sheets.
- Noms de feuilles dynamiques dans Importrange dans Google Sheets.
- Comment utiliser Query avec Importrange dans Google Sheets.
- Colonne d’index dynamique dans Query Importrange en utilisant les plages nommées.
