Fonction Index – Rechercher Une Valeur Dans Un Tableau Excel

Temps de lecture : 10 minutes

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.

Excel Fonction INDEX
Excel Fonction INDEX

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.

Ce premier article pose les bases sur le fonctionnement de la fonction INDEX utilisée seule.

Si vous avez des difficultés ou une question, n’hésitez pas à commenter ou me contacter.

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 paramètres.

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.

Taux De Change - Exemple
Taux De Change - Exemple

Si l’on souhaite chercher dans cette table le taux de change de l' »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 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.

Fonction Excel INDEX : Les 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 calculs Excel.

=INDEX(Matrice, Numéro Ligne, [Numéro Colonne])
Données à 2 Dimensions
Données à 2 Dimensions

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 cellule, soit directe (A1:B100), soit via une plage de cellules nommées via 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, retourner 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.

Données à 3 Dimensions
Données à 3 Dimensions

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 paramètre 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 la 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 calculs.

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 versions)

=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 inutile 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.

Fonction INDEX : Rechercher une valeur située dans une ligne

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 ligne 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).

Fonction INDEX - Exemple Recherche Ligne
Fonction INDEX - Exemple Recherche Ligne

Fonction INDEX : Rechercher une valeur située dans une colonne

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 cellule (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).

Fonction INDEX - Exemple Recherche Colonne
Fonction INDEX - Exemple Recherche Colonne

Fonction INDEX : Rechercher une valeur à l'intersection d'une ligne et d'une colonne

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ée 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).

Fonction INDEX - Exemple Recherche Intersection Ligne et Colonne
Fonction INDEX - Exemple Recherche Intersection Ligne et Colonne

Fonction INDEX : Recherche à 3 dimensions

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).

Fonction INDEX - Exemple Recherche 3 Dimensions
Fonction INDEX - Exemple Recherche 3 Dimensions

Fonction INDEX : Rechercher une ligne entière

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 lignes, entrer la formule et valider avec [Ctrl] + [Shift] + [Entrer].

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).

Fonction INDEX - Exemple Recherche Ligne Entière
Fonction INDEX - Exemple Recherche Ligne Entière

Fonction INDEX : Rechercher une colonne entière

Même principe que l’exemple précédemment à 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 colonnes, entrer la formule et valider avec [Ctrl] + [Shift] + [Entrer].

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).

Fonction INDEX - Exemple Recherche Colonne Entière
Fonction INDEX - Exemple Recherche Colonne Entière
Partager :

One Reply to “Fonction Index – Rechercher Une Valeur Dans Un Tableau Excel”

Laisser un commentaire