Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment utiliser INDEX et MATCH ensemble dans Excel

Lorsque vous travaillez avec des tableaux Excel, vous pouvez constamment trouver des situations dont vous avez besoin pour rechercher une valeur. Dans ce didacticiel, nous allons vous montrer comment appliquer la combinaison des fonctions INDEX et MATCH pour effectuer des recherches horizontales et verticales, des recherches bidirectionnelles, des recherches sensibles à la casse et des recherches répondant à plusieurs critères.

Que font les fonctions INDEX et MATCH dans Excel

Comment utiliser les fonctions INDEX et MATCH ensemble


Que font les fonctions INDEX et MATCH dans Excel

Avant d'utiliser les fonctions INDEX et MATCH, assurons-nous de savoir comment INDEX et MATCH peuvent nous aider à rechercher des valeurs en premier.

Utilisation de la fonction INDEX dans Excel

Le INDEX La fonction dans Excel renvoie la valeur à un emplacement donné dans une plage spécifique. La syntaxe de la fonction INDEX est la suivante :

=INDEX(array, row_num, [column_num])
  • tableau (obligatoire) fait référence à la plage à partir de laquelle vous souhaitez renvoyer la valeur.
  • row_num (obligatoire, sauf si column_num est présent) fait référence au numéro de ligne du tableau.
  • num_colonne (facultatif, mais obligatoire si row_num est omis) fait référence au numéro de colonne du tableau.

Par exemple, pour savoir le score de l'examen final de Jeff, le 6ème élève de la liste, vous pouvez utiliser la fonction INDEX comme ceci :

=INDICE(E2:E11, 6) >>> retours 60

indice excel correspondance 01

Remarque : la gamme E2: E11 est l'endroit où l'examen final est inscrit, tandis que le nombre 6 trouve la note d'examen du 6e étudiant.

Ici, faisons un petit test. Pour la formule =INDICE(B2:E2,3), quelle valeur renverra-t-il ? --- Oui, il reviendra Chine, les 3rd valeur dans la plage donnée.

Il faut maintenant savoir que la fonction INDEX peut parfaitement fonctionner avec des plages horizontales ou verticales. Mais que se passe-t-il si nous en avons besoin pour renvoyer une valeur dans une plage plus large avec plusieurs lignes et colonnes ? Eh bien, dans ce cas, nous devrions appliquer à la fois un numéro de ligne et un numéro de colonne. Par exemple, pour savoir le pays d'où vient Emily avec INDEX, nous pouvons localiser la valeur avec un numéro de ligne de 8 et un numéro de colonne de 3 dans les cellules de B2 à E11 comme ceci :

=INDICE(B2:E11,8,3) >>> retours Chine

indice excel correspondance 02

D'après les exemples ci-dessus, à propos de la fonction INDEX dans Excel, vous devez savoir que :

  • La fonction INDEX peut fonctionner avec des plages verticales et horizontales.
  • La fonction INDEX n'est pas sensible à la casse.
  • Le numéro de ligne précède le numéro de colonne (si vous avez besoin des deux numéros) dans la formule INDEX.

Cependant, pour une très grande base de données avec plusieurs lignes et colonnes, il n'est certainement pas pratique pour nous d'appliquer la formule avec un numéro de ligne et un numéro de colonne exacts. Et c'est là que nous devons combiner l'utilisation de la fonction MATCH.

Voyons d'abord les bases de la fonction MATCH.


Utilisation de la fonction MATCH dans Excel

La fonction MATCH dans Excel renvoie une valeur numérique, l'emplacement d'un élément spécifique dans la plage donnée. La syntaxe de la fonction MATCH est la suivante :

=MATCH(lookup_value, lookup_array, [match_type])
  • tableau_recherche (obligatoire) fait référence à la plage de cellules dans laquelle vous souhaitez rechercher MATCH.
  • Type de match (optionnel), 1, 0 or -1:
  • 1(par défaut), MATCH trouvera la plus grande valeur inférieure ou égale à la valeur de recherche. Les valeurs dans le tableau_recherche doivent être placés dans l'ordre croissant.
  • 0, MATCH trouvera la première valeur exactement égale à la valeur de recherche. Les valeurs dans le tableau_recherche peut être dans n'importe quel ordre. (Pour les cas où le type de correspondance est défini sur 0, vous pouvez utiliser des caractères génériques.)
  • -1, MATCH trouvera la plus petite valeur supérieure ou égale à la valeur de recherche. Les valeurs dans le tableau_recherche doivent être placés par ordre décroissant.

Par exemple, pour savoir la position de Vera dans la liste des noms, vous pouvez utiliser la formule MATCH comme ceci :

= MATCH("vera",C2:C11,0) >>> retours 4

indice excel correspondance 03

Remarque : La fonction MATCH n'est pas sensible à la casse. Le résultat « 4 » indique que le nom « Vera » est en 4ème position de la liste. Le « 0 » dans la formule est le type de correspondance qui trouvera la première valeur dans le tableau de recherche qui correspond exactement à la valeur de recherche « Vera ».

À savoir la position du score « 96 » dans la rangée de B2 à E2, vous pouvez utiliser MATCH comme ceci :

= CORRESPONDANCE(96,B2:E2,0) >>> retours 4

indice excel correspondance 04

Ce que nous devons savoir sur la fonction MATCH dans Excel :

  • La fonction MATCH renvoie la position de la valeur de recherche dans le tableau de recherche, pas la valeur elle-même.
  • La fonction MATCH renvoie la première correspondance en cas de doublons.
  • Tout comme la fonction INDEX, la fonction MATCH peut également fonctionner avec des plages verticales et horizontales.
  • MATCH n'est pas non plus sensible à la casse.
  • Si la valeur de recherche de la formule MATCH est sous forme de texte, placez-la entre guillemets.

Maintenant que nous connaissons les utilisations de base des fonctions INDEX et MATCH dans Excel, retroussons nos manches et préparons-nous à combiner les deux fonctions.


Comment utiliser les fonctions INDEX et MATCH ensemble

Dans cette partie, nous parlerons de différentes circonstances pour utiliser les fonctions INDEX et MATCH pour répondre à différents besoins.

Exemple pour combiner INDEX et MATCH

Veuillez consulter l'exemple ci-dessous pour comprendre comment combiner les fonctions INDEX et MATCH :

Par exemple, pour savoir La note finale d'Evelyn à l'examen, il faut utiliser la formule :

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> retours 90

indice excel correspondance 05

Eh bien, puisque la formule peut sembler compliquée, passons en revue chaque partie.

indice excel correspondance 06

Comme vous pouvez le voir ci-dessus, le grand INDEX la formule contient trois arguments :

  • tableau: A2: D11 indique à INDEX de renvoyer la valeur correspondante des cellules via A2 à D11.
  • row_num: MATCH("evelyn",B2:B11,0) indique à INDEX la ligne exacte de la valeur.
  • À propos de la formule MATCH, nous pouvons l'expliquer comme suit : pour renvoyer la position de la première valeur qui est exactement égale à « evelyn » dans les cellules de B2 à B11 dans une valeur numérique, qui est 5.
  • num_colonne: MATCH("examen final",A1:D1,0) indique à INDEX la colonne exacte de la valeur.
  • À propos de la formule MATCH, nous pouvons l'expliquer comme suit : pour renvoyer la position de la première valeur qui est exactement égale à « examen final » dans les cellules de A1 à D1 dans une valeur numérique, qui est 4.

Ainsi, vous pouvez voir la grande formule aussi simple que celle que nous avons montrée ci-dessous :

= INDEX (A2: D11,5,4)

Dans l'exemple, nous avons utilisé des valeurs codées en dur, "evelyn" et "final exam". Cependant, dans une si grosse formule, nous ne voulons pas de valeurs codées en dur car nous devrons les changer chaque fois que nous chercherons quelque chose de nouveau. Dans de telles circonstances, nous pouvons utiliser des références de cellule pour rendre la formule dynamique comme ceci :

= INDEX (A2: D11,RENCONTRE(G2,B2:B11,0),RENCONTRE(F3,A1:D1,0))

indice excel correspondance 07


INDEX et MATCH pour appliquer une recherche à gauche

Maintenant, disons que vous avez besoin de connaître la classe d'Evelyn, comment pouvons-nous utiliser INDEX et MATCH pour connaître la réponse ? Si vous avez fait attention, vous devriez remarquer que la colonne de classe se trouve à gauche de la colonne de nom, ce qui dépasse la capacité d'une autre fonction de recherche puissante d'Excel, la RECHERCHEV.

En fait, la capacité de recherche à gauche se trouve être l'un des aspects où la combinaison d'INDEX et de MATCH est supérieure à VLOOKUP.

À savoir La classe d'Evelyne, tout ce que vous avez à faire est de changer la valeur de la cellule F3 en "Classe", et d'utiliser la même formule que celle indiquée ci-dessus, les fonctions INDEX et MATCH vous donneront alors la réponse immédiatement :

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> retours A

indice excel correspondance 08

Si vous avez installéLED Kutools pour Excel, un complément Excel professionnel développé par notre équipe, vous pouvez également appliquer une recherche à gauche pour des valeurs spécifiées avec son RECHERCHE de droite à gauche fonctionnalité en quelques clics. Pour implémenter la fonctionnalité, veuillez vous rendre sur le Kutools onglet dans votre excel, trouvez le Laits en poudre groupe, et cliquez sur RECHERCHE de droite à gauche dans la liste déroulante de Super recherche. Vous verrez une boîte de dialogue contextuelle comme celle-ci :

indice excel correspondance 09

Cliquez ici pour obtenir des étapes concrètes pour appliquer la fonction de recherche de gauche avec Kutools for Excel.


INDEX et MATCH pour appliquer une recherche bidirectionnelle

Maintenant, êtes-vous en mesure de créer la formule de combinaison INDEX et MATCH avec des valeurs de recherche dynamiques pour appliquer des recherches bidirectionnelles ? Entraînons-nous à créer des formules dans les cellules G3, G4 et G5, comme indiqué ci-dessous :

indice excel correspondance 10

Voici les réponses :

Cellule G3 : =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Cellule G4 : =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Cellule G5 : =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Remarque : après avoir appliqué les formules, vous pouvez facilement obtenir les informations de tous les élèves en modifiant le nom dans la cellule G2.


INDEX et MATCH pour appliquer une recherche sensible à la casse

D'après les exemples ci-dessus, nous savons que les fonctions INDEX et MATCH ne sont pas sensibles à la casse. Cependant, dans les cas où vous avez besoin de votre formule pour distinguer les caractères majuscules et minuscules, vous pouvez ajouter EXACT fonction à vos formules comme ceci:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Remarque : Il s'agit d'une formule matricielle qui vous oblige à entrer avec Ctrl + Maj + Entrée. Une paire d'accolades apparaîtra alors dans la barre de formule.

Par exemple, pour savoir La note d'examen de JIMMY, utilisez les fonctions comme ceci :

indice excel correspondance 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> retours 86

Ou vous pouvez utiliser des références de cellule :

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> retours 86
Remarque : n'oubliez pas d'entrer avec Ctrl + Maj + Entrée.


INDEX et MATCH pour appliquer une recherche avec plusieurs critères

Lorsqu'il s'agit d'une grande base de données avec plusieurs colonnes et légendes de lignes, il est toujours difficile de trouver quelque chose qui répond à plusieurs conditions. Dans ce cas, veuillez consulter la formule ci-dessous pour rechercher plusieurs critères :

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Remarque : Il s'agit d'une formule matricielle qui vous oblige à entrer avec Ctrl + Maj + Entrée. Une paire d'accolades apparaîtra alors dans la barre de formule.

Par exemple, pour trouver le score à l'examen final de Coco de la classe A, originaire d'Inde, la formule est la suivante :

indice excel correspondance 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> retours 88
Remarque : n'oubliez pas d'entrer avec Ctrl + Maj + Entrée.

Eh bien, et si vous oubliez constamment d'utiliser Ctrl + Maj + Entrée compléter la formule pour que la formule renvoie des résultats incorrects ? Ici, nous avons une formule plus complexe, avec laquelle vous pouvez simplement compléter avec un simple Entrer clé:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Pour le même exemple ci-dessus pour trouver le score à l'examen final de Coco de la classe A, originaire d'Inde, la formule qui n'a besoin que d'un Entrer le coup est le suivant :

indice excel correspondance 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> retours 88

Ici, nous n'utiliserons pas de valeurs codées en dur, car nous voudrons une formule universelle dans le cas de plusieurs critères. Ce n'est qu'ainsi que nous pouvons facilement obtenir le résultat souhaité en modifiant les valeurs des cellules G2, G3, G4 dans l'exemple ci-dessus.

Avec Kutools pour Excel»s Fonctionnalité de recherche multi-conditions, vous pouvez rechercher des valeurs spécifiques avec plusieurs critères en quelques clics. Pour implémenter la fonctionnalité, veuillez vous rendre sur le Kutools onglet dans votre excel, trouvez le Laits en poudre groupe, et cliquez sur Recherche multi-conditions dans la liste déroulante de Super recherche. Vous verrez alors une boîte de dialogue contextuelle comme indiqué ci-dessous :

indice excel correspondance 14

Cliquez ici pour obtenir des étapes concrètes pour appliquer la fonction de recherche multi-conditions avec Kutools for Excel.


INDEX et MATCH pour appliquer une recherche sur plusieurs colonnes

Si nous avons une feuille de calcul Excel avec différentes colonnes partageant une légende comme indiqué ci-dessous, comment pouvons-nous faire correspondre le nom de chaque élève avec sa classe avec INDEX et MATCH ?

indice excel correspondance 15

Ici, laissez-moi vous montrer le chemin pour terminer la tâche avec notre outil professionnel Kutools pour Excel. Avec son Assistant de formule, vous pouvez rapidement associer des étudiants à leurs classes en suivant les étapes ci-dessous :

1. Sélectionnez la cellule de destination où vous souhaitez appliquer la fonction.

2. Sous le Kutools onglet, aller à Aide à la formulecliquez Aide à la formule dans la liste déroulante.

indice excel correspondance 16

3. Choisir Recherche à partir du type de formule, puis cliquez sur Indexer et faire correspondre sur plusieurs colonnes.

indice excel correspondance 17

4. a. Cliquez sur le 1er icône de correspondance d'index excelbouton sur le côté droit de Lookup_col pour sélectionner les cellules à partir desquelles vous souhaitez renvoyer une valeur, c'est-à-dire les noms de classe. (Vous ne pouvez sélectionner qu'une seule colonne ou ligne ici.)
    b. Cliquez sur le 2e icône de correspondance d'index excelbouton sur le côté droit de Table_rng pour sélectionner les cellules pour correspondre aux valeurs dans le sélectionné Lookup_col, c'est-à-dire les noms des élèves.
    c. Cliquez sur le 3e icône de correspondance d'index excelbouton sur le côté droit de Lookup_value pour sélectionner la cellule à rechercher, c'est-à-dire le nom de l'élève que vous souhaitez associer à sa classe.

indice excel correspondance 18

5. Cliquez sur Ok, vous verrez le nom de la classe de Jimmy apparaître dans la cellule de destination.

indice excel correspondance 19

6. Vous pouvez maintenant faire glisser la poignée de remplissage vers le bas pour remplir les classes d'autres étudiants.

indice excel correspondance 20

Cliquez pour télécharger Kutools for Excel pour un essai gratuit de 30 jours.



  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes et conservation des données; Contenu des cellules divisées; Combiner les lignes en double et la somme / moyenne... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Formules favorites et insérer rapidement, Plages, graphiques et images; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Regroupement du tableau croisé dynamique par numéro de semaine, jour de la semaine et plus encore ... Afficher les cellules déverrouillées et verrouillées par différentes couleurs; Mettre en évidence les cellules qui ont une formule / un nom...
onglet kte 201905
  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.