Apprenez à utiliser la fonction INDEX ainsi que la fonction combo INDEX et MATCH dans Excel (avec exemples).

La fonction INDEX est l’une des fonctions les plus utilisées dans Excel qui est classée comme fonction de recherche/référence. La fonction INDEX est utilisée pour extraire une valeur ou une référence d’une cellule dans un tableau ou une plage en fonction du numéro de ligne et du numéro de colonne que vous avez spécifiés. Il peut également être utilisé pour extraire des lignes et des colonnes entières dans un tableau.

Par exemple, nous avons une table de 700 lignes et 100 colonnes et nous voulons renvoyer la valeur à la 522e ligne et à la 50e colonne. Pour ce faire, vous n’avez pas besoin de faire défiler des centaines de lignes pour accéder à la valeur, à la place, vous pouvez utiliser la fonction INDEX pour renvoyer la valeur à l’intersection du numéro de ligne et de colonne spécifié.

La fonction INDEX est souvent utilisée avec la fonction MATCH comme alternative puissante à VLOOKUP. Dans l’article, nous expliquerons ce qu’est la fonction INDEX, comment utiliser la fonction INDEX séparément et comment utiliser INDEX et MATCH avec des exemples détaillés dans Excel.

Formats de la fonction INDEX

Il existe deux formats de la fonction INDEX dans Excel : Array et Reference.

La forme tableau de la fonction INDEX :

La fonction Index de tableau est utilisée pour renvoyer la ou les valeurs réelles à l’intersection de la ligne et de la colonne spécifiées dans une seule plage.

Syntaxe :

=INDEX(array, row_num, [column_num])

Les arguments de la fonction ci-dessus :

  • tableau – Cela spécifie un tableau ou une plage de cellules à partir de laquelle vous souhaitez renvoyer une valeur.
  • row_num – Cela spécifie le numéro de ligne à partir de laquelle vous souhaitez extraire la valeur.
  • column_num – Cela spécifie le numéro de colonne à partir de laquelle vous souhaitez extraire la valeur. S’il est omis ou nul, la fonction prend 1 par défaut.

Si le numéro de ligne n’est pas spécifié, le numéro de colonne est requis et si le numéro de colonne est omis, le numéro de ligne est requis.

Le format de référence de la fonction INDEX

Le format Référence de la fonction INDEX est utilisé pour renvoyer la référence de la cellule à l’intersection du numéro de ligne et du numéro de colonne.

Syntaxe:

=INDEX(reference, row_num, [column_num], [area_num])

La fonction utilise les arguments ci-dessous :

  • référence – Cet argument spécifie la référence à une ou plusieurs plages de cellules (zones). Si plusieurs plages ou tableaux sont utilisés dans une fonction, ils doivent être séparés par des virgules et fermés par des crochets, c’est-à-dire (A3:B11, B15:D5, E10:K50).
  • row_num – Il indique le numéro de ligne dans la référence ou le tableau spécifié.
  • [col_num] – Il indique le numéro de colonne dans la référence ou le tableau spécifié. S’il est omis ou nul, la fonction prend 1 par défaut.
  • [area_num] – Si l’argument de référence est composé de plusieurs plages ou tableaux, cela spécifie la plage ou la zone à utiliser. Les zones sont numérotées de gauche à droite dans l’argument de référence. La première zone saisie est considérée comme 1, la seconde 2, et ainsi de suite. Si cet argument est omis, sa valeur par défaut est 1.

Comment utiliser la fonction INDEX dans Excel

Pour mieux comprendre le fonctionnement de la fonction INDEX dans Excel, considérons quelques exemples.

Fonction INDEX avec tableau unidimensionnel (format tableau)

La fonction INDEX peut être utilisée sur un tableau unidimensionnel (recherche unidirectionnelle) pour renvoyer la valeur d’une seule ligne ou colonne. Pour cette fonction, seuls l’argument de tableau et le numéro de ligne ou le numéro de colonne sont requis.

Supposons que vous ayez l’ensemble de données ci-dessous :

Maintenant, sélectionnez la cellule dans laquelle vous souhaitez renvoyer le résultat de la fonction. Tapez ensuite la formule suivante pour obtenir la note de la 10e ligne de l’examen 1 et appuyez surEnter :

=INDEX(C2:C20,9)

Dans la formule ci-dessus, C2:C20spécifie la plage ou la colonne et 9fait référence au numéro de ligne pour extraire la valeur de la plage donnée. Puisque nous recherchons la valeur dans une seule colonne, l’argument col_num est omis.

Comme vous pouvez le voir ci-dessus, nous avons exclu l’en-tête de colonne lorsque nous avons spécifié la plage dans la formule, afin que nous puissions commencer à compter la ligne à partir de C2, c’est-à-dire la 1ère ligne dans la plage donnée, d’où la valeur à la ligne 9 est 77.

Fonction INDEX avec tableau bidimensionnel (format tableau)

Le tableau à deux dimensions signifie un tableau avec plus d’une ligne et une colonne. Pour utiliser la fonction de tableau dans une recherche bidirectionnelle, vous aurez besoin d’une plage, d’un numéro de ligne et/ou d’un numéro de colonne. Si un numéro de ligne ou un numéro de colonne est ignoré, sa valeur par défaut est 1.

Supposons que nous ayons l’ensemble de données ci-dessous où nous voulons rechercher et renvoyer le score de Laura au Quiz 2.

Pour ce faire, sélectionnez la cellule où vous souhaitez renvoyer le résultat et exécutez la formule suivante :

=INDEX(A2:G20,9,4)

Où A2:G20(en-tête de colonne exclu) est le tableau ou la plage, 9 est le numéro de ligne et 4 est le numéro de colonne dans la formule. La formule ci-dessus renvoie la valeur (92) à l’intersection de la ligne 9 et de la colonne 4 du tableau A2:G20.

Fonction INDEX avec uniquement un numéro de ligne ou de colonne (format tableau)

Si un argument de tableau contient plusieurs lignes et colonnes, nous devons spécifier à la fois les numéros de ligne et de colonne pour extraire une valeur spécifique. Toutefois, si le numéro de ligne ou de colonne est laissé vide ou entré comme zéro dans la formule INDEX, la formule renvoie un tableau de valeurs.

Exemple 1:

Essayez la formule ci-dessous où le numéro de colonne est zéro ou vide :

=INDEX(A2:F20,13,0)

ou

=INDEX(A2:F20,13,)

Lorsqu’une formule renvoie plusieurs valeurs, elle est considérée comme une formule matricielle. Il faut donc l’exécuter en appuyant sur CtrlShiftEnter. Alors, tapez la formule ci-dessus et appuyez sur CtrlShiftEnterpour l’exécuter comme une formule matricielle.

Si la formule INDEX est entrée sous forme de formule matricielle dans une seule cellule, elle ne renvoie que la première valeur de la ligne ou de la colonne spécifiée, comme indiqué ci-dessous. Si une formule est exécutée sous forme de formule matricielle, les accolades seront automatiquement insérées.

Dans le cas où le numéro de ligne ou les numéros de colonne ou les deux sont entrés sous forme de zéros ou vides, et que vous exécutez la formule comme une formule normale en appuyant simplement sur Enter, la sortie serait ‘#VALUE!’ Erreur.

Exemple 2 :

Avant d’entrer la formule INDEX en tant que formule matricielle, vous devez d’abord sélectionner la plage de sortie exacte. La plage de sortie doit avoir le même nombre de cellules vides que le tableau de l’ensemble de données source. Voici comment procéder :

Pour renvoyer un tableau de valeurs en tant que sortie de la formule INDEX , sélectionnez d’abord la plage de sortie vide A23:G23. Ensuite, tapez la formule =INDEX(A2:F20,13,0)dans la première cellule sélectionnée (A23) et appuyez simultanément sur CtrlShiftEnterpour l’exécuter sous forme de formule matricielle.

Comme vous pouvez le voir ci-dessus, la ligne entière est renvoyée en sortie dans la plage sélectionnée.

Exemple 3 :

Vous pouvez également utiliser les cellules pour saisir votre numéro de ligne et votre numéro de colonne afin d’obtenir des résultats dynamiques à partir de la formule INDEX, sans avoir à modifier votre formule à chaque fois pour obtenir une sortie différente.

Pour ce faire, utilisez la formule ci-dessous :

=INDEX(A2:G20,I5,I6)

Où A2:G20est la plage, la I5référence indique à la formule d’utiliser la valeur de cette cellule comme numéro de ligne et I6fait référence à la valeur de cette cellule comme numéro de colonne pour renvoyer le résultat (86).

Désormais, vous pouvez facilement ajuster le numéro de ligne ou de colonne dans les cellules I5et I6obtenir un résultat différent sans apporter aucun ajustement à la formule d’origine.

Fonction INDEX avec plusieurs tableaux (formulaire de référence)

Le formulaire de référence INDEX peut être utilisé pour extraire la valeur si vous avez plusieurs tableaux ou tableaux dans la formule. Vous pouvez également utiliser une référence de cellule unique et pas seulement un tableau dans la formule. La fonction INDEX du formulaire de référence a également besoin d’un argument supplémentaire – le numéro de zone qui indique à la fonction à partir de quel tableau extraire les données.

Supposons que vous disposiez de trois ensembles de données différents contenant les détails des ventes et des expéditions de produits, comme indiqué ci-dessous.

Pour extraire la valeur de plusieurs tableaux bidimensionnels avec Index, tapez ci-dessous la formule où vous voulez le résultat et appuyez sur Enter:

=INDEX((A3:D9,A14:D18,F3:H5),3,4,2)

Voici A3:D9, A14:D18, F3:H5les 3 zones de l’argument de référence et elles sont appelées zones 1, 2 et 3 respectivement. Le numéro de ligne et le numéro de colonne sont respectivement 3 et 4, d’où nous devons extraire la valeur. Et l’argument final 2 spécifie la plage ou la zone à utiliser.

En conséquence, la formule INDEX affichera la valeur de la 3ème ligne de la quatrième colonne du 2ème tableau de la feuille, c’est-à-dire ‘7.2’.

Fonction d’indexation avec d’autres fonctions

La fonction Index peut être utilisée avec diverses autres fonctions pour effectuer divers calculs dans Excel.

Exemple 1:

Par exemple, nous voulons additionner les valeurs de la 3ème colonne du tableau A2:C20 à l’aide de la fonction INDEX. Vous pouvez le faire avec la formule ci-dessous:

=SUM(INDEX(A2:E20,0,3))

Ici, la fonction INDEX est incluse dans la fonction SOMME pour additionner les valeurs de la 3ème colonne.

Exemple 2 :

Essayons la fonction INDEX avec une formule moyenne pour renvoyer la moyenne d’une plage. Par défaut, la fonction Index renvoie la valeur d’une cellule. Cependant, lorsque la fonction INDEX vient après la référence de cellule et deux-points (:), il renverra une référence de cellule à la place.

Par exemple, la formule ci-dessous renvoie la moyenne de la plage de cellules B2 : B6 :

=AVERAGE(B2:INDEX(B2:B10,5))

Le INDEX(B2:B10,5)renvoie une référence à la cellule B6 car une référence de cellule et les deux-points (:) précédaient la fonction. En conséquence, nous avons maintenant la formule AVERAGE(B2:B6) et la sortie est de 75,8.

Si vous modifiez les numéros de ligne dans la fonction INDEX, cela produira des résultats dynamiques.

Utilisation conjointe des fonctions INDEX et MATCH

Les fonctions INDEX sont rarement utilisées seules. Ils sont souvent associés à MATCH pour effectuer des calculs puissants. Lorsque la fonction INDEX est combinée avec la fonction MATCH, elle peut effectuer des recherches avancées à gauche. Beaucoup de gens préfèrent toujours utiliser VLOOKUP pour rechercher une valeur, car c’est plus populaire et plus simple, mais INDEX MATCH est plus flexible et plus rapide que VLOOKUP.

Contrairement à la fonction RECHERCHEV, elle peut fonctionner avec des données disposées à la fois verticalement et horizontalement et elle peut rechercher des valeurs de gauche à droite ainsi que de droite à gauche. INDEX MATCH n’a pas de restriction de tableau ni de limite de taille de valeur de recherche. De plus, INDEX et MATCH utilisent des plages de données dynamiques lors de la recherche de valeurs.

Fonction MATCH d’Excel 

Avant de combiner les fonctions INDEX et MATCH, voyons d’abord comment fonctionne la fonction MATCH avec un exemple.

La fonction MATCH est une fonction intégrée dans Excel qui recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de cette valeur dans la plage donnée.

Syntaxe de la fonction MATCH :

=MATCH(lookup_value,lookup_array,[match_type})

Où:

lookup_value –  La valeur que vous souhaitez rechercher dans une plage de cellules spécifiée ou un tableau. Il peut s’agir d’une valeur numérique, d’une valeur textuelle, d’une valeur logique ou d’une référence de cellule qui a une valeur.

lookup_array  – Les tableaux de cellules dans lesquels vous recherchez une valeur. Il doit s’agir d’une seule colonne ou d’une seule ligne.

match_type  – Il s’agit d’un paramètre facultatif qui peut être défini sur 0, 1 ou -1 et la valeur par défaut est 1.

  •   recherche une correspondance exacte, lorsqu’elle n’est pas trouvée, renvoie une erreur.
  • -1  recherche la plus petite valeur supérieure ou égale à lookup_value lorsque le tableau de recherche est dans l’ordre croissant.
  • 1  recherche la plus grande valeur inférieure ou égale à la valeur look_up lorsque le tableau de recherche est dans l’ordre décroissant.

Exemple:

Dans l’ensemble de données ci-dessous, nous voulons trouver la position exacte du nom du produit (Avery 510), voyons comment nous pouvons le faire avec la fonction MATCH.

Pour trouver la position de la valeur dans une ligne ou une colonne, essayez la formule ci-dessous :

=MATCH("Avery 510",A2:A19,0)

Dans la formule ci-dessus, Avery 510est la valeur de recherche pour laquelle nous voulons trouver la position, A2:A19est l’endroit où rechercher la valeur et indique à la fonction de rechercher la valeur exacte. Par conséquent, la formule renvoie la position de la valeur sous la forme ‘8’ dans la plage donnée.

La fonction MATCH est insensible à la casse, peu importe si la valeur de recherche est en minuscules ou en majuscules, elle trouvera la valeur exacte. Si la valeur de recherche est une chaîne, elle doit être placée entre guillemets doubles.

Vous pouvez également utiliser les cellules pour saisir votre valeur de recherche afin d’obtenir des résultats dynamiques, sans avoir à modifier votre formule chaque fois que vous souhaitez une sortie différente. Pour cela, utilisez une référence de cellule qui contient la valeur de recherche dans le premier argument.

La formule suivante trouve la position de la valeur dans la cellule G2 :

=MATCH(G2,A2:A19,0)

Combinez les fonctions INDEX et MATCH

RECHERCHEV ne peut rechercher une valeur que verticalement, c’est-à-dire des colonnes, tandis que la combinaison INDEX MATCH peut effectuer des recherches verticales et horizontales.

La fonction INDEX est utilisée pour récupérer une valeur à un emplacement spécifique dans une table ou une plage tandis que la fonction MATCH renvoie la position relative d’une valeur dans une colonne ou une ligne. Lorsqu’il est combiné, le MATCH peut trouver le numéro de ligne ou de colonne (position) d’une valeur spécifique, et la fonction INDEX peut récupérer la valeur en fonction de ce numéro de ligne ou de colonne.

Exemple:

Supposons que nous ayons l’ensemble de données ci-dessous, comme indiqué ci-dessous :

Dans le tableau ci-dessus, supposons que vous souhaitiez récupérer le score “Maths” de l’élève nommé “Tyisha”, essayez la formule ci-dessous :

=INDEX(B2:G22,MATCH("Tyisha",A2:A22,0),4)

Dans la formule ci-dessus, la fonction MATCH imbriquée trouve le numéro de ligne (position) de la valeur ‘Tyisha’ (5). Ensuite, ce numéro de ligne et un numéro de colonne (4), que nous avons spécifiés comme dernier argument, sont fournis à la fonction INDEX. En conséquence, la formule récupère les valeurs à l’intersection des numéros de ligne (5) et de colonne (4) dans le tableau B2:G22 et renvoie le score ’65’.

Vous pouvez également utiliser une référence de cellule comme argument pour la valeur de recherche dans la formule :

=INDEX(B2:G22,MATCH(I4,A2:A22,0),4)

Recherche bidirectionnelle avec INDEX et MATCH

Dans l’exemple ci-dessus, nous connaissons le numéro de colonne (saisi manuellement) et nous n’avons utilisé que la fonction MATCH pour trouver le numéro de ligne pour récupérer les données. Mais supposons que nous n’ayons pas non plus le numéro de colonne et que nous ne connaissions que la ligne (nom de l’étudiant) et l’en-tête de colonne (sujet) à suivre. Dans ce cas, vous pouvez utiliser les fonctions INDEX et MATCh pour trouver une valeur avec une recherche bidirectionnelle (tableau à deux dimensions).

Pour cela, nous devons imbriquer deux fonctions MATCH dans la fonction INDEX : une pour le numéro de ligne et une pour le numéro de colonne.

Exemple 1:

Maintenant, nous voulons savoir combien de points ‘Lura’ a obtenu dans la matière ‘Histoire’. Voici comment nous pouvons le faire :

Pour appliquer une recherche bidirectionnelle avec INDEX et MATCH, vous pouvez utiliser la formule ci-dessous :

=INDEX(B2:G22,MATCH("Lura",A2:A22,0),MATCH("History",B1:G1,0))

Comme nous le savons, la fonction MATCH peut rechercher une valeur à la fois horizontalement et verticalement. Dans cette formule :

  • MATCH(“Lura”,A2:A22,0) recherche la valeur ‘Lura’ verticalement dans la plage A2:A22 et renvoie sa position, qui est le numéro de ligne (9).
  • MATCH(“History”,B1:G1,0) recherche la valeur ‘History’ horizontalement dans la plage B1:G1 et renvoie sa position, qui est le numéro de colonne (4).

Ensuite, les numéros de ligne et de colonne sont fournis à la fonction INDEX pour récupérer le score (86) à l’intersection dans le tableau B2:G22.

Recherche bidirectionnelle dynamique avec INDEX et MATCH

Si vous disposez de données volumineuses, vous souhaiterez peut-être rendre les valeurs de recherche dynamiques dans la fonction Match, afin de ne pas avoir à saisir manuellement les valeurs de recherche dans la fonction. Vous pouvez simplement mettre à jour les valeurs de recherche dans des cellules spécifiques afin que les fonctions MATCH puissent identifier automatiquement les valeurs dans les cellules spécifiées et renvoyer les positions exactes (numéro de ligne et de colonne).

Voici la formule pour une recherche bidirectionnelle entièrement dynamique avec INDEX et MATCH :

=INDEX(B2:G22,MATCH(I3,A2:A22,0),MATCH(I4,B1:G1,0))

Dans la formule ci-dessus, MATCH(I3,A2:A22,0)renvoie un numéro de ligne dynamique en identifiant la valeur dans la cellule I3et en la recherchant dans la plage A2:A22. MATCH(I4,B1:G1,0) renvoie un numéro de colonne dynamique en identifiant la valeur dans la cellule I4et en la recherchant dans la plage B1:G1. Ensuite, les deux valeurs sont fournies à la fonction INDEX pour récupérer le score de Yuette en chimie : 90.

Vous pouvez changer le nom de l’étudiant et/ou le sujet dans les cellules ‘I3’ et ‘I4’ pour obtenir des résultats dynamiques différents.

Exemple 2 :

Si vous souhaitez obtenir des valeurs d’une ligne ou d’une colonne entière au lieu d’une seule valeur de cellule. Par exemple, nous pouvons utiliser la formule INDEX MATCH pour obtenir tous les scores de ‘Yuette’, y compris les scores dans toutes les matières, leur total et leur note.

Pour ce faire, essayez la formule ci-dessous :

=INDEX(B2:G22,MATCH(I3,A2:A22,0),0)

Tout d’abord, sélectionnez la plage de sortie avec autant de cellules que dans la plage source, tapez la formule et appuyez sur CtrlShiftEnter. Ici, nous avons utilisé 0 comme numéro de colonne. En conséquence, nous avons obtenu un tableau de sorties comme indiqué ci-dessous.

Si vous voulez voir la sortie du tableau renvoyée par la formule, sélectionnez simplement la formule en mode édition et appuyez sur F9.

Supposons que nous n’ayons pas de notes totales dans le tableau ci-dessus, nous pouvons obtenir les notes totales de ‘Yeutte’ dans les quatre matières en imbriquant la formule ci-dessus dans une fonction SOMME.

Voici les scores de tous les élèves dans quatre matières.

Maintenant, tapez la formule ci-dessous et exécutez-la comme une formule matricielle ( CtrlShiftEnter):

=SUM(INDEX(B2:E17,MATCH(G5,A2:A17,0),0))

Ici, les fonctions INDEX et MATCH renvoient des scores dans les quatre matières pour Yeutte et la fonction SOMME les additionne et affiche le total sous la forme 378.

De même, vous pouvez utiliser la fonction MAX avec INDEX pour calculer le score le plus élevé et la fonction MIN pour connaître le score le plus bas.

Créer des listes déroulantes pour saisir des valeurs pour la formule INDEX et MATCH

Dans les exemples ci-dessus, nous avons dû coder en dur la valeur de recherche dans la fonction MATCH ou entrer la valeur de recherche dans les cellules spécifiées pour rendre la formule dynamique. Cependant, si vous disposez d’un ensemble de données volumineux, cela peut être un processus long et sujet aux erreurs (si vous avez mal orthographié ou spécifié la mauvaise valeur.

Vous pouvez facilement éviter cela en créant une liste déroulante de valeurs de recherche (noms et matières des étudiants) et en sélectionnant une valeur dans la liste déroulante. Après avoir choisi une valeur, la formule mettrait automatiquement à jour la sortie. Voici comment procéder :

Tout d’abord, sélectionnez la cellule dans laquelle vous souhaitez créer la liste déroulante. Ici, nous voulons la liste déroulante des noms d’étudiants dans ‘I3’. Ensuite, allez dans l’onglet ‘Données’ et cliquez sur le bouton ‘Validation des données’ dans le groupe Outils de données.

Dans la boîte de dialogue de validation des données, accédez à l’onglet Paramètres et choisissez “Liste” dans le menu déroulant “Autoriser :”.

Ensuite, cliquez sur le champ ‘Source’ et sélectionnez la plage avec les noms d’étudiants : ‘$A$2:$A$22’.

Ensuite, assurez-vous que les options “Ignorer le vide” et “Liste déroulante dans la cellule” sont sélectionnées et cliquez sur “OK”.

Cela créera une liste déroulante dans la cellule sélectionnée, comme indiqué ci-dessous. N’oubliez pas que le menu déroulant ne sera visible que lorsque vous sélectionnez cette cellule. Vous pouvez sélectionner la cellule et cliquer sur le bouton déroulant pour afficher la liste des noms.

Maintenant, répétez les mêmes étapes pour créer une liste déroulante pour les sujets. Pour ce faire, sélectionnez une cellule dans laquelle vous voulez la liste déroulante “Sujets”. Ensuite, allez dans l’onglet ‘Données’ et cliquez sur le bouton ‘Validation des données’ dans le groupe Outils de données.

Dans la boîte de dialogue de validation des données, choisissez l’option “Liste” dans la liste déroulante “Autoriser :” de l’onglet Paramètres. Après cela, cliquez sur le champ Source et sélectionnez la plage avec les sujets ($B$1:$E$1). Ensuite, cliquez sur ‘OK’.

Cela créera une liste déroulante pour les sujets dans la cellule sélectionnée.

Maintenant, sélectionnez le nom de l’étudiant et le sujet dans ces listes déroulantes.

Après cela, vous devez entrer une formule qui obtient les valeurs d’entrée à partir des listes déroulantes. Pour cela, essayez la formule ci-dessous, qui est presque la même que celle que nous avons utilisée dans l’exemple de recherche bidirectionnelle dynamique :

=INDEX(B2:E22,MATCH(J3,A2:A22,0),MATCH(J4,B1:E1,0))

Désormais, vous n’avez plus besoin de coder en dur les valeurs de recherche dans la formule ou dans les cellules d’entrée, vous pouvez simplement sélectionner le nom et le sujet dans les listes déroulantes et les résultats seront automatiquement mis à jour.

Recherche à trois voies avec la formule INDEX/MATCH (formulaire de référence)

Vous pouvez également utiliser la formule INDEX et MATCH pour récupérer une valeur avec une recherche à trois voies. Dans l’exemple ci-dessus, nous avons utilisé une recherche dynamique bidirectionnelle (deux valeurs de recherche) pour obtenir les scores des élèves à partir d’un tableau.

Dans les exemples ci-dessus, nous avons utilisé un tableau avec les scores des élèves dans différentes matières. Ceci est un exemple de recherche bidirectionnelle car nous utilisons deux variables pour récupérer le score (le nom de l’étudiant et le sujet).

Exemple:

Mais que se passe-t-il si vous souhaitez récupérer le score d’un étudiant dans une certaine matière à partir d’un semestre spécifique ? Par exemple, supposons que vous ayez trois tables (chacune pour un semestre différent) avec des listes d’étudiants et leurs scores sur différentes matières.

Désormais, vous pouvez utiliser la combinaison d’INDEX et de MATCH pour récupérer le score d’un étudiant pour une matière particulière à partir du semestre spécifique à l’aide de la recherche à trois voies. Pour une recherche à trois voies, vous avez besoin de trois entrées, le numéro de ligne (nom de l’étudiant), le numéro de colonne (sujet) et le numéro de zone (semestre).

Tout d’abord, nous devons créer trois menus déroulants, un pour les noms des étudiants, un autre pour les matières et un autre pour les semestres. Créez les listes déroulantes des étudiants et des sujets, de la même manière que vous l’avez fait pour l’exemple précédent. Pour la liste déroulante des semestres, vous devez saisir les noms des semestres dans une plage vide et l’utiliser pour créer une liste déroulante.

Pour ce faire, entrez les noms des semestres dans une plage de cellules, puis sélectionnez la cellule dans laquelle vous souhaitez que la liste déroulante et accédez à Données -> Outils de données -> Validation des données.

Dans la boîte de dialogue Validation des données, choisissez Liste des critères de validation, sélectionnez la plage avec les noms de semestre et cliquez sur “OK”.

Maintenant, le menu déroulant du semestre est créé.

De même, créez des listes déroulantes pour les noms et les matières des étudiants. Ensuite, sélectionnez une cellule où vous voulez la sortie et entrez la formule suivante :

=INDEX((B3:E11,B15:E23,J3:M9),MATCH(J16,A3:A11,0),MATCH(J17,B2:E2,0),IF(J15="Semester 1",1,IF(J15="Semester 2",2,3)))

Puisqu’il s’agit d’une grande formule, laissez-nous la décomposer pour vous :

  • (B3:E11,B15:E23,J3:M9)sont la référence pour les tableaux (tableau pour chaque semestre). Lorsque vous spécifiez plusieurs tableaux, ils doivent être placés entre parenthèses.
  • MATCH(J16,A3:A11,0) est la formule MATCH utilisée pour trouver le numéro de ligne du nom de l’élève dans la liste déroulante de la cellule J16.
  • MATCH(J15,B2:E2,0) est la formule MATCH utilisée pour trouver le numéro de colonne du sujet dans la liste déroulante de la cellule J17.
  • IF(J15="Semester 1",1,IF(J15="Semester 2",2,3)))est l’argument du numéro de zone qui informe la fonction INDEX de la table dans laquelle rechercher la valeur. Si ‘Semester 1’ est sélectionné dans la liste déroulante, il renvoie 1. Si c’est faux, il vérifie ‘Semester 2’ et renvoie 2 et si c’est également faux, il renvoie 3.

Ensuite, choisissez le semestre, l’étudiant et le sujet dans les menus déroulants pour récupérer la note d’un étudiant particulier pour un sujet particulier dans le semestre sélectionné.

Le résultat:

Maintenant, chaque fois que vous choisissez une option différente dans la liste déroulante, la sortie s’ajustera automatiquement.

Recherche à gauche en utilisant INDEX et MATCH

Contrairement à la fonction VLOOKUP, les fonctions INDEX et MATCH peuvent regarder vers la gauche et extraire la valeur du côté gauche de la colonne contenant la valeur de recherche.

Par exemple, dans l’ensemble de données ci-dessous, nous avons la liste des montagnes, leur hauteur et leur emplacement. Maintenant, nous avons l’emplacement comme valeur de recherche et nous voulons savoir quelle montagne s’y trouve. Voici comment vous pouvez le faire avec le combo INDEX MATCH en regardant de droite à gauche.

Sélectionnez la cellule où vous voulez la sortie et entrez la formule ci-dessous :

=INDEX(A2:C22,MATCH(F5,C2:C22,0),1)

Dans la formule ci-dessus, la fonction MATCH recherche la valeur de recherche (F5) dans la plage C2:C22, et lorsqu’une correspondance est trouvée, elle récupère la valeur correspondante dans la 1ère colonne à gauche.

Recherche sensible à la casse avec INDEX et MATCH

Par défaut, la combinaison INDEX et MATCH n’est pas sensible à la casse. Peu importe la casse de la valeur de recherche, elle renvoie la même sortie. Par exemple, si vous recherchez ‘Dexter’, ‘dexter’ ou ‘dEXteR’, toutes les valeurs de recherche renverront le même numéro de position.

Toutefois, si vous disposez d’une liste des résultats des tests des étudiants et de plusieurs étudiants portant le même nom, vous pouvez utiliser la fonction EXACT dans la fonction INDEX pour distinguer les caractères majuscules et minuscules.

Pour effectuer une recherche sensible à la casse à l’aide de INDEX et MATCH, utilisez la formule ci-dessous :

=INDEX(B2:E22,MATCH(TRUE,EXACT(J5,A2:A22),0),2)

Ceci est une formule matricielle, alors entrez la formule ci-dessus et appuyez sur CtrlShiftEntersinon vous obtiendrez N/A ! Erreur.

Le tableau ci-dessous contient trois Evans (Evan, pair, EVAN). La fonction Exact vérifie chaque valeur dans la plage A2:A22 par rapport à la valeur de recherche dans la cellule J5 (EVAN) pour une correspondance exacte. Lorsqu’une correspondance exacte est trouvée, elle renvoie TRUE et la fonction MATCH renvoie cette position comme numéro de ligne (13). Et le numéro de colonne est spécifié dans le dernier argument comme ‘2’. En conséquence, nous obtenons le score d’EVAN en chimie à 59.

Bien que nous ayons utilisé une référence de cellule pour la valeur de recherche, vous pouvez également coder en dur la valeur de recherche directement dans la formule.

Recherche de critères multiples avec INDEX et MATCH

Avec les fonctions INDEX et MATCH, vous pouvez effectuer une recherche basée sur plusieurs critères. Dans cette méthode, pour rechercher une valeur, la fonction MATCH doit répondre aux critères dans 2 colonnes ou plus en même temps.

Par exemple, si vous avez une grande base de données et que vous souhaitez rechercher le nom “John”. Mais le problème est que plusieurs enregistrements ont le même prénom “John”. Cela pourrait causer des problèmes car vous pourriez récupérer la mauvaise valeur. Pour résoudre ce problème, vous pouvez utiliser une fonction INDEX et MATCH qui peut rechercher une valeur qui correspond à plusieurs colonnes (plusieurs critères) et récupérer la valeur correspondante.

La syntaxe pour INDEX MATCH avec plusieurs critères : 

=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))

où,

  • return_range est la plage à partir de laquelle la valeur sera récupérée
  • critère1, critère2, … sont les critères qui doivent être remplis
  • gamme1, gamme2, …  sont les gammes sur lesquelles les critères requis doivent être testés.

Exemple:

Dans l’ensemble de données ci-dessous, nous souhaitons récupérer le salaire de ‘Anne’ mais nous ne pouvons pas rechercher ‘Anne’ car il existe trois enregistrements différents avec ‘Anne’ comme prénom.

La formule ci-dessous recherche et renvoie le salaire pour l’enregistrement dont le prénom est “Anne” et le nom de famille “Ecosse” (plusieurs critères) :

=INDEX(C2:C20,MATCH(1,(F4=A2:A20)*(F5=B2:B20),0))

Il s’agit d’une formule matricielle, assurez-vous donc de l’exécuter en appuyant sur CtrlShiftEnter.

Ici, la fonction MATCH recherche le prénom ‘Anne’ (F4) dans la colonne A2:A20 et le nom ‘Scotland’ (F5) dans la colonne B2:B20. Si les deux colonnes correspondent aux valeurs de recherche spécifiées, il renvoie le salaire correspondant de la colonne C2:C20.

Trouvez la correspondance la plus proche avec INDEX et MATCH

Trouver la valeur exacte avec INDEX et MATCH est facile, mais trouver une correspondance proche d’une valeur spécifique dans la plage est un peu compliqué et implique deux autres fonctions ABS et MIN avec INDEX et MATCH.

Par exemple, si vous avez une liste d’événements et leurs dates dans un tableau et que vous souhaitez rechercher un événement le plus proche d’une donnée spécifique, vous pouvez utiliser l’exemple suivant.

Pour trouver la correspondance la plus proche d’une valeur spécifique dans une colonne, utilisez la syntaxe de formule ci-dessous :

{=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))}

  • data est la plage des valeurs d’entrée.
  • valueest la valeur d’entrée dont la sortie sera la plus proche.
  • MINLa fonction est utilisée pour renvoyer la plus petite valeur du tableau de valeurs de données.
  • ABSrenvoie la valeur absolue d’un nombre.
  • 1 (exact ou suivant le plus petit) ou   (correspondance exacte) ou  -1 (exact ou suivant le plus grand)

Exemple:

Supposons que vous ayez l’ensemble de données ci-dessous et que vous souhaitiez rechercher la date la plus proche de la date donnée et récupérer le nom de la ville correspondante.

Pour trouver la date la plus proche du 25 mars 2020 dans la plage, essayez la formule ci-dessous :

=INDEX(A2:A23,MATCH(MIN(ABS(B2:B23-G6)),ABS(B2:B23-G6),0))

Ensuite, appuyez sur CtrlShiftEnterpour exécuter la formule sous forme de formule matricielle.

Dans la formule ci-dessus, ABS(B2:B23-G6)trouve la différence entre le tableau de recherche (B2:B23) et la date donnée en soustrayant toutes les valeurs de date de la date spécifiée dans la cellule G6. Cela crée un tableau renvoyé avec les valeurs soustraites. Ensuite, MIN(ABS(B2:B23-G6)trouve la valeur fermée à partir des valeurs soustraites. Ensuite, la fonction MATCH trouve la position de la valeur minimale fermée dans la constante matricielle et ce numéro de position est renvoyé à la fonction INDEX en tant que numéro de ligne.

Par conséquent, la fonction INDEX renvoie le nom de la ville correspondant à la date la plus proche.

Correspondance générique avec INDEX et MATCH

Les caractères génériques peuvent être utilisés dans la combinaison de fonctions INDEX et MATCH peuvent être utilisés pour trouver une valeur avec une correspondance partielle. Cela ne peut fonctionner que lorsque match_type est défini sur ‘0’ et que la valeur de recherche est une chaîne de texte dans la fonction MATCH. Il existe des caractères génériques que vous pouvez utiliser dans la fonction MATCH : un astérisque (*) et un point d’interrogation (?).

  • Le point d’interrogation (?)  est utilisé pour faire correspondre n’importe quel caractère ou lettre avec la chaîne de texte.
  • L’astérisque (*)  est utilisé pour faire correspondre n’importe quel nombre de caractères avec la chaîne.

Par exemple, vous trouverez ci-dessous un ensemble de données de produits, leurs quantités et leurs prix.

Supposons que vous souhaitiez obtenir le prix d’un produit spécifique mais que vous ne connaissiez que le nom partiel du produit. Vous pouvez toujours obtenir les bonnes données en utilisant un caractère générique avec la valeur de recherche. Vous pouvez utiliser le caractère générique astérisque (*) pour faire correspondre n’importe quel nombre de caractères tandis qu’un point d’interrogation (?) est utilisé pour faire correspondre n’importe quel caractère unique.

Astérisque (*)

Vous pouvez essayer la formule ci-dessous pour y parvenir :

=INDEX(C2:C19,MATCH("Canon*",A2:A19,0),1)

ou

=INDEX(C2:C19,MATCH(F4&"*",A2:A19,0),1)

Où la fonction MATCH recherche la valeur qui commence par ‘Canon’ suivie de l’un des caractères, dans la colonne Product (A2:A19). Cela signifie que “Canon* dans l’argument de recherche désigne toute chaîne de texte commençant par le mot “Canon” et pouvant être suivi de n’importe quel nombre de caractères.

Dans le tableau ci-dessous, nous avons ‘Canon PC940 Copier’ dans la plage, il renvoie donc le numéro de ligne (12) à la fonction INDEX. Ensuite, la fonction INDEX récupère la valeur de la 12e ligne de la plage de prix (C2:C19).

Point d’interrogation (?)

Vous pouvez utiliser le caractère générique point d’interrogation (?) pour représenter n’importe quel caractère manquant dans la chaîne.

Pour trouver une valeur qui correspond à la chaîne de texte avec n’importe quel caractère unique (à la place du caractère générique) :

=INDEX(C2:C19,MATCH("Xerox 19?0",A2:A19,0),1)

Ici, la formule MATCH recherche le Xerox 19?0dans la plage A2:A19 et renvoie la position de ‘Xerox 1980’, car elle a la chaîne avec ‘8’ à la place du caractère générique. La position de la chaîne est 3, qui est transmise à la fonction INDEX pour extraire le prix (44,28) de la plage C2: C19.

Si vous avez aimé cet article, veuillez- vous abonner à notre communauté reddit pour en discuter. Vous pouvez également nous retrouver sur Twitter et Facebook .

5/5 - (1 vote)
READ  5 meilleures tasses de raccourcis Excel pour les passionnés d'Excel