I. Introduction▲
INDEX et EQUIV sont deux fonctions de la catégorie "Recherche".
INDEX renvoie une valeur ou la référence de la cellule au croisement d'une ligne et
d'une colonne dans une plage de données.
EQUIV renvoie la position de la valeur cherchée dans le tableau.
Contrairement aux fonctions RECHERCHEV et RECHERCHEH qui ne renvoient que la première
donnée trouvée, les fonctions INDEX et EQUIV, utilisées ensemble, permettent d'extraire
plusieurs éléments répondant à la recherche dans un tableau.
Tous les exemples présentés dans le tutoriel sont disponibles dans le classeur téléchargeable
en bas de cette page.
II. La fonction Index▲
II-A. Description▲
La fonction INDEX peut renvoyer une valeur (formule matricielle) ou la référence de la
cellule (formule référentielle) à l'intersection d'une ligne et d'une colonne dans une plage
de cellules.
Définition des plages et constantes matricielles :
Une plage matricielle partage une même formule.
Par exemple, dans une nouvelle feuille de calcul, sélectionnez la cellule A1.
Collez cette formule
=LIGNE(Feuil1!$B$2:$B$20)-LIGNE(Feuil1!$B$2)
Sélectionnez la plage A1:A19
Appuyez sur la touche clavier F2
Pour valider la formule matricielle, appuyez sur CTRL+MAJ+ENTRÉE.
Une constante matricielle est un groupe de constantes qui sert d'argument dans la formule.
Dans l'exemple ci-dessous, la constante matricielle {"A";"B";"C";"D";"E";"F"} affiche une lettre
aléatoire entre A et F :
=INDEX({"A";"B";"C";"D";"E";"F"};(ENT(ALEA()*6+1)))
La fonction INDEX affiche la valeur d'erreur #REF! si les arguments no_lig et no_col font
référence à une cellule située en dehors du tableau.
II-A-1. La forme matricielle▲
Renvoie une donnée d'un tableau ou d'une matrice à partir des numéros de ligne et
de colonne indiqués.
La syntaxe est
INDEX(tableau;no_ligne;no_col)
tableau définit la plage de données où va être effectuée
la recherche
no_ligne définit la ligne du tableau
no_col définit la colonne du tableau
Par exemple :
En spécifiant un numéro de ligne et de colonnes d'un tableau, vous pouvez
récupérer le contenu de la cellule à cette intersection.
Au moins un des deux arguments no_ligne ou no_col doit être spécifié.
Si les arguments no_lig et no_col sont tous les deux utilisés, la fonction INDEX renvoie
la valeur de la cellule située à l'intersection des numéros de ligne et de colonne.
Si l'argument tableau contient une seule ligne ou colonne, l'argument no_lig ou no_col
correspondant est facultatif.
Si vous spécifiez la valeur 0 pour l'argument no_lig ou no_col, la fonction INDEX renvoie
respectivement la matrice des valeurs de la colonne ou de la ligne entière.
II-A-2. La forme référentielle▲
Dans ce cas, la fonction INDEX renvoie la référence de la cellule située à l'intersection
d'une ligne et d'une colonne déterminées.
Cette référence est utilisable dans d'autres formules.
L'exemple suivant renvoie l'adresse de la cellule à l'intersection de la 5ième ligne et de la
2ième colonne dans la plage A1:B10 (soit $B$5).
=CELLULE("adresse";INDEX(A1:B10;5;2))
La syntaxe est
INDEX(réf;no_lig;no_col;no_zone)
réf est une référence à une ou plusieurs plages de cellules.
Placez l'argument réf entre parenthèses lorsque vous utilisez des plages multiples.
Vous pouvez choisir la sélection sur laquelle la fonction doit être exécutée
grâce à l'argument no_zone.
Cet exemple renvoie la référence à l'intersection de la 5ième ligne et de la 1ière colonne
dans la 3ième plage de cellules (G1:H10) :
=INDEX((A1:B10;D1:E10;G1:H10);5;1;3)
no_zone définit la plage de l'argument réf pour laquelle
l'intersection de no_col et no_lig doit être renvoyée.
La première zone sélectionnée ou
entrée porte le numéro 1, la deuxième, le numéro 2 et ainsi de suite.
Si l'argument no_zone est omis, la fonction INDEX utilise la zone numéro 1.
Par exemple, si l'argument réf décrit les cellules (A1:B10;D1:E10;G1:H10), l'argument
no_zone 1 correspond à la plage A1:B10, l'argument no_zone 2, à la plage D1:E10
et l'argument no_zone 3, à la plage G1:H10.
Si chaque zone de l'argument réf contient une seule ligne ou colonne, l'argument no_lig
ou no_col, respectivement, devient facultatif. Par exemple, dans le cas d'un argument
référence à une seule ligne, utilisez la fonction INDEX(réf;;no_col).
no_lig est le numéro de la ligne de référence à partir de laquelle une référence doit
être renvoyée.
no_col est le numéro de la colonne de réf à partir de laquelle
une référence doit être renvoyée.
Si vous spécifiez la valeur 0 (zéro) pour l'argument no_lig ou no_col, la fonction
INDEX renvoie respectivement la référence de la colonne ou de la ligne entière.
Si les arguments no_lig et no_col sont omis, la fonction INDEX renvoie la zone de l'argument
référence définie par l'argument no_zone.
II-B. Exemples▲
II-B-1. Afficher une donnée aléatoire▲
Cet exemple affiche une donnée aléatoire parmi les cellules de la plage A10:A15.
=INDEX(A10:A15;(ENT(ALEA()*6+1)))
Appuyez sur la touche F9 pour relancer le calcul.
II-B-2. Renvoyer la dernière donnée saisie dans la colonne A▲
La formule fonctionne également s'il y a des cellules vides entre les données.
Cet exemple suppose qu'il y a moins de 1000 données dans la colonne A.
(formule matricielle à valider par Ctrl+Maj+Entree).
=INDEX(A1:A1000;MAX(NON(ESTVIDE(A1:A1000))*LIGNE(A1:A1000));1)
II-B-3. Renvoyer la dernière donnée saisie dans la ligne 1▲
Cette formule renvoie la donnée la plus proche, à gauche de la cellule AA1
(formule matricielle à valider par Ctrl+Maj+Entrée)
=INDEX(A1:Z1;MAX(COLONNE(A1:Z1)*(A1:Z1<>"")))
II-B-4. Extraire une donnée sur 5 dans la plage A1:A100▲
Saisissez la formule dans la première ligne d'une autre colonne puis utilisez la poignée
de recopie vers le bas.
=SI(LIGNE()=1;A1;INDEX($A$2:$A$100;(LIGNE()-2)*5+5))
II-B-5. Extraire les données d'une liste sans doublons▲
Si par exemple votre liste initiale est dans la plage A1:A15,
En B1, saisissez :
=A1
En B2, saisissez cette formule matricielle (à valider par Ctrl+Maj+Entree).
=SI(B1="";"";INDEX(A2:A$16;MIN(SI(NB.SI(B$1:B1;A2:A$15);LIGNE(A$16)-LIGNE()+1;LIGNE(A2:A$15)-LIGNE()+1))))
Puis étirez la formule vers le bas.
III. La fonction Equiv▲
III-A. Description▲
La fonction EQUIV recherche une valeur dans une colonne d'un tableau et renvoie sa
position verticale (numéro de ligne).
La syntaxe est :
Equiv(valeur_cherchée;tableau_recherche;type)
valeur_cherchée est la donnée recherchée dans le tableau.
La donnée peut être de tout type: texte, numérique ou logique.
tableau_recherche est le tableau de données.
type spécifie la recherche à effectuer.
Si la valeur de l'argument type est -1 (valeur par défaut), la fonction EQUIV trouve la plus petite
valeur qui est supérieure ou égale à celle de l'argument valeur_cherchée. Les valeurs
de l'argument matrice_recherche doivent être placées en ordre décroissant.
Si la valeur de l'argument type est 0, la fonction EQUIV trouve la première valeur exactement
équivalente à celle de l'argument valeur_cherchée. Les valeurs de l'argument matrice_recherche
peuvent être placées dans un ordre quelconque.
L'application vérifie tous les éléments de l'argument tableau_recherche les uns après
les autres, jusqu'à ce qu'il trouve une donnée égale à la valeur recherchée.
Si l'argument valeur_cherchée est une donnée texte, les caractères génériques peuvent être
utilisés. L'astérisque (*) correspond à une séquence de caractères. Le point d'interrogation (?)
correspond à un caractère particulier. Si vous voulez rechercher un véritable point d'interrogation
ou astérisque, tapez un tilde (~) avant ce caractère.
Si la valeur de l'argument type est 1, la fonction EQUIV trouve la valeur la plus
élevée qui est inférieure ou égale à celle de l'argument valeur_cherchée. Les valeurs
de l'argument tableau_recherche doivent être triées en ordre croissant.
La fonction EQUIV n'est pas sensible à la casse.
Une valeur d'erreur #N/A est renvoyée si la donnée n'est pas trouvée dans le tableau.
III-B. Exemples▲
III-B-1. Retrouver la position d'une donnée dans une plage de cellules▲
Cet exemple affiche la position de la donnée "dvp" dans la plage cible A1:A10
=EQUIV("dvp";A1:A10;0)
Si le mot recherché est dans la cellule A3, le résultat renvoyé sera 3.
La valeur recherchée peut être numérique ou du texte.
La fonction EQUIV n'est pas sensible aux majuscules ou minuscules pour les recherches de texte.
Si vous ne connaissez qu'une partie du mot recherché, il est possible d'insérer des valeurs
génériques.
L'astérisque est équivalent à une séquence de caractères,
Le point d'interrogation est équivalent à un caractère unique.
Par exemple :
=EQUIV("d*";A1:A10;0)
III-B-2. Extraire le dernier mot d'un texte saisi dans la cellule A1▲
=DROITE(A1;EQUIV(" ";STXT(A1;NBCAR(A1)-LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0))
A valider par Ctrl+Maj+Entree
III-B-3. Appliquer une formule conditionnelle sur une plage de cellule▲
Cet exemple renvoie les valeurs max, min et la moyenne de B3:B10, pour les dates
comprises entre F2 et G2.
Les dates sont dans la plage A3:A10
Les données à calculer sont en B3:B10
La date de début est saisie en F2
La date de fin est saisie en G2
La valeur maxi :
=MAX(INDIRECT(ADRESSE((EQUIV($F$2;A3:A10)+2);2)&":"&ADRESSE((EQUIV($G$2;A3:A10)+2);2)))
La valeur mini :
=MIN(INDIRECT(ADRESSE((EQUIV($F$2;A3:A10)+2);2)&":"&ADRESSE((EQUIV($G$2;A3:A10)+2);2)))
La moyenne
=MOYENNE(INDIRECT(ADRESSE((EQUIV($F$2;A3:A10)+2);2)&":"&ADRESSE((EQUIV($G$2;A3:A10)+2);2)))
III-B-4. Faire une recherche verticale RECHERCHEV sur plusieurs feuilles▲
La fonction suivante recherche le contenu de la cellule FeuilResult!A2.
Les données sont dans la plage C3:F20 de chaque feuille (Feuil1 à Feuil4):
=RECHERCHEV(FeuilResult!A2;INDIRECT("Feuil"&NB(EQUIV(FeuilResult!A2;Feuil1!C3:C20;0))+
NB(EQUIV(FeuilResult!A2;Feuil2!C3:C20;0))*2+NB(EQUIV(FeuilResult!A2;Feuil3!C3:C20;0))*3+
NB(EQUIV(FeuilResult!A2;Feuil4!C3:C20;0))*4&"!C3:F20");4;FAUX)
Si la donnée saisie (FeuilResult!A2) est trouvée, la fonction renvoie la valeur
de la colonne F.
La formule suppose que la donnée recherchée est unique dans le colonne C de
chaque plage (Feuil1!C3:C20 à Feuil4!C3:C20).
La partie :
INDIRECT("Feuil"&NB(EQUIV(FeuilResult!A2;Feuil1!C3:C20;0))+
NB(EQUIV(FeuilResult!A2;Feuil2!C3:C20;0))*2+NB(EQUIV(FeuilResult!A2;Feuil3!C3:C20;0))*3+
NB(EQUIV(FeuilResult!A2;Feuil4!C3:C20;0))*4
Permet de définir dans quelles feuilles faire la recherche.
III-B-5. Extraire les valeurs impaires contenues dans la plage A1:A5▲
En B1, saisissez:
=(A1=IMPAIR(A1))*1
Etirez la formule vers le bas.
La fonction renvoie 1 si la donnée est impaire, et 0 dans le cas contraire.
En C1, saisissez:
=EQUIV(1;$B$1:$B$5;0)
En C2, saisissez:
=EQUIV(1;INDIRECT("B"&(SOMME($C$1:C1)+1)&":B5");0)
Puis étirez la formule vers le bas.
Vous obtenez la position relative des lignes impaires dans le tableau.
En D1, saisissez:
=SI(ESTERREUR(DECALER($A$1;SOMME($C$1:C1)-1;0));"";DECALER($A$1;SOMME($C$1:C1)-1;0))
Puis, étirez la formule vers le bas.
Les colonnes B et C peuvent ensuite être masquées si besoin.
IV. L'association Index Equiv▲
IV-A. Description▲
L'association INDEX / EQUIV est une solution plus complexe à mettre en œuvre que la
classique fonction RECHERCHEV mais moins limitative.
Elle permet de :
Gérer des plages multiples non adjacentes et sans restriction
d'ordre.
D'effectuer une recherche multicritères dans la matrice.
Vous pouvez par exemple afficher le prix d'une référence saisie en E6 en fonction du
nombre de pièces saisi en E7.
Formule à valider par Ctrl+Maj+Entrée
=INDEX(C9:C20;EQUIV(1;(A9:A20=E6)*(B9:B20=E7);0))
A9:A20 contient les références
B9:B20 contient le nombre de pièces
C9:C20 contient les prix
IV-B. Exemples▲
IV-B-1. Afficher la donnée (texte ou numérique) qui apparait le plus souvent dans la plage A1:A10▲
=INDEX(A1:A10;EQUIV(MAX(NB.SI(A1:A10;A1:A10));NB.SI(A1:A10;A1:A10);0))
Formule matricielle à valider par Ctrl+Maj+Entrée
IV-B-2. Créer une relation entre plusieurs tableaux▲
Lorsque vous stockez vos données dans plusieurs tables (bien que ce soit fortement
déconseillé sous Excel), vous aurez peut être besoin ensuite d'une liaison entre
les informations contenues dans les bases.
Par exemple:
Vous disposez de deux tableaux contenant chacun un type de données (Le nom et le code
produit dans un premier tableau, le nom et le prix unitaire dans un deuxième tableau).
Vous souhaitez afficher une synthèse qui renvoie le prix unitaire par code produit.
Plutôt que de reconstituer une base de données commune par de fastidieux copier/coller, et
sans avoir besoin de trier et reclasser les informations, vous pouvez créer une relation
simple entre des tableaux, grâce aux fonctions Index/Equiv.
=SI(ESTNA(INDEX($E$20:$E$25;EQUIV(INDEX($A$20:$A$24;EQUIV(G20;$B$20:$B$24;0));$D$20:$D$25;0)));
"";INDEX($E$20:$E$25;EQUIV(INDEX($A$20:$A$24;EQUIV(G20;$B$20:$B$24;0));$D$20:$D$25;0)))
Remarque:
Il s'agit ici d'un simple exemple pour montrer une capacité des fonctions
Index/Equiv. Cette solution a ses limites, notamment si un élément apparait plusieurs
fois dans une des tables car seule la première donnée trouvée sera prise en compte.
Sous Excel, privilégiez toujours des bases des données uniques, sinon utilisez Access
qui permet de gérer facilement les bases relationnelles.
IV-B-3. Lister toutes les occurrences contenant une chaîne particulière▲
L'exemple liste toutes les cellules de la plage A11:A20 qui contiennent le mot "DVP".
En C14, vous allez saisir la chaîne de caractères à rechercher.
Dans la cellule E14, saisissez cette formule:
=EQUIV("*"&C$14&"*";DECALER(A$11:A$20;SOMME(E$13:E13);0);0)
Puis utilisez la poignée de recopie vers le bas.
Dans la cellule F14, saisissez:
=SI(ESTNA(INDEX(A$11:A$20;SOMME(E$14:E14)));"";INDEX(A$11:A$20;SOMME(E$14:E14)))
Puis utilisez les poignées de recopie vers le bas.
Vous pouvez si vous le souhaitez ensuite, masquer les colonnes intermédiaires
et les valeurs #N/A.
IV-B-4. Rechercher entre bornes sur plusieurs colonnes▲
La fonction retrouve dans quelle ligne est située la valeur de la cellule E11
en fonction des données Mini et Maxi indiquées dans les colonnes A et B.
Le contenu de la colonne C est renvoyé en résultat.
Si vous recherchez 13 (13 étant compris entre 12 et 14), la formule affichera "B".
=INDEX(C10:C14;EQUIV(1;(A10:A14=MAX((A10:A14<=E11)*A10:A14))*(B10:B14=MIN(SI(B10:B14>=E11;B10:B14)));0))
Formule matricielle à valider par Ctrl+Maj+Entrée
V. Liens▲
Les fonctions Excel
La gestion des erreurs dans Excel
La fonction Index
La fonction Equiv
How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel
VI. Remerciements▲
Je remercie toute l'équipe Office de DVP et particulièrement
Jean Ballat,
pour la relecture et la correction du tutoriel.