Utilisation d’Index Match dans Google Sheets (Guide étape par étape)

Les fonctions INDEX et MATCH de Google Sheets, lorsqu’elles sont utilisées seules, peuvent sembler avoir des applications limitées. Cependant, ensemble, ils peuvent être très efficaces. En fait, ensemble, ils peuvent fournir une excellente (et en fait meilleure) alternative à VLOOKUP.

Dans ce didacticiel, nous aborderons le mappage des répertoires Google Sheets, ce que chaque fonction fait individuellement et comment elles fonctionnent ensemble. Nous expliquerons également pourquoi nous avons dit que cette combinaison principale est en fait meilleure que VLOOKUP.

Fonction CORRESPONDANCE

La fonction MATCH de Google Sheets fournit la position relative ou “index” d’un élément dans une plage de cellules. Il accepte une plage de cellules et une valeur et renvoie la position de cette valeur dans la plage de cellules.

La syntaxe de la fonction MATCH est la suivante :

=MATCH(search_key, range, search_type)

Ici,

• clé de recherche est l’objet que nous voulons connecter. Il peut s’agir d’un texte ou d’une valeur numérique, d’une référence de cellule ou d’une formule.
• Région est la plage de cellules où nous voulons rechercher un élément qui correspond clé de recherche.
• type de recherche est un paramètre facultatif. Il définit la correspondance que nous voulons. Il peut s’agir de l’une des valeurs suivantes :
  • 0 : Cette valeur spécifie qu’une recherche doit être effectuée exactement objet correspondant. Cette option est généralement utilisée lorsque notre Région probablement pas triés dans n’importe quel ordre.
  • 1 : Il s’agit de la valeur par défaut. Cette option suppose que la plage est déjà triée en hausse Ordre. La définition de ce paramètre sur 1 renvoie la plus grande valeur inférieure ou égale à clé de recherche.
  • -1 : cette option suppose que la plage est déjà triée vers le bas Ordre. La définition de ce paramètre sur -1 renvoie la plus petite valeur supérieure ou égale à clé de recherche.

Prenons un exemple simple pour comprendre le fonctionnement de la fonction MATCH. Supposons que vous disposiez de la liste suivante de noms d’employés :

Si vous voulez connaître l’emplacementFermer Véga‘, vous pouvez utiliser la fonction MATCH comme suit :

=MATCH(‘Cierra Vega’,A2:A8,0)

Cela renvoie 4 car l’élément correspondant est le quatrième élément de la liste à partir de la cellule A2. Notez que 4 n’est pas le numéro de ligne de l’élément correspondant. C’est un poste ou indice dans la plage spécifiée de la cible (A2:A8).

Fonction INDEX

La fonction INDEX Google Sheets fournit le contenu d’une cellule ou d’une plage de cellules spécifique. En d’autres termes, il accepte une plage de cellules, un index de ligne et un index de colonne, et renvoie la valeur dans la cellule à l’intersection de la ligne et de la colonne spécifiées.

La syntaxe de la fonction INDEX est la suivante :

INDEX(reference, [row], [column])

Ici,

• référence est la plage de cellules à partir de laquelle nous voulons extraire la cible.
• ligne est le décalage de ligne dans la référence à partir de laquelle nous voulons extraire l’objet.
• dans la colonne est le décalage de colonne dans la référence à partir de laquelle nous voulons extraire l’élément. Ce paramètre est facultatif.

Prenons un exemple simple pour comprendre le fonctionnement de la fonction INDEX. Supposons que vous disposiez de la liste suivante de noms d’employés :

Si vous souhaitez connaître le nom du 4ème employé dans la liste des noms, vous pouvez utiliser la fonction INDEX comme suit :

=INDEX(A2:A8,4,1)

Cela renvoie la valeur ‘Fermer Véga‘, car il se trouve dans la colonne 1 en position 4 à partir de la cellule A2. Notez que 4 n’est pas le numéro de ligne de l’élément correspondant. C’est un poste ou indice dans la plage spécifiée de la cible (A2:A8).

Pourquoi utiliser les fonctions INDEX et MATCH dans Google Sheets ?

Comme vous l’avez peut-être déjà remarqué, les fonctions MATCH et INDEX n’apportent pas vraiment de valeur par elles-mêmes. Mais voici la torsion. Lorsque vous combinez les fonctions INDEX et MATCH de Google Sheets, elles peuvent faire des choses très utiles !

Combinées, ces deux formules peuvent rechercher une valeur dans une cellule d’un tableau et renvoyer la valeur correspondante dans une autre cellule de la même ligne ou colonne.

Considérez l’ensemble de données suivant, dans lequel vous disposez d’une liste d’ID d’employés, de noms, de services et de salaires mensuels :

Si vous souhaitez accéder au salaire d’un employé en fonction de son nom, cela peut ne pas être possible avec une fonction (sauf VHAKU).

C’est là que l’équipe INDEX-MATCH peut être utile. Cette paire de fonctions vous permet d’accéder au département ou au salaire de n’importe quel employé en fonction de son nom, ou du nom de n’importe quel employé en fonction de son ID.

Dans les sections suivantes, nous expliquerons exactement comment vous pouvez y parvenir.

Combinaison des fonctions INDEX et MATCH dans Google Sheets

La formule générale pour combiner les fonctions INDEX et MATCH est la suivante :

=INDEX(range2,MATCH(search_key,range1,0))

Ici,

• clé de recherche est la valeur que nous voulons rechercher dans range1.
• zone1 est la plage de cellules où la fonction MATCH trouve l’index de la valeur correspondante clé de recherche.
• zone2 est la plage de cellules à partir de laquelle la fonction INDEX extrait la valeur correspondant à la position/index renvoyé par MATCH.

En d’autres termes, la fonction MATCH aide la fonction INDEX à identifier l’emplacement de la valeur à renvoyer.

Regardons un exemple pour comprendre cela un peu plus.

Utilisation des fonctions INDEX et MATCH sur des références à une seule colonne

Dans la capture d’écran suivante, vous remarquerez que nous avons un tableau composé d’identifiants, de noms, de départements et de salaires. Au bas de la capture d’écran se trouve un autre petit tableau avec le nom d’un employé et un espace vide pour le service de l’employé :

Si le nom de la sous-table est dynamique (peut changer), nous ne pouvons pas le coder dans une formule qui trouve le département correspondant.

Dans ce cas, une combinaison des fonctions INDEX et MATCH est idéale. Voici la formule que nous pouvons utiliser :

=INDEX(C2:C8,MATCH(B10,B2:B8,0))

Comme le montre l’image ci-dessous, cette formule renvoie le département correspondant au nomFermer Véga‘ :

Explication de la formule

Pour comprendre comment cette formule a fonctionné, décomposons-la. Nous commençons par la fonction à l’intérieur de la formule :

MATCH(B10,B2:B8,0)

Cette fonction recherche la plage B2:B8 pour la valeur dans B10 et renvoie la position de cette valeur dans la plage. Depuis ‘Fermer Vega’ est le quatrième nom de la cellule B2, cette fonction renvoie la position 4.

Ensuite, regardons la fonction externe de la formule :

INDEX(C2:C8,MATCH(B10,B2:B8,0))

Cette formule recherche la valeur à la 4ème position de la plage C2: C8 et renvoie la valeur à cette position qui est “Ventes’. Ceci est un quartierFermer Vega’ fait parti.

Maintenant, même si nous changeons le nom dans la cellule B10, nous obtiendrons toujours le résultat correct dans la cellule B11 :

Ainsi, les fonctions INDEX-MATCH nous ont aidés à utiliser une valeur pour accéder à une autre valeur dans une colonne différente de la même ligne.

Utilisation de la fonction INDEX et MATCH avec plusieurs critères

L’exemple ci-dessus utilise une colonne comme plage. Cependant, les fonctions INDEX-MATCH peuvent également offrir plus de flexibilité en nous permettant d’accéder à une valeur à partir de plusieurs colonnes.

Dans notre exemple de feuille de calcul, disons que nous pouvons avoir soit département Nom, Payer, ou même identifiant sous forme de cachet dans la cellule A11. En d’autres termes, que se passerait-il si l’étiquette de la cellule A11 était également dynamique et modifiée ?

Dans ce cas, nous devons considérer plusieurs colonnes, et la colonne à utiliser dépend de l’en-tête de la cellule A11. Eh bien, cela peut également être réalisé en utilisant INDEX-MATCH comme suit :

=INDEX(A2:D8,MATCH(B10,B2:B8,0),MATCH(A11,A1:D1,0))

Cette fois, nous avons également utilisé le troisième paramètre de la fonction INDEX (qui nous permet de spécifier l’index de colonne dans la plage).

Comme le montre l’image ci-dessous, si la cellule A11 contient le texte “Payer”, cette formule renvoie le salaire qui correspond Nom défini dans la cellule B10 :

Si la cellule A2 contient le texte “identifiant“, cette même formule renvoie un ID mensuel qui correspond Nom défini dans la cellule B10 :

Même si tu changes Nom dans la cellule B10, vous obtenez toujours le bon ID :

Explication de la formule

Pour comprendre comment cette formule a fonctionné, décomposons-la. Nous commençons avec la première fonction MATCH :

MATCH(B10,B2:B8,0)

Comme expliqué précédemment, cette fonction renvoie l’emplacement Fermer Véganom dans la plage B2:B8. Il renvoie donc l’index 4.

Ensuite, regardons une autre fonction MATCH :

MATCH(A11,A1:D1,0)

Cette fonction recherche la valeur de la cellule A11 dans la plage A1:D1 et renvoie la position de cette valeur dans la plage. Lorsque A11 contient le texte “département”, la fonction détecte qu’il s’agit du troisième élément à partir de la cellule A1. Cette fonction renvoie donc la position 3.

Enfin, regardons la fonction externe :

INDEX(A2:D8,MATCH(B10,B2:B8,0),MATCH(A11,A1:D1,0))

Cette formule recherche la valeur en 4ème position en ligne et en 3ème position en colonne dans la plage A2:D8 (avec décalage de ligne et de colonne 4 et 3) et renvoie la valeur à cette position, qui est “Ventes’. c’est département répondre Fermer Véga.

Nous avons donc maintenant beaucoup de flexibilité pour aller de l’avant et changer Nom que nous voulons utiliser et les informations que nous voulons pour cela Nom.

Pourquoi utiliser INDEX et MATCH est-il meilleur que VLOOKUP ?

Après avoir lu l’intégralité du didacticiel, vous pensez peut-être :

“Tout cela ne pourrait-il pas être fait avec RECHERCHEV ?”

Eh bien, ça pourrait ! En fait, ce que fait INDEX-MATCH est essentiellement une VLOOKUP. Cependant, INDEX-MATCH peut faire certaines choses que VLOOKUP ne peut pas.

• INDEX-MATCH vous permet de rechercher des colonnes à gauche et à droite de la colonne de recherche, tandis que VLOOKUP vous permet uniquement de rechercher sur le côté gauche de la colonne de recherche. Il recherche la première colonne d’une plage donnée, puis recherche les correspondances uniquement à droite de celle-ci. Essayer d’utiliser la colonne à gauche de la plage avec VLOOKUP renvoie une erreur #N/A.
• L’ajout de nouvelles colonnes ou le déplacement de colonnes existantes n’affecte pas les résultats de la formule INDEX-MATCH car elle utilise des références de cellule. Ainsi, l’ordre des colonnes ou le changement d’index n’a pas d’importance. Cependant, les résultats VLOOKUP sont complètement perturbés si une colonne est modifiée ou supprimée car elle utilise l’ordre des colonnes au lieu des références de cellules.

Related: VLOOKUP from Another Sheet

En conclusion, la fonction Index Match Google Sheets offre une excellente alternative à VLOOKUP en ajoutant de la flexibilité aux opérations de recherche impliquant des références de cellules dynamiques. Nous espérons que vous avez trouvé ce tutoriel utile et facile à comprendre et à suivre.