Excel : Combinaison INDEX + EQUIV

La fonction INDEX, couplée à la fonction EQUIV (MATCH en anglais), vous permet de récupérer des données dans un tableau à double entrée.

Personnellement, je m’en sers quand j’ai une requête avec de nombreuses colonnes dont je souhaite en récupérer que quelques unes et réagencer l’ordre des colonnes à ma convenance. Je saisis 1 seule fois la fonction pour remplir l’intégralité de mon tableau cible.

Voyons ensemble comment marchent ces fonctions.

Description des fonctions

INDEX

La fonction INDEX permet de trouver une valeur précise dans un tableau à double entrée. Elle renvoie au contenu de l’intersection d’une ligne et d’une colonne. Elle peut fonctionner de manière matricielle ou référentielle.

=INDEX(matrice; no_ligne; [no_col])

=INDEX(réf; no_lig; [no_col]; [no_zone])

Nous l’utiliserons plutôt de manière matricielle.

Ainsi nous pourrions la configurer ainsi :

=INDEX(Table A1:B5;3;2)

Sur l’exemple ci-dessus, la configuration signifie que je cherche dans la table qui va de A1 à B5 (5 lignes et 2 colonnes), la donnée qui est à l’intersection de la 3e ligne et la 2e colonne.

La fonction INDEX seule nécessite des données très précises. D’où l’intérêt de la coupler avec la fonction EQUIV.

EQUIV (MATCH)

La fonction EQUIV (MATCH en anglais) permet de chercher une valeur dans une plage de données.

=EQUIV(valeur_cherchée; tableau_recherche; [type])

Ainsi nous pourrions la configurer ainsi :

=EQUIV(« Durand »;A1:A5;0)

Sur l’exemple ci-dessus, je cherche Durand dans la colonne A entre les lignes 1 à 5. Le 0 signifie que je cherche exactement DURAND. (En mettant -1 ou 1, on cherche la valeur inférieure ou supérieure la plus proche)

Cas d’usage

Nous allons voir ensemble comment à partir d’une source, nous pouvons remplir un 2nd tableau dont la configuration diffère.

Le tableau ci-dessous est notre requête source. Les colonnes ont un ordre spécifique. Nous voulons récupérer quelques données mais pas toutes. Cette source se trouve dans l’onglet Recettes.

Construction du tableau cible

Tout d’abord nous construisons notre tableau cible, dans l’onglet Réalisations. Le code recette doit être saisi en dur. Ce sera notre référence pour récupérer les autres données. Ce code fait référence à la Clé recette de la source (Colonne H).

Les noms des colonnes reprennent exactement le même nom que les colonnes de la requête source (via des copier-coller), en choisissant l’ordre qui nous convient le mieux.

Saisie de la formule contenant les fonctions INDEX et EQUIV

Dans la 1ère cellule vide (B2), saisissons la formule contenant la fonction INDEX couplée à la fonction EQUIV.

Décomposons cette formule :

=INDEX(Recettes!$A:$M;EQUIV(Réalisations!$A2;Recettes!$H:$H;0);EQUIV(Réalisations!B$1;Recettes!$1:$1;0))

Si nous reprenons le fonctionnement de la fonction

=INDEX(matrice ; numéro de ligne ; numéro de colonne)

Matrice

La matrice est Recettes!$A:$M. Cela signifie de nous recherchons dans le tableau l’onglet Recettes qui est entre les colonnes A et M.

NB : Nous figeons les colonnes A et M (via le symbole $ avant A et M). Quand nous copierons la formule, la plage ne sera pas décalée.

Numéro de ligne

Le numéro de ligne est EQUIV(Réalisations!$A2;Recettes!$H:$H;0)

Ainsi, étant dans l’onglet Réalisations, nous recherchons la donnée figurant en A2 (soit 2302 dans notre exemple), dans la colonne H du tableau de l’onglet Recettes (Clé recette). Cela nous donne la position, donc le numéro de la ligne.

NB : Dans la référence A2, nous figeons uniquement le A, car quand nous copierons la formule, cela ira chercher les lignes suivantes sans changer de colonne.
Nous figeons toute la colonne H, pour éviter tout décalage.

Numéro de colonne

Le numéro de colonne est EQUIV(Réalisations!B$1;Recettes!$1:$1;0)

Ainsi, étant dans l’onglet Réalisations, nous recherchons, la donnée en B1, soit le titre de la colonne, dans la ligne 1 du tableau de l’onglet Recettes (ligne de titres). Cela nous donne la position, donc le numéro de colonne.

NB : Dans la référence B1, nous figeons uniquement le 1, car quand nous copierons la formule, elle se décalera sur les autres colonnes sans changer de ligne.
Nous figeons la ligne 1 du tableau de Recettes.

A noter : Si les références ne sont pas correctement figées, la copie de la formule dans l’intégralité du tableau entraînera des erreurs.

Copie de la formule sur l’intégralité du tableau

Après avoir configuré la formule, nous pouvons la copier sur l’intégralité du tableau. Si ce dernier est très grand, nous utiliserons la vue fractionnée.

Résultat

Notre tableau cible est entièrement rempli !

L’ordre des colonnes dans la requête source peut changer cela n’affectera pas le résultat. Seule notre référence doit toujours être au même emplacement (Colonne H dans le tableau source).

Si une colonne est renommée dans le source, il suffira de changer le nom dans le tableau cible pour que les résultats s’actualisent correctement.