INDEX et EQUIV dans Excel : recherches simples et avancées

Dans Excel, la récupération précise de données spécifiques constitue souvent une nécessité fréquente. Bien que les fonctions INDEX et EQUIV possèdent chacune leurs propres atouts, leur combinaison déverrouille un ensemble d’outils puissants pour la recherche de données. Ensemble, elles permettent une variété de capacités de recherche, allant des recherches horizontales et verticales de base à des fonctionnalités plus avancées telles que les recherches bidirectionnelles, sensibles à la casse ou multicritères. Offrant des capacités améliorées par rapport à RECHERCHEV, l’association d’INDEX et d’EQUIV autorise un éventail plus large d’options de recherche de données. Dans ce tutoriel, explorons en profondeur les possibilités qu’elles peuvent réaliser conjointement.

• Fonction INDEX
• Fonction EQUIV
• Combiner INDEX et EQUIV

• Recherche à deux critères
• Recherche vers la gauche
• Recherche sensible à la casse
• Correspondance la plus proche
• Recherche avec plusieurs critères
• Recherche sur plusieurs colonnes
• Recherche de la première valeur non vide
• Recherche de la première valeur numérique
• Recherche d’associations MAX ou MIN
• Astuce : personnalisez vos propres messages d’erreur #N/A

Comment utiliser INDEX et EQUIV dans Excel

Avant d’utiliser les fonctions INDEX et EQUIV, assurez-vous de bien comprendre comment elles peuvent vous aider à rechercher des valeurs.

Comment utiliser la fonction INDEX dans Excel

La fonction INDEX dans Excel renvoie la valeur située à une position donnée dans une plage spécifique. Sa syntaxe est la suivante :

• matrice (obligatoire) désigne la plage à partir de laquelle vous souhaitez renvoyer une valeur.
• num_ligne(obligatoire, sauf si)num_colonne est présent) fait référence au numéro de ligne de la matrice.
• num_colonne(facultatif, mais obligatoire si)num_ligne est omis) fait référence au numéro de la colonne dans la matrice.

Par exemple, pour connaître le score de Jeff, le 6e élève de la liste, vous pouvez utiliser la fonction INDEX comme suit :

=INDEX(C2:C11,6)

√ Remarque : La plage C2:C11contient les scores, tandis que le nombre 6permet de trouver le score de l’6e élève.

Faisons maintenant un petit test. Pour la formule =INDEX(A1:C1;2), quelle valeur renvoie-t-elle ? — Exactement ! Elle renvoie Date de naissance, soit la 2e valeur de la ligne indiquée.

Nous savons désormais que la fonction INDEX fonctionne parfaitement aussi bien avec des plages horizontales que verticales. Mais que se passe-t-il si nous devons extraire une valeur dans une plage plus étendue, comportant plusieurs lignes et colonnes ? Dans ce cas, il faut indiquer à la fois un numéro de ligne et un numéro de colonne. Par exemple, pour trouver le score de Jeff dans la plage du tableau (au lieu d’une seule colonne), nous pouvons localiser son score grâce à un numéro de ligne de 6 et à un numéro de colonne de 3 dans la plage de cellules allant de A2 à C11, comme suit :

=INDEX(A2:C11,6,3)

Toutefois, dans le cas d’une base de données très volumineuse comportant de nombreuses lignes et colonnes, il n’est certainement pas pratique d’utiliser une formule avec des numéros de ligne et de colonne exacts. C’est précisément à ce moment-là que nous devons combiner l’utilisation de la fonction EQUIV.

Comment utiliser la fonction EQUIV dans Excel

La fonction EQUIV dans Excel renvoie la position numérique d’un élément spécifique au sein d’une plage donnée. Sa syntaxe est la suivante :

• valeur_rech (obligatoire) correspond à la valeur à rechercher dans le tableau_rech.
• tableau_rech (obligatoire) désigne la plage de cellules dans laquelle la fonction EQUIV effectue sa recherche.
• type_equiv(facultatif) :1,0ou -1.
  • 1(par défaut), EQUIV recherche la plus grande valeur inférieure ou égale à la valeur_recherchée. Les valeurs du tableau_recherche doivent être classées par ordre croissant.
  • 0 : EQUIV recherche la première valeur strictement égale à la valeur_recherchée. Les valeurs du tableau_recherche peuvent être dans n’importe quel ordre. (Lorsque le type de correspondance est défini sur 0, vous pouvez utiliser des caractères génériques.)
  • -1, EQUIV recherche la plus petite valeur qui est supérieure ou égale à la valeur_recherchée. Les valeurs du tableau_recherche doivent être classées par ordre décroissant.

Par exemple, pour connaître la position de Vera dans la Liste des noms, vous pouvez utiliser la Distinguer les formules comme suit :

=MATCH("Vera",A2:A11,0)

√ Remarque : Le résultat « 4 » indique que le nom « Vera » apparaît en 4ᵉ position dans la liste.

Maintenant que nous maîtrisons les usages de base des fonctions INDEX et EQUIV dans Excel, passons à l’action et préparons-nous à les combiner.

Comment combiner INDEX et EQUIV dans Excel

Consultez l’exemple ci-dessous pour comprendre comment combiner les fonctions INDEX et EQUIV :

Pour trouver le score d’Evelyn, sachant que les scores aux examens se trouvent dans la 3e colonne, nous pouvons utiliser la fonction EQUIV pour déterminer automatiquement la position de la ligne sans avoir à la compter manuellement. Ensuite, nous employons la fonction INDEX pour récupérer la valeur située à l’intersection de la ligne identifiée et de la 3e colonne :

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Comme cette formule peut paraître un peu complexe, examinons chacune de ses parties.

La formule INDEXcomporte trois arguments :

• num_ligne:EQUIV(«Evelyn»;A2:A11;0)fournit à INDEX la position de la ligne de la valeur «Evelyn» dans la plage A2:A11, qui est5.
• num_colonne : 3 indique à INDEX la 3^e colonne dans laquelle localiser le score au sein de la matrice.
• matrice : A2:C11 indique à INDEX de renvoyer la valeur située à l’intersection de la ligne et de la colonne spécifiées, dans la plage allant de A2 à C11. Le résultat obtenu est finalement 90.

Dans la formule ci-dessus, nous avons utilisé une valeur codée en dur : « Evelyn ». Toutefois, en pratique, les valeurs codées en dur ne sont pas pratiques, car elles doivent être modifiées chaque fois que l’on souhaite rechercher d’autres données, comme le score d’un autre élève. Dans ce cas, on peut utiliser des références de cellule pour créer des formules dynamiques. Par exemple, ici, je vais remplacer « Evelyn » par F2 :

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Exemples avec INDEX et Distinguer les formules

Dans cette section, nous explorerons diverses situations d’utilisation des fonctions INDEX et EQUIV pour répondre à une variété de besoins.

Utiliser INDEX et EQUIV pour effectuer une recherche bidirectionnelle

Dans l’exemple précédent, nous connaissions le numéro de colonne et utilisions une Distinguer les formules pour trouver le numéro de ligne. Mais que faire si nous ignorons également le numéro de colonne ?

Dans de tels cas, nous pouvons effectuer une recherche bidirectionnelle, également appelée recherche matricielle, en combinant deux fonctions EQUIV : l’une pour identifier le numéro de ligne et l’autre pour déterminer le numéro de colonne. Par exemple, pour connaître le score d’Evelyn, utilisez la formule suivante :

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

Utiliser INDEX et EQUIV pour effectuer une recherche vers la gauche

Imaginons maintenant un scénario où vous devez déterminer la classe d’Evelyn. Vous aurez peut-être remarqué que la colonne des classes se trouve à gauche de la colonne des noms, une situation qui dépasse les capacités d’une autre fonction puissante de recherche dans Excel, RECHERCHEV.

En effet, la capacité d’effectuer des recherches vers la gauche constitue l’un des domaines où la combinaison d’INDEX et d’EQUIV surpasse RECHERCHEV.

Pour trouver la classe d’Evelyn, utilisez la formule suivante afin de rechercher Evelyn dans B2:B11 et de récupérer la valeur correspondante dans A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

Utiliser INDEX et EQUIV pour effectuer une recherche sensible à la casse

La fonction EQUIV n’est pas sensible à la casse par défaut. Toutefois, si votre formule doit distinguer les majuscules des minuscules, vous pouvez la renforcer en intégrant la fonction EXACT. En combinant EQUIV avec EXACT au sein d’une formule INDEX, vous effectuez alors une recherche sensible à la casse de manière efficace, comme illustré ci-dessous :

• matrice fait référence à la plage à partir de laquelle vous souhaitez renvoyer une valeur.
• valeur_rech fait référence à la valeur à rechercher en tenant compte de la casse des caractères, dans le tableau_rech.
• tableau_rech fait référence à la plage de cellules dans laquelle EQUIV doit effectuer une comparaison avec la valeur_rech.

Par exemple, pour connaître le score de JIMMY à l’examen, utilisez la formule suivante :

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Remarque : Il s’agit d’une formule matricielle que vous devez valider en appuyant sur Ctrl+Maj+Entrée, sauf dans Excel 365, Excel 2021 et les versions ultérieures.

Utiliser INDEX et EQUIV pour trouver la valeur la plus proche

Dans Excel, vous pouvez être confronté à des situations où vous devez trouver la valeur la plus proche d’une valeur donnée au sein d’un ensemble de données. Dans de tels cas, combiner les fonctions INDEX et EQUIV avec les fonctions ABS et MIN peut s’avérer extrêmement utile.

• matrice fait référence à la plage à partir de laquelle vous souhaitez renvoyer une valeur.
• tableau_rech fait référence à la plage de valeurs dans laquelle vous souhaitez trouver la correspondance la plus proche de la valeur_rech.
• valeur_rech fait référence à la valeur dont vous souhaitez trouver la correspondance la plus proche.

Par exemple, pour déterminer de quel élève le score est le plus proche de 85, utilisez la formule suivante afin de rechercher le score le plus proche de 85 dans C2:C11 et de récupérer la valeur correspondante dans A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Remarque : Il s’agit d’une formule matricielle que vous devez valider en appuyant sur Ctrl+Maj+Entrée, sauf dans Excel 365, Excel 2021 et les versions ultérieures.

Utiliser INDEX et EQUIV pour effectuer une recherche selon plusieurs critères

Pour trouver une valeur répondant à plusieurs conditions, exigeant une recherche dans deux colonnes ou plus, utilisez la formule suivante. Cette formule vous permet d’effectuer une recherche multicritères en précisant diverses conditions dans différentes colonnes, vous aidant ainsi à identifier la valeur souhaitée qui satisfait l’ensemble des critères spécifiés.

√ Remarque : il s’agit d’une formule matricielle que vous devez valider en appuyant sur Ctrl + Maj + Entrée. Une paire d’accolades apparaîtra alors dans la barre de formule.

• matrice fait référence à la plage à partir de laquelle vous souhaitez renvoyer la valeur.
• (valeur_rech=tableau_rech) représente une condition unique. Elle vérifie si une valeur_rech donnée correspond aux valeurs du tableau_rech.

Par exemple, pour trouver le score de Coco de la classe A, dont la date de naissance est le 7/2/2008, vous pouvez utiliser la formule suivante :

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

Utiliser INDEX et EQUIV pour effectuer une recherche dans plusieurs colonnes

Imaginez un scénario dans lequel vous travaillez avec plusieurs colonnes de données. La première colonne sert de clé pour classer les données des autres colonnes. Pour déterminer la catégorie ou la classification d’une entrée spécifique, vous devez effectuer une recherche dans les colonnes de données et l’associer à la clé correspondante dans la colonne de référence.

Par exemple, dans le tableau ci-dessous, comment faire correspondre l’élève Shawn à sa classe respective à l’aide des fonctions INDEX et EQUIV ? Eh bien, cela est possible grâce à une formule, mais celle-ci est assez longue et peut s’avérer difficile à comprendre, sans même parler de la mémoriser ou de la saisir.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

C’est là qu’intervient la fonctionnalité Kutools pour Excel Index et correspondance de plusieurs colonnes. Elle simplifie cette opération en permettant de faire correspondre rapidement et facilement des entrées spécifiques à leurs catégories associées. Pour bénéficier de cet outil puissant et associer Shawn à sa classe en toute simplicité, il vous suffit de télécharger et installer le complément Kutools pour Excel, puis de procéder comme suit :

1. Sélectionnez la cellule de destination dans laquelle vous souhaitez afficher la classe correspondante.
2. Dans l’onglet Kutools, cliquez sur Assistant de formule > Recherche et référence > Index et correspondance de plusieurs colonnes.
3. 
3. Dans la boîte de dialogue qui s’ouvre, procédez comme suit :
   1. Cliquez sur le premier bouton à côté de Lookup_col pour sélectionner la colonne contenant l’information clé que vous souhaitez récupérer, c’est-à-dire les noms de classe. (Vous ne pouvez sélectionner qu’une seule colonne ici.)
   2. Cliquez sur le deuxième bouton à côté de Table_rngpour sélectionner les cellules correspondant aux valeurs de la colonne Lookup_colsélectionnée, c’est-à-dire les noms d’élèves.
   3. Cliquez sur le troisième bouton à côté de Lookup_valuepour sélectionner la cellule contenant le nom de l’élève que vous souhaitez faire correspondre à sa classe — ici, Shawn.
   4. Cliquez sur OK.
   5. 

Résultat

Kutools a automatiquement généré la formule, et vous verrez immédiatement apparaître le nom de la classe de Shawn dans la cellule cible.

Utiliser INDEX et EQUIV pour rechercher la première valeur non vide

Pour récupérer la première valeur non vide, en ignorant les erreurs, à partir d’une colonne ou d’une ligne, vous pouvez utiliser une formule basée sur les fonctions INDEX et EQUIV. Toutefois, si vous ne souhaitez pas ignorer les erreurs présentes dans votre plage, ajoutez la fonction ESTVIDE.

• Obtenir la première valeur non vide dans une colonne ou une ligne en ignorant les erreurs :

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Obtenir la première valeur non vide dans une colonne ou une ligne, y compris les erreurs :

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

Utiliser INDEX et EQUIV pour rechercher la première valeur numérique

Pour récupérer la première valeur numérique d’une colonne ou d’une ligne, utilisez une formule reposant sur les fonctions INDEX, EQUIV et ESTNUM.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

Utiliser INDEX et EQUIV pour rechercher les associations MAX ou MIN

Si vous devez récupérer une valeur associée à la valeur maximale ou Valeur minimale dans une plage, vous pouvez combiner les fonctions MAX ou MIN avec les fonctions INDEX et EQUIV.

• INDEX et EQUIV pour récupérer une valeur associée au Valeur maximale :
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX et EQUIV pour récupérer une valeur associée au Valeur minimale :
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Les formules ci-dessus comportent deux arguments :
• • tableau désigne la plage à partir de laquelle vous souhaitez récupérer l’information associée.
  • plage_recherche représente l’ensemble des valeurs à examiner ou à rechercher selon des critères spécifiques, c’est-à-dire la valeur maximale ou minimale.

Par exemple, si vous souhaitez déterminer qui a obtenu le score le plus élevé, utilisez la formule suivante :

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Astuce : personnalisez vos propres messages d’erreur #N/A

Lorsque vous utilisez les fonctions INDEX et EQUIV d’Excel, il peut arriver que l’erreur #N/A s’affiche lorsqu’aucun résultat ne correspond à votre recherche. Par exemple, dans le tableau ci-dessous, une erreur #N/A apparaît lorsque vous tentez de trouver le score d’une élève nommée Samantha, car elle n’est pas présente dans l’ensemble de données.

Afin de rendre vos feuilles de calcul plus conviviales, vous pouvez personnaliser ce message d’erreur en encapsulant votre formule INDEX Distinguer les formules dans la fonction SIERREUR :

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Ce qui précède couvre l’ensemble des fonctionnalités des fonctions INDEX et EQUIV dans Excel. J’espère que ce tutoriel vous sera utile ! Si vous souhaitez découvrir davantage d’astuces et de conseils Excel, cliquez ici pour accéder à notre vaste collection de milliers de tutoriels.