Comment utiliser la fonction INDEX dans Google Sheets ?

Les Fonction INDEX dans Google Sheets renvoie la valeur d'une cellule à l'intérieur d'une plage de saisie, relativement séparée de la première cellule par des décalages de ligne et de colonne. Cette fonction est similaire à l'index figurant à la fin d'un livre, qui permet de localiser rapidement un contenu spécifique.

Syntaxe

INDEX(référence, [décalage_de_ligne], [décalage_de_colonne])

• référence - l'adresse de la plage de cellules dans laquelle le décalage est évalué à partir de la toute première cellule (en haut à gauche). En conséquence, la formule INDEX renvoie la valeur de la cellule cible du décalage.
• row_offset - le nombre de lignes à décaler par rapport à la cellule de départ.
• column_offset - le nombre de colonnes à décaler par rapport à la cellule de départ.

Comment utiliser la fonction INDEX dans Google Sheets ?

Voyons quelques exemples pratiques pour comprendre comment utiliser cette fonction.

Dans les exemples suivants, j'ai utilisé des données fictives pour montrer l'utilisation de la fonction INDEX.

Dans la capture d'écran ci-dessus, tous les cas, à l'exception du dernier, sont assez simples.

Essentiellement, vous donnez à la formule une plage de cellules, puis vous lui donnez les coordonnées de la cellule de la plage que vous voulez qu'elle renvoie comme résultat. Ainsi, dans rangée 3, où la formule est =INDEX(A2:B11,8,2), j'indique à la formule que je veux la cellule de la ligne 8, colonne 2 du tableau, qui est $13,947.

Dans le dernier exemple de la capture d'écran (rangée 5) Je n'ai spécifié aucun décalage de ligne ou de colonne. Par conséquent, la fonction a renvoyé l'ensemble des valeurs de la plage de référence saisie. En fait, cette fonction pourrait servir d'entrée pour une autre formule de tableau. Par exemple, vous pourriez l'utiliser pour consolider des données provenant de plusieurs feuilles, ou vous pourriez faire quelque chose comme =COUNTA(INDEX(A2:A8)), qui aurait renvoyé la valeur 7.

Y a-t-il des cas où la fonction INDEX peut générer une erreur ? Bien sûr, oui ! Lorsque vous essayez d'éloigner la fonction des limites de la plage d'entrée, elle génère une erreur, comme le montre la capture d'écran ci-dessous :

Comment utiliser la fonction INDEX en combinaison avec d'autres formules ?

Je vais examiner ici quelques cas d'utilisation qui montrent comment la fonction INDEX de Google Sheets peut être utilisée en combinaison avec d'autres fonctions et formules.

Cas d'utilisation #1 : Fonction INDEX et fonction COUNTA

La combinaison de la fonction COUNTA avec la fonction INDEX peut s'avérer utile dans les situations où, par exemple, vous souhaitez toujours effectuer un calcul à l'aide de la fonction dernière ligne de données dans une liste qui s'enrichit régulièrement.

Dans la capture d'écran suivante, j'ai une feuille de calcul qui est mise à jour chaque semaine avec la température moyenne pour cette semaine.

Si je veux toujours effectuer un calcul avec la température moyenne de la semaine la plus récente, je peux utiliser COUNTA et INDEX pour toujours sélectionner la dernière entrée, en utilisant la formule =INDEX((A:B), COUNTA(A:A),2)

Ici, l'ensemble des colonnes A et B constitue la plage. La fonction COUNTA(A:A) compte le nombre de points de données dans la colonne A et vous indique combien il y en a, et la formule INDEX prend ce nombre comme ligne à partir de laquelle elle obtiendra son résultat.

Le 2 à la fin indique à la formule qu'elle obtiendra le résultat de la deuxième colonne de la plage (colonne B). Ainsi, quel que soit le nombre de semaines ajoutées à la liste, la “semaine la plus récente” restera toujours à jour !

Cas d'utilisation #2 : combinaison des fonctions MATCH et INDEX

L'une des utilisations les plus puissantes de la fonction INDEX dans Google Sheets est sans doute celle qui consiste à l'associer à la fonction MATCH pour rechercher des valeurs.

Mais il existe déjà une formule VLOOKUP dans Google Sheets à cet effet, n'est-ce pas ?

Regardez l'exemple ci-dessous :

La formule VLOOKUP de D2 recherche 161 dans la rubrique Emp ID # (car il s'agit de la colonne la plus à gauche de la plage A2:B11), et à partir de la ligne où il trouve la valeur 161, il récupère la valeur située dans la deuxième colonne (c'est-à-dire la colonne B), tout en supposant que les données ne sont pas triées.

Jusqu'à présent, tout va bien. Mais il existe deux problèmes majeurs avec VLOOKUP dans Google Sheets.

Problèmes avec VLOOKUP qui peuvent être résolus en utilisant les fonctions INDEX et MATCH

Avant de poursuivre avec des exemples d'utilisation combinée des fonctions INDEX et MATCH, je décrirai deux problèmes critiques liés à la fonction VLOOKUP, afin de vous donner une idée des limites de cette fonction et de vous indiquer quand utiliser plutôt les fonctions INDEX et MATCH.

Problème #1 : Référencement statique de cellules

Que se passe-t-il si vous insérez une nouvelle colonne entre la première et la deuxième colonne ? Essayons-le :

Vous remarquerez que la valeur retournée n'est plus “Ethan”. C'est parce que VLOOKUP est une formule semi-statique.

Google Sheets a mis à jour le deuxième paramètre pour refléter la nouvelle plage, mais n'a pas modifié en conséquence l'indice de la colonne (troisième paramètre) lorsqu'une nouvelle colonne a été ajoutée avant le paramètre Vendeur colonne.

Problème #2 : La colonne de consultation est toujours la plus à gauche

Il peut arriver que vous deviez rechercher des valeurs dans une colonne (Emp ID #) qui n'est pas la plus à gauche, comme indiqué ci-dessous.

Dans ce cas, le déplacement du Emp ID # pour en faire la colonne la plus à gauche. Mais ce n'est pas l'approche idéale - il peut y avoir des spécifications de mise en page ou de présentation des données qui ne vous permettent pas de réorganiser les colonnes.

Dans cette situation, que pouvez-vous faire ? La combinaison MATCH et INDEX vient à la rescousse. Voici la syntaxe, suivie de quelques exemples.

INDEX(reference, MATCH(search_key, range, search_type))

La clé de cette combinaison est que les plages sélectionnées pour les fonctions INDEX et MATCH, respectivement, doivent être une seule colonne.

Vous utilisez essentiellement un VLOOKUP, mais en spécifiant la colonne dans laquelle rechercher et la colonne à partir de laquelle renvoyer la valeur, dans des plages distinctes au sein des fonctions MATCH et INDEX Comme vous le verrez, cette méthodologie vous permet d'éviter les erreurs qui peuvent survenir avec VLOOKUP.

Fonction MATCH

Pour ceux qui ne connaissent pas la fonction MATCH, je vais la résumer brièvement. La fonction MATCH prend une clé de recherche donnée, c'est-à-dire la valeur que vous lui demandez de rechercher dans un certain intervalle. Contrairement à la fonction INDEX, cette valeur doit être unidimensionnelle (à l'intérieur d'une seule colonne ou d'une seule ligne).

La fonction MATCH vous donne l'emplacement de l'élément que vous lui avez demandé de rechercher. Pour search_type, vous indiquez généralement 0, ce qui indique à la fonction qu'il doit s'agir d'une correspondance exacte.

Dans l'exemple ci-dessus, j'ai utilisé la fonction MATCH pour me dire quelle ligne contient l'ID Emp #161 dans la colonne C. La fonction renvoie un nombre qui indique à la fonction INDEX dans quelle ligne de la colonne A chercher le vendeur. Le résultat est que les deux fonctions se combinent pour m'indiquer le nom correct (Ethan) pour l'ID employé #161. La formule que j'ai utilisée pour ce faire est =INDEX(A2:A11,MATCH(161,C2:C11,0)).

Comme vous pouvez le constater, contrairement à VLOOKUP, la combinaison fonctionne même si la colonne de consultation n'est pas la plus à gauche (trois premiers exemples dans la capture d'écran ci-dessus). Il n'est pas surprenant qu'elle fonctionne également comme VLOOKUP lorsque la colonne de consultation est la plus à gauche.

Voyons également si les fonctions se maintiennent lorsque l'on introduit une nouvelle colonne entre les deux.

Heureusement, elles fonctionnent toujours. Dès que j'ai introduit une nouvelle colonne (État), Google Sheets a automatiquement mis à jour les références pour tenir compte de ce changement.

Comme vous l'avez vu ci-dessus, la combinaison des fonctions MATCH et INDEX est beaucoup plus flexible et polyvalente que la fonction VLOOKUP, déjà populaire et puissante. Mais la combinaison de ces fonctions peut être encore plus puissante lorsque vous utilisez deux fonctions MATCH au lieu d'une seule.

Cas d'utilisation #3 : Fonction INDEX avec deux fonctions MATCH

Vous avez vu à quel point la combinaison INDEX et MATCH peut être utile. Mais qu'en est-il si vous disposez d'un tableau bidimensionnel dont vous souhaitez obtenir une valeur ? Dans ce cas, vous pouvez utiliser la fonction MATCH deux fois dans la fonction INDEX, comme le montre l'exemple ci-dessous.

Dans cet exemple, vous pouvez voir des données sur la température moyenne de chaque mois, pour les années 2015-2020. Si vous souhaitez extraire de ce tableau la température moyenne d'un mois spécifique d'une année spécifique, vous pouvez utiliser la combinaison INDEX et MATCH pour y parvenir. La fonction utilisée ici est la suivante :

=INDEX(B2:M7, Match(B11, A2:A7, false), Match(B10, B1:M1, false))

Comme vous pouvez le voir, j'utilise la fonction MATCH pour obtenir l'emplacement de la ligne correspondant à l'année recherchée (dans ce cas, il s'agit de 2018, qui se trouve dans la 4e ligne de données) ainsi que la colonne correspondant au mois recherché (septembre, dans la 9e colonne de données). La fonction INDEX prend ensuite ces coordonnées et renvoie la température moyenne pour septembre 2018.

En utilisant les fonctions INDEX et MATCH, vous pouvez non seulement obtenir une fonction de recherche plus polyvalente que VLOOKUP, mais aussi effectuer des recherches dans des tableaux bidimensionnels. En savoir plus sur Fonction MATCH de Google Sheets ici.

Vous avez aimé cet article ?

Si vous avez trouvé cet article utile, partagez-le avec votre réseau via les boutons de médias sociaux à gauche !