La fonction Excel INDEX fait partie de la catégorie « Recherche & Référence » et est l’une des fonction les plus intéressantes pour commencer à créer des tableaux de bord et rendre vos classeurs Excel dynamiques.
En effet, INDEX permet d’obtenir la cellule qui se trouve à l’intersection d’une ligne et d’une colonne au sein d’une plage de cellules, et peut être également utilisée sur 3 dimensions.
Bien qu’utilisée seule son intérêt est limité, elle trouve tout son pouvoir en combinaison avec d’autres fonctions, notamment avec une fonction EQUIV imbriquée.
Dans ce premier article, je poserai les bases sur le fonctionnement de la fonction INDEX utilisée seule.
- 1 Pourquoi Utiliser la Fonction INDEX ?
- 2 Comment Utiliser la Fonction INDEX : 2 Syntaxes
-
3
Fonction Excel INDEX : Exemples d’utilisation
- 3.1 Rechercher une Valeur Située dans une Ligne avec INDEX
- 3.2 Rechercher une Valeur Située dans une Colonne avec INDEX
- 3.3 Rechercher une Valeur à l’Intersection d’une Ligne et d’une Colonne avec INDEX
- 3.4 Recherche à 3 Dimensions avec INDEX
- 3.5 Rechercher une Ligne Entière avec INDEX
- 3.6 Rechercher une Colonne Entière avec INDEX
Pourquoi Utiliser la Fonction INDEX ?
Telle que définie dans Excel, la fonction INDEX « renvoie une valeur ou la référence de la cellule à l’intersection d’une ligne et d’une colonne particulières, dans une plage de données. »
Considérant une plage de cellules contenant plusieurs lignes et plusieurs colonnes de données croisées, la fonction INDEX permet donc de retrouver, au sein de cette plage de cellules, la cellule qui correspond à l’intersection de la ligne et de la colonne données en argument.
Prenons pour illustration la table ci-dessous qui croise des taux de change fictifs pour différentes devises monétaires (en colonnes) à différentes dates (en lignes). Les numéros de lignes et colonnes sont indiqués en italique.
Si l’on souhaite chercher dans cette table le taux de change de « EUR » au « 04/01/2021 », il suffira de d’utiliser la fonction INDEX en lui demandant la donnée qui se trouve à la ligne 4 (ligne pour « 04/01/2021 ») et à la colonne 1 (colonne pour « EUR ») de cette table, le résultat sera donc la cellule contenant « 1.205 ».
La Fonction INDEX Renvoie-t-elle une Valeur ou une Référence ?
Pour éclaircir la définition donnée dans Excel, notamment la partie « renvoie une valeur ou la référence de la cellule », le résultat renvoyé par la fonction INDEX est en réalité une référence à la cellule trouvée.
Utilisée directement telle qu’elle dans une cellule (=INDEX(…)), le résultat affiché semble être une valeur (identique à la valeur de la cellule trouvée) alors qu’il s’agit en fait d’une référence, qui affiche elle-même à son tour la valeur de la cellule trouvée (et référencée), comme le ferait une formule sur une référence (telle que « =A1 » par exemple).
Ce détail a son importance puisqu’il permet alors à la fonction INDEX d’être imbriquée dans toute autre fonction qui nécessite une référence.
Comment Utiliser la Fonction INDEX : 2 Syntaxes
Il existe deux syntaxes disponibles pour la fonction INDEX :
- La syntaxe pour une recherche à 2 dimensions (Ligne, Colonne), qui prend jusque 3 arguments dont 1 optionnel.
- La syntaxe pour une recherche à 3 dimensions (Table, Ligne, Colonne), qui prend jusque 4 arguments, dont 2 optionnels (et intègre en elle-même la syntaxe précédente dans le sens où si seulement 2 ou 3 arguments sont fournis, il s’agit de la syntaxe à 2 dimensions).
La Syntaxe Matrice : Recherche à 2 Dimensions
Cette première syntaxe prend jusque 3 arguments et permet une recherche sur une table de données à 2 dimensions. Plus simple à visualiser, il suffit d’y voir simplement un tableau composé de lignes et de colonnes, ce qui ressemble fortement à une feuille de calcul Excel.
=INDEX(Matrice; Numéro Ligne; [Numéro Colonne])
Avec, tel que défini dans Excel :
Matrice : est une plage de cellules ou une constante de matrice.
Numéro Ligne : sélectionne la ligne de la matrice ou de la référence à partir de laquelle la valeur doit être renvoyée. Si cet argument est omis, Numéro Colonne est requis.
Numéro Colonne : [Optionnel] sélectionne la colonne de la matrice ou de la référence à partir de laquelle la velur doit être renvoyée. Si cet argument est omis, Numéro Ligne est requis.
L’argument Matrice correspond donc à la référence de notre plage de cellules, soit directe (A1:B100), soit via une plage de cellules nommées dans le gestionnaire de noms.
Le Numéro de Ligne peut rester vide si le Numéro de Colonne est fourni, il doit s’agir d’une valeur numérique qui correspond au numéro relatif de la ligne à laquelle se trouve la cellule que l’on cherche au sein de Matrice, ce numéro peut être compris entre 0 (voir plus bas pour ce point) et jusqu’au maximum de lignes disponibles dans Matrice.
De façon similaire, le Numéro de Colonne peut rester vide si le Numéro de Ligne est fourni, il doit là aussi s’agir d’une valeur numérique qui correspond au numéro relatif de la colonne à laquelle se trouve la cellule que l’on cherche au sein de Matrice, ce numéro peut être compris entre 0 (voir plus bas pour ce point) et jusqu’au maximum de colonnes disponibles dans Matrice.
La Syntaxe Référence : Recherche à 3 Dimensions
Contrairement à la première syntaxe à 2 dimensions, on rajoute ici une 3ème dimension « Table » plus difficile à visualiser dans un classeur Excel (voir image). On imagine une superposition de plusieurs tableaux composés de lignes et de colonnes. Dans ce cas, la fonction INDEX effectue un triple croisement, on lui demande : « Dans cette Table, retourne la cellule qui se trouve à cette Ligne et à cette Colonne« .
Dans la pratique, il s’agira effectivement de plusieurs plages de cellules à 2 dimensions réparties à différents endroits d’une feuille de calculs.
La syntaxe est la suivante :
=INDEX(Référence; Numéro Ligne; [Numéro Colonne]; [Numéro Zone])Avec, tel que défini dans Excel :
Référence : est une référence à une ou plusieurs plages de cellules.
Numéro Ligne : sélectionne la ligne de la matrice ou de la référence à partir de laquelle la valeur doit être renvoyée. Si cet argument est omis, Numéro Colonne est requis.
Numéro Colonne : [Optionnel] sélectionne la colonne de la matrice ou de la référence à partir de laquelle la velur doit être renvoyée. Si cet argument est omis, Numéro Ligne est requis.
Numéro Zone : [Optionnel] sélectionne une plage de la référence à partir de laquelle une valeur doit être renvoyée.
A la différence de la syntaxe à 2 dimensions, le premier argument considère la 3ème dimension « Table », Référence pourra donc être plusieurs plages de cellules, soit en références directes, séparées et encadrées de parenthèses, soit le nommage de cet ensemble de plages de cellules via le gestionnaire de noms.
A noter que l’ensemble des plages de cellules de cet argument, qu’elles soient directes ou via gestionnaire de noms, doivent se trouver sur la même feuille de calcul.
Les arguments Numéro Ligne et Numéro Colonne reste identiques à ceux de la syntaxe à 2 dimensions (voir section précédente).
Le dernier argument Numéro Zone permet d’indiquer dans quelle plage de cellules, parmi celles passées dans l’argument Référence, on souhaite retrouver la ligne et la colonne. Il permet donc de sélectionner l’emplacement au sein de la 3ème dimension.
Les Arguments Numéro Ligne et Numéro Colonne
Selon les cas, les arguments Numéro Ligne et Numéro Colonne peuvent être omis ou passés à « 0 ».
Plage de Cellules à une Seule Ligne
Dans le cas où la plage de cellules de l’argument Matrice (ou les plages de cellules de l’argument Référence) ne contient qu’une seule ligne, l’argument Numéro Ligne peut être omis puisqu’il sera par défaut à « 1 ». On entrera donc la formule en laissant vide l’argument mais sans oublier le séparateur de liste (« , » ou « ; » selon les paramétrage d’Excel)
=INDEX(Matrice; ; Numéro Colonne)
=INDEX(Référence; ; Numéro Colonne; Numéro Zone)Plage de Cellules à une Seule Colonne
De manière similaire, si la plage de cellules de l’argument Matrice (ou les plages de cellules de l’argument Référence) ne contient qu’une seule colonne, l’argument Numéro Colonne peut être omis et sera par défaut « 1 ».
=INDEX(Matrice; Numéro Ligne)
=INDEX(Référence; Numéro Ligne; ; Numéro Zone)Rechercher une Colonne Entière
Il est possible de rechercher une colonne entière en passant comme argument Numéro Ligne la valeur « 0 ». Dans ce cas, la fonction INDEX retournera un Tableau de cellules qui contiendra l’ensemble des cellules de la colonne déterminée par l’argument Numéro Colonne.
Rechercher une Ligne Entière
Le principe est le même pour obtenir une ligne entière : il est possible de passer comme argument Numéro Colonne la valeur « 0 », ainsi la fonction INDEX retournera un Tableau de cellules qui contiendra l’ensemble des cellules de la ligne déterminée par l’argument Numéro Ligne.
Rechercher une Plage de Cellules Entière
Il est aussi possible de combiner ensemble l’omission d’argument Numéro Ligne et Numéro Colonne, ou de les fixer à « 0 » dans le but d’obtenir en retour de la fonction INDEX un Tableau de l’ensemble des cellules qui composent une plage de cellules.
=INDEX(Matrice; 0; 0)
=INDEX(Matrice; 0; 0)
=INDEX(Référence; 0; 0; Numéro Zone)
=INDEX(Référence; ; ; Numéro Zone)Bien que l’intérêt reste limité dans la syntaxe à 2 dimensions puisque cela revient à référencer directement la matrice elle-même.
Fonction Excel INDEX : Exemples d’utilisation
Nous avons vu qu’il existe différentes syntaxes et possibilités pour faire varier les arguments de la fonction INDEX, nous allons donc maintenant illustrer plusieurs de ces cas possibles à travers les exemples ci-dessous.
Rechercher une Valeur Située dans une Ligne avec INDEX
Premier exemple pour illustrer la recherche d’une cellule qui se trouve à la ligne, donnée en argument, dans une plage de cellules composée de plusieurs lignes mais d’une seule colonne.
On passe donc pour l’argument Matrice la référence de notre plage de cellules (C2:C7) et on indique par le deuxième argument Numéro Ligne, la ligne pour laquelle on souhaite trouver la cellule au sein de cette Matrice (La ligne « 3 » est passée en argument via la cellule C9).
Rechercher une Valeur Située dans une Colonne avec INDEX
Ici à l’inverse, on va rechercher une cellule qui se trouve à la colonne, donnée en argument, dans une plage de cellules composée d’une seule ligne mais de plusieurs colonnes.
On passe alors en argument Matrice la référence de la plage de cellules (B3:G3), on laisse l’argument Numéro Ligne vide (on peut également indiquer « 1 » qui sera la valeur par défaut si vide), et on indique finalement le Numéro Colonne pour la cellule que l’on souhaite obtenir au sein de cette Matrice (La colonne « 3 » est passée en argument via la cellule C5).
Rechercher une Valeur à l’Intersection d’une Ligne et d’une Colonne avec INDEX
Cette fois-ci notre plage de cellules est réellement multi-dimensionnelle : plusieurs lignes et plusieurs colonnes. Nous allons y rechercher une cellule qui se trouve à l’intersection de la ligne et de la colonne passées en arguments.
Il faut donc passer 3 arguments : la Matrice est là encore la référence de notre plage de cellules (C3:F8), le Numéro Ligne (Ligne « 4 » passée en argument via la cellule C10) et le Numéro Colonne (Colonne « 3 » passée en argument via la cellule C11).
Recherche à 3 Dimensions avec INDEX
Passons à la syntaxe à 3 dimensions. Notre feuille de calculs se compose ici de plusieurs plages de cellules (3 Tables : Table 1, Table 2 et Table 3) qui contiennent chacune plusieurs lignes et plusieurs colonnes.
On indique donc à la fonction INDEX, les références de chaque plage de cellules, séparées (par « , » ou « ; » selon les réglages) et encadrées de parenthèses (Ici (C4:F9; H4:K9; M4:P9)).
Puis pour l’argument Numéro Ligne, la ligne pour laquelle on cherche la cellule (au sein de la plage qui sera donnée en argument Numéro Zone) (La ligne « 4 » passée en argument via la cellule C12).
Pour l’argument Numéro Colonne, la colonne pour laquelle on cherche la cellule (au sein de la plage qui sera donnée en argument Numéro Zone) (La Colonne « 3 » est passée en argument via la cellule C13).
Finalement on indique l’argument Numéro Zone, il s’agit du numéro de la Table au sein de la liste passée dans le premier argument Référence, c’est au sein de cette plage de cellules que sera recherchée l’intersection Ligne/Colonne (On indique ici Zone « 2 » via la cellule C11, INDEX prend donc en compte la deuxième plage de cellule passée dans l’argument Référence, donc H4:K9).
Rechercher une Ligne Entière avec INDEX
Comme mentionné précédemment, il est également possible de retourner une ligne entière via la fonction INDEX, pour ce faire, il suffit de passer « 0 » pour l’argument Numéro Colonne.
Aussi, puisque le résultat sera sous forme matricielle, il faudra entrer et valider la formule en tant que formule matricielle : c’est à dire sélectionner plusieurs cellules en ligne, entrer la formule et valider avec Ctrl + Shift + Entrée.
Dans l’exemple ci-dessous, on souhaite retrouver l’ensemble de la ligne « 4 » au sein de la plage de cellules qui s’étend de « C3 » à « F8 ». On indique le Numéro Ligne et le Numéro Colonne respectivement via les cellules « C10 » et « C11 » (Colonne « 0 » pour récupérer toutes les colonnes de la Ligne « 4 » donc la ligne entière). Pour finir on sélectionne un nombre de cellules suffisant pour accueillir notre Ligne de résultat (De C13 à F13) et on entre la formule « =INDEX(C3:F8; C10; C11) » que l’on valide ensuite en formule matricielle (d’où l’ajout automatique des « {} » dans la formule).
Rechercher une Colonne Entière avec INDEX
Même principe que l’exemple précédent à la différence que cette fois-ci on souhaite obtenir une colonne entière. On passera donc « 0 » pour l’argument Numéro Ligne, et le Numéro de la colonne que l’on souhaite obtenir pour l’argument Numéro Colonne. Il s’agit là également d’une formule matricielle puisque la fonction INDEX renverra en résultat une matrice en colonne. il faudra entrer et valider la formule en tant que formule matricielle : c’est à dire sélectionner plusieurs cellules en colonne, entrer la formule et valider avec Ctrl + Shift + Entrée.
Dans l’exemple ci-dessous, on souhaite retrouver l’ensemble de la colonne « 3 » au sein de la plage de cellules qui s’étend de « C3 » à « F8 ». On indique le Numéro Ligne et le Numéro Colonne respectivement via les cellules « C10 » et « C11 » (Ligne « 0 » pour récupérer toutes les lignes de la Colonne « 3 » donc la colonne entière). Pour finir on sélectionne un nombre de cellules suffisant pour accueillir notre Colonne de résultat (De F10 à F15) et on entre la formule « =INDEX(C3:F8; C10; C11) » que l’on valide ensuite en formule matricielle (d’où l’ajout automatique des « {} » dans la formule).
