Préambule

On peut compter sur les doigts de la main, les fonctions qui sont majeures et INDEX en fait partie.
Pourquoi ?

La syntaxe d’INDEX

La fonction INDEX a deux syntaxes possibles
INDEX(matrice, no_lig, [no_col])
INDEX(reference, row_num, [column_num], [area_num])
La première est de forme matricielle, la seconde de forme référencielle
Ce billet illustrera la première syntaxe

Scénario 1

Dans le tableau ci-dessus simulant les récoltes en kilos de plusieurs fruits pour une période donnée, nous aimerions obtenir par exemple le nombre de kilos de fraises récolté en juin.
Comme nous pouvons le constater, ce tableau est constitué de 8 lignes et 12 colonnes et la plage de cellules colorée en vert contient les valeurs (kilos) que nous devons obtenir à l’aide de la fonction INDEX.
Cette plage est donc la matrice (1er argument de INDEX) et est nommée Recolte (pour en savoir plus sur le nom des cellules, lire ce billet titré Comment simplifier la lecture d’une formule dans excel en nommant les cellules
Les fraises se trouvant en ligne 2 du tableau et le mois de juin en 4ème colonne, la formule pour obtenir le nombre de kilos se trouvant à l’intersection de la 2ème ligne et 4ème colonne de la plage nommée Recolte sera

=INDEX(Recolte, 2, 4)

La fonction renverra donc le nombre 320
Comme nous l’avons constaté, la syntaxe est simple mais telle que nous l’avons écrite, elle n’est pas pratique.
En effet, il serait plus intéressant que le calcul du numéro de ligne et du numéro de colonne soit calculé automatiquement en fonction de la période et du fruit choisi.
C’est là qu’intervient une autre fonction intéressante et que nous pouvons qualifier également de majeure

EQUIV – La fonction « coup de pouce » d’INDEX

INDEX ne serait pas tout à fait majeure sans le coup de pouce de la fonction EQUIV
EQUIV a pour but de renvoyer la position d’une valeur cherchée dans une colonne ou une ligne. Ainsi, si nous écrivons =EQUIV("Fraise";C7:C14;0), la fonction renverra la valeur 2 et =EQUIV("juin";D6:O6;0) renverra 4
C’est à dire exactement, les deux valeurs constantes que nous avions placées respectivement en deuxième et troisième argument de la fonction INDEX de notre premier scénario.

La formule complète

La formule est en D16, la plage C7:C14 est nommé lstFruits, la plage D6:O6 se nomme lstPeriod

On peut voir dans l’illustration ci-dessus que les cellules C16 et C17 le fruit et la période dont on cherche le poids récolté et que le choix est effectué grâce à une liste déroulante (fonctionnalité Validation des données)
La formule complète incluant INDEX et l’imbrication des deux fonctions EQUIV est
=INDEX( Recolte ; EQUIV(C16;lstFruits;0) ; EQUIV(C17;lstPeriod;0) )
Remarque : Les caractères espacés ne sont là que pour rendre la formule plus lisible

Scénario 2

Imaginons que nous ayons trois codes TVA numérotés 1, 2 et 3 pour définir les différents taux que nous utilisons (actuellement 0%, 6% et 21%) pour établir nos factures.
Dans l’illustration ci-dessus, nous pouvons voir que la plage de cellules A2:A4 contient les taux de TVA et que la cellule contient le code qui permettra à la fonction INDEX de nous renvoyer le taux de TVA.
Pour ce scénario, nous n’utiliserons que les deux premiers arguments, soit
=INDEX(A2:A4:D2)
Lorsque la plage de cellules (argument Matrice), se trouve sur UNE ligne ou sur UNE colonne, l’utilisation du deuxième argument permet de définir le Xème élément à renvoyer (ici pour l’exemple le 3ème élément de A2:A4 est 21%

Scénario 3 – Un autre exemple

Nous vendons des produits comme par exemple des tuyaux avec une longueur et une épaisseur et nous souhaitons trouver le prix après avoir indiqué ces mesures.
Nous insérons la longueur en B6 et l’épaisseur en B7. la formule ci-dessous se trouve en B12
la formule =INDEX(tblPrice;EQUIV(B6;LabelLenght;0);EQUIV(B7;LabelThickness;0))
Le tableau D3:F5 est nommé tblPrice, LabelLenght pour la plage D2:F2 et LabelThickness pour C3:C2

A lire

Crédit

Merci à Sophie du blog Tomate-Cerise pour m’avoir autorisé à publier la grille « Les fruits de mon jardin » qui m’a inspirée pour expliquer la fonction INDEX