Vous cherchez un moyen d’utiliser Vlookup et Offset avec plusieurs critères dans Google Sheets ? Ne cherchez pas plus loin ! Dans cet article, nous allons vous montrer comment utiliser une formule matricielle pour atteindre cet objectif.
La puissance des formules matricielles dans Google Sheets
Une des raisons pour lesquelles je choisis Google Sheets comme application de tableur est la flexibilité offerte par les formules matricielles. Cette formule que je vais vous présenter en est un exemple.
De plus, Google Sheets est également plus performant en ligne que la version en ligne de son concurrent. Malgré le fait que Google Sheets soit une application basée sur le cloud, il est plus rapide que la version en ligne de son concurrent.
Exemple de Vlookup et Offset avec plusieurs critères
Commençons par des données d’exemple. Voici un tableau que vous pouvez copier-coller dans votre feuille de calcul.
Nommez l’onglet « Item » et collez le tableau dans la plage A1:D10 de cet onglet.
Dans cet exemple, toutes les dates d’achat sont regroupées dans une colonne. Nous allons chercher tous les numéros de commande et les articles correspondants dans un autre onglet, et afficher la quantité correspondante dans des colonnes de dates séparées.
Cette solution peut également fonctionner si vous recherchez une correspondance et un décalage avec plusieurs critères dans Google Sheets.
Cependant, veuillez noter que si vous utilisez Vlookup, vous pouvez rencontrer des problèmes si vous avez des doublons dans l’onglet « Item ». La formule correspondra uniquement à la première ligne parmi les lignes doublons.
Dans l’exemple ci-dessus, toutes les valeurs sont uniques si vous les considérez par ligne, et non individuellement.
Avant de passer à la formule et aux explications, voici le résultat qu’elle renvoie. Le résultat de la formule se trouve dans l’onglet « Offset Match ».
En tant que note annexe, le premier onglet, nommé « Item », contient nos données sources.
Formule matricielle pour Vlookup et Offset avec plusieurs critères
Si vous regardez la formule pour la première fois, vous pourriez penser qu’elle est compliquée. C’est pourquoi je ne l’ai pas affichée dans l’image ci-dessus.
Commençons donc par écrire pas à pas la formule matricielle pour Vlookup et Offset avec plusieurs critères dans Google Sheets.
Il y a trois étapes impliquées :
- Vlookup avec plusieurs critères pour trouver les correspondances et retourner les quantités.
- Diviser les colonnes du résultat de Vlookup à l’aide de la fonction Index.
- Répartir/Décaler le résultat de Vlookup dans les colonnes de dates correspondantes. Pour cela, nous utiliserons une astuce qui implique la fonction SI et la fonction Séquence.
Voici les étapes pour correspondre à Vlookup et décaler avec plusieurs critères dans Google Sheets.
Étape 1 : Vlookup avec deux colonnes dans la table source
Vous pouvez tester la formule suivante dans l’onglet « Offset Match ».
=ArrayFormula( vlookup( A3:A11&B3:B11, {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10}, {2,3}, 0 ) )
La syntaxe de Vlookup est la suivante : VLOOKUP(clé_recherche, plage, index, [trié])
Les arguments utilisés dans Vlookup sont les suivants :
- clé_recherche : A3:A11&B3:B11
- plage : {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10}
- index : {2,3}
- trié : 0
Si vous ne comprenez pas cette formule, veuillez consulter mon guide associé.
Maintenant, pour effectuer un Vlookup et un décalage avec plusieurs critères dans Google Sheets, nous devons diviser le résultat obtenu ci-dessus. Pour cela, nous utiliserons la fonction Index.
Étape 2 : Diviser les colonnes du résultat de Vlookup
Sur l’image ci-dessus, vous pouvez voir les deux colonnes renvoyées par Vlookup.
La formule matricielle ArrayFormula pour Vlookup est dans la cellule C3, et les résultats sont dans la plage de colonnes C3:C11 et D3:D11.
Dans cette deuxième étape de Vlookup et de décalage avec plusieurs critères, j’utilise la fonction Index comme suit pour diviser les colonnes.
=index(step_1_formula,0,1)
Nous pouvons supprimer la formule ArrayFormula de step_1_formula lors de l’utilisation de la fonction Index. Ainsi, la formule deviendrait :
=index( vlookup( A3:A11&B3:B11, {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10}, {2,3}, 0 ), 0,1 )
La seule différence par rapport à la formule ci-dessus est le dernier nombre, qui est 2 (deuxième colonne). Dans la formule précédente, il était 1 (première colonne).
Pas besoin d’utiliser cette formule, les formules précédentes ou les formules qui suivent dans une cellule pour le moment. Testez-les et supprimez-les, car nous les utiliserons plus tard dans la fonction SI.
Maintenant, voici l’étape 3, qui est la clé et une formule astucieuse dans notre formule matricielle pour Vlookup et décalage avec plusieurs critères dans Google Sheets.
Rôle de SI et de Séquence dans Vlookup et Offset avec plusieurs critères dans Google Sheets
Tout d’abord, regardez la zone de résultat dans notre onglet « Offset Match ».
La zone de résultat de la formule est surlignée en jaune clair, et la plage est C3:AG11. Il y a neuf lignes et trente et une colonnes dans la zone de résultat.
Dans la plage C2:AG2, vous pouvez voir les chiffres de 1 à 30. Ce sont les dates du 01/11/2020 au 30/11/2020 (au format jj/mm/aaaa).
J’ai formaté les dates au format « jj » dans le menu Format > Nombre > Format personnalisé.
Si vous le souhaitez, vous pouvez conserver les dates au lieu des dates au format « jj ». Je les ai formatées pour réduire la largeur des colonnes.
Voici le rôle de la fonction Séquence dans Vlookup et Offset avec plusieurs critères dans Google Sheets.
Rôle de Séquence
Dans la cellule C3, entrez cette formule.
=ArrayFormula(sequence(9,31)^0+$C$2:$AF$2-1)
Cette formule copie les dates de la ligne d’en-tête C2:AG2 dans toutes les cellules de la plage (zone de résultat). Si vous le souhaitez, je vous expliquerai cette petite formule dans un prochain article.
Rôle de SI
Voici la dernière étape.
Nous devons combiner toutes les formules que j’ai données dans les différentes étapes ci-dessus, comme indiqué dans la formule générique ci-dessous.
=ArrayFormula( if( step_3_formula=step_2_formula_1, step_2_formula_2, ) )
Selon la formule générique ci-dessus, voici notre formule finale pour Vlookup et décalage avec plusieurs critères dans Google Sheets.
=ArrayFormula(IFNA(if(sequence(9,31)^0+$C$2:$AF$2-1=index(vlookup(A3:A11&B3:B11,{Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},{2,3},0),0,1),index(vlookup(A3:A11&B3:B11,{Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},{2,3},0),0,2),)))
Nous avons simplement ajouté IFNA pour éviter les erreurs de #N/A dans le résultat.
Vlookup et Offset avec plusieurs critères dans un grand ensemble de données (Conseils supplémentaires)
Quels sont les changements nécessaires pour étendre la formule pour couvrir plus de lignes ? Nous n’avons pas besoin de plus de colonnes, car la formule couvre déjà 31 colonnes, c’est-à-dire un mois entier.
Je vais vous expliquer comment utiliser la formule pour couvrir jusqu’à 100 lignes. Plus de lignes peuvent avoir un impact sur les performances de votre feuille ou peuvent ne pas fonctionner en raison de la limitation du signe « & » dans la formule.
Voici les changements à apporter :
- Remplacez sequence(9,31) par sequence(100,31)
- Remplacez 11 dans toutes les références de plage A3:A11 et B3:B11 dans la formule par A3:A100 et B3:B100, respectivement.
- Vous pouvez voir le numéro de ligne 10 dans les plages Item!A2:A10, Item!B2:B10, Item!C2:C10 et Item!D2:D10 dans la formule. Remplacez tous ces 10 par 100. C’est-à-dire, remplacez-les par Item!A2:A100, Item!B2:B100, Item!C2:C100 et Item!D2:D100.
Voilà tout ce qu’il y a à savoir sur Vlookup et Offset avec plusieurs critères dans Google Sheets.
SAMPLE_SHEET_181120
