Dans cet article, découvrons comment obtenir le résultat LOOKUP à partir d’une colonne dynamique dans Google Sheets.
Pourquoi utiliser la fonction LOOKUP ?
La fonction LOOKUP permet de rechercher dans une table la dernière occurrence d’une clé donnée dans une colonne spécifiée, puis d’obtenir le résultat à partir d’une autre colonne ou de la dernière colonne de cette table.
Voici un exemple :
Tableau n° 1 (plage de données A2:D9)
| Date | Acrylic Acid | Acrylamide Liquid | Acrylamide Powder |
|---|---|---|---|
| 1-janv-21 | 205.00 | 349.25 | 100.50 |
| 1-janv-21 | 122.60 | 300.85 | 200.00 |
Supposons que nous voulions rechercher la dernière occurrence de la date 1-Janv-21 dans la plage de colonnes A2:A9 et obtenir le résultat de la colonne C2:C9 (« Acrylamide Liquide »).
Pour cela, nous pouvons utiliser la formule LOOKUP suivante dans Google Sheets :
=lookup(date(2021,1,1),A2:A9,C2:C9)
Résultat : 349.25
Note : J’ai spécifié la date au format DATE(année, mois, jour) selon la fonction DATE.
Pour obtenir le résultat de la dernière colonne (D2:D9), nous pouvons utiliser la formule LOOKUP suivante :
=lookup(date(2021,1,1),A2:D9)
Résultat : 200.00
Dans la première formule, j’ai séparément spécifié la plage de recherche (A2:A9) et la plage de résultat (C2:C9). Tandis que dans la deuxième formule, j’ai utilisé l’ensemble des données.
La syntaxe de la formule 1 est LOOKUP(clef_recherche, plage_recherche, plage_résultat) et la syntaxe de la formule 2 est LOOKUP(clef_recherche, tableau_résultat).
Pour obtenir le résultat LOOKUP à partir d’une colonne dynamique dans Google Sheets, nous utiliserons la deuxième syntaxe. Mais avant cela, vous devez comprendre ce qu’est une colonne dynamique dans LOOKUP.
Que signifie le résultat LOOKUP d’une colonne dynamique ?
Une colonne dynamique dans LOOKUP signifie que le résultat sera obtenu à partir d’une colonne en fonction d’une autre recherche dans l’en-tête de la colonne.
Prenons l’exemple de l’inventaire de certains produits (tableau n° 1 ci-dessus).
Supposons que nous voulions rechercher dans la colonne A la dernière occurrence de la date 1-janv-21 dans la colonne A et obtenir la valeur des colonnes B (« Acrylic Acid »), C (« Acrylamide Liquide ») ou D (« Acrylamide Powder »). Dans ce cas, nous voulons spécifier dynamiquement les colonnes B, C ou D.
Lorsque nous changeons la valeur dans la cellule F4 en « Acrylamide Liquide », le résultat dans la cellule G4 doit être 349.25 (la valeur de la cellule C5).
C’est ce que je veux dire par obtenir le résultat LOOKUP à partir d’une colonne dynamique.
De plus, si nous changeons la date dans la cellule G1 en 2-janv-21, nous devrions obtenir 300.85, c’est-à-dire l’inventaire de l’article « Acrylamide Liquide » à cette date.
Deux formules pour obtenir le résultat LOOKUP à partir d’une colonne dynamique dans Google Sheets
Pour obtenir le résultat LOOKUP à partir d’une colonne dynamique comme indiqué ci-dessus, nous pouvons utiliser les formules QUERY ou FILTER dans LOOKUP dans Google Sheets.
Comme je l’ai mentionné précédemment, nous utiliserons la syntaxe LOOKUP(clé_recherche, tableau_résultat) à cet effet.
Dans LOOKUP, nous pouvons utiliser une expression pour contrôler le tableau de résultats de recherche. En d’autres termes, rendre le tableau de résultats de recherche dynamique.
Tableau de résultats de recherche dynamique dans LOOKUP
La clé pour rechercher la dernière valeur à partir d’une colonne dynamique dans Google Sheets consiste à utiliser une formule FILTER ou QUERY (expression) dans le tableau de résultats de recherche de LOOKUP.
La logique est la suivante :
Au lieu de spécifier A2:D9, l’ensemble des données, comme tableau de résultats de recherche, nous devrions filtrer les colonnes requises pour spécifier un tableau de résultats de recherche.
Autrement dit, nous devons utiliser A2:A9 (la colonne de dates) et filtrer une autre colonne en fonction de l’élément dans F4 (étiquette de la colonne). Cela sera le tableau de résultats de recherche à utiliser.
Les formules suivantes font cela :
Si nous utilisons Filter :
= {$A$2:$A, filter($B$2:$D, $B$2:$D$2 = $F4)}
Si nous utilisons Query :
= query({$A$2:$D}, "Select Col1,Col" & match(F4, $A$2:$D$2, 0))
Les deux formules renverront le même résultat comme indiqué ci-dessous. Vous pouvez choisir l’une ou l’autre des formules comme tableau de résultats de recherche dans LOOKUP.
Dans cette formule, la colonne de dates est statique et la colonne d’inventaire est dynamique.
Si nous changeons la valeur de F4 en « Acrylamide Powder », la formule renverra les valeurs de cette colonne au lieu de la colonne « Acrylic Acid ». La colonne de dates restera la même.
Voici deux formules principales à utiliser pour obtenir les résultats LOOKUP à partir d’une colonne dynamique dans Google Sheets.
Laissez-moi vous montrer comment les utiliser pour écrire les formules requises.
Formule pour obtenir le résultat LOOKUP à partir d’une colonne dynamique
Commençons par la formule LOOKUP avec FILTER.
La formule à utiliser dans la cellule G4 est la suivante :
=lookup($G$1, {$A$2:$A, filter($B$2:$D, $B$2:$D$2 = $F4)})
Voici l’alternative :
=lookup($G$1, query({$A$2:$D}, "Select Col1,Col" & match(F4, $A$2:$D$2, 0)))
Dans les formules ci-dessus, nous contrôlons la colonne dynamique à partir de la valeur dans la cellule F4. Voici une approche légèrement différente qui sera plus pratique dans une utilisation réelle.
Trouver l’inventaire de tous les articles
Dans l’exemple précédent, au lieu de modifier la valeur de la cellule F4, nous pouvons l’utiliser comme suit dans la plage F4:F6, puis faire glisser la formule de G4 à G6.
Cela nous permettra de voir l’inventaire de tous les articles le 1-janv-21.
Lorsque nous voulons obtenir l’inventaire de fermeture de tous les articles à une date différente, procédez comme suit :
Modifiez la date dans la cellule G1 de 1-janv-21 à 2-janv-21 pour obtenir l’inventaire de fermeture de cette date particulière.
De cette façon, nous pouvons obtenir le résultat LOOKUP à partir d’une colonne dynamique dans Google Sheets.
Cependant, je n’ai pas mentionné délibérément un point important : la formule ci-dessus est destinée à un ensemble de données trié.
Les dates dans la colonne A doivent être dans l’ordre chronologique pour que la formule retourne le résultat correct, car LOOKUP est destiné à une plage triée.
Puis-je modifier et utiliser les deux formules ci-dessus dans un ensemble de données non trié ?
Oui ! Nous devons modifier légèrement la requête ou le filtre utilisé comme tableau de résultats de recherche de LOOKUP. Voici quelques conseils.
Données non triées dans le tableau de résultats de recherche
Veuillez jeter un coup d’œil à la plage G4:H11 sur l’image n°2 ci-dessus.
Vous pouvez voir que la formule du tableau de résultats de recherche dans la cellule G4 renvoie toutes les lignes de l’ensemble de données.
Nous voulons les lignes qui correspondent à la date dans la cellule G1. Ainsi, nous pouvons éviter le problème de non-tri.
Nous pouvons utiliser le critère de date dans la requête pour filtrer les lignes qui ne correspondent pas à la date dans la cellule G1.
=QUERY({$A$2:$D}, "Select Col1,Col" & match(F4, $A$2:$D$2, 0) & " where Col1=date '" & TEXT(&G1, "yyyy-mm-dd") & "'")
Ce sera le tableau de résultats de recherche dans LOOKUP. Si vous préférez utiliser FILTER, utilisez la formule suivante.
=filter({$A$2:$A, filter($B$2:$D, $B$2:$D$2=$F4)}, $A$2:$A=$G$1)
Cela signifie que nous pouvons utiliser l’une ou l’autre des formules suivantes pour obtenir le résultat LOOKUP à partir d’une colonne dynamique dans un ensemble de données trié ou non trié dans Google Sheets.
Formule A :
=lookup($G$1, QUERY({$A$2:$D}, "Select Col1,Col" & match(F4, $A$2:$D$2, 0) & " where Col1=date '" & TEXT($G$1, "yyyy-mm-dd") & "'"))
Formule B :
=lookup($G$1, filter({$A$2:$A, filter($B$2:$D, $B$2:$D$2=$F4)}, $A$2:$A=$G$1))
Pour tester, veuillez procéder comme suit :
- Sélectionnez A3:D9.
- Allez dans le menu DONNÉES et cliquez sur « Randomize range ».
C’est tout. Merci d’être resté(e), amusez-vous bien.
Sample_Sheet_23121
Ressources :
