Combiner INDEX EQUIV Dans Une Formule Excel

Temps de lecture : 6 minutes

Dans l’implémentation de tableau de bord Excel, d’interface utilisateur ou encore couplé avec du VBA pour diverses automatisation ou même pour de simples calculs, il peut être utile de récupérer des données de manière dynamique. Quel que soit le cas d’utilisation, la maitrise du combo INDEX EQUIV peut apporter toute sa force. Nous allons donc aborder ses bases ici.

Illustration - Combo INDEX EQUIV
Illustration - Combo INDEX EQUIV

Pour ce premier abord, cet article se focalise sur une utilisation de la fonction INDEX avec la syntaxe à 2 dimensions combinée avec la fonction EQUIV avec la recherche exacte (Type = 0).

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

Pour le fonctionnement de INDEX et de EQUIV indépendemment, voir : Fonction Excel INDEX et Fonction Excel EQUIV

Avant de commencer sur la combinaison INDEX EQUIV, petit rappel sur chacune de ces fonctions.

La fonction INDEX

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

C’est à dire, étant donnés une plage de cellules, un numéro de ligne et un numéro de colonne, INDEX recherchera la cellule qui correspond à l’intersection de la ligne et de la colonne (donnés en arguments) au sein de la plage de cellules (également donnée en argument).

Il existe deux variantes de la fonction INDEX, nous nous focaliserons ici sur la syntaxe limitée à 2 dimensions.

=INDEX(Matrice, Numéro Ligne, [Numéro Colonne])

L’exemple ci-dessous (la table représente des taux de change fictifs pour différentes devises monétaires à différentes dates), utilisé dans l’article sur la fonction INDEX, illustre parfaitement l’utilisation d’INDEX dans une recherche à 2 dimensions : « Dans la plage de cellules de C3 à F8, je souhaite obtenir la cellule qui se trouve à l’intersection de la ligne 4 et de la colonne 3 ».

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

Or, dans la pratique, on cherchera plutôt à obtenir une donnée particulière dans une recherche basée sur des arguments concret plutôt que sur des numéros de lignes et de colonnes : « Dans la plage de cellules C3 à F8, je souhaiterais obtenir le taux de change du JPY au 04/01/2021 ». 

C’est là que la fonction EQUIV intervient.

La fonction EQUIV

La fonction EQUIV « renvoie la position relative d’un élément dans une matrice qui correspond à une valeur spécifique dans un ordre spécifique. »

C’est à dire, étant données une plage de cellules uni-dimensionnelle (limitée à une seule colonne et plusieurs lignes ou à l’inverses une seule ligne et plusieurs colonnes) et une valeur (numérique, texte, date, logique), la fonction EQUIV recherchera la position (numéro de ligne ou numéro de colonne) relative de cette valeur au sein de la plage de cellules. Plusieurs variations du type de recherche sont permises telles que recherche exacte ou recherche approximative. Nous nous focaliserons ici sur la recherche exacte ([Type] = 0)

=EQUIV(Valeur Cherchée, Tableau Recherche, [Type])

Les deux exemples ci-dessous, respectivement un exemple pour EQUIV pour trouver un numéro de Ligne et un exemple pour EQUIV pour trouver un  numéro de Colonne sont ceux utilisés dans l’article sur la fonction EQUIV.

Fonction Excel EQUIV - Exemple Avec Correspondance Exacte
Fonction Excel EQUIV - Exemple Avec Correspondance Exacte

Ce premier exemple illustre l’utilisation de la fonction EQUIV sur une plage de cellules composée d’une colonne et de plusieurs lignes (la colonne Mot), on indique à la fonction EQUIV « je cherche le numéro de la ligne où se trouve la valeur ‘Pomme’ avec correspondance exacte (Type = 0) ».

Fonction Excel EQUIV - Exemple Avec Colonne
Fonction Excel EQUIV - Exemple Avec Colonne

Dans ce second exemple, la plage de cellules est composée d’une seule ligne et de plusieurs colonnes (la ligne Date), on indique à EQUIV « je cherche le numéro de la colonne où se trouve la date ’01/06/2021′ avec une correspondance exacte (Type = 0) ».

EQUIV renvoie donc soit un numéro de ligne, soit un numéro de colonne (si la valeur cherchée est effectivement trouvée), ce qui correspond exactement à ce dont la fonction INDEX a besoin !

La combinaison INDEX EQUIV

Puisque la fonction INDEX requiert un numéro de ligne et/ou un numéro de colonne pour retourner la cellule qui se trouve à l’intersection de cette ligne et/ou colonne au sein d’une plage de cellules.

Et que la fonction EQUIV fournit un numéro de ligne ou un numéro de colonne pour une valeur cherchée au sein d’une plage de cellules.

Alors, on peut demander avec cette combinaison :

  • « EQUIV, je cherche le numéro de la ligne où se trouve la valeur ‘X’ dans ma colonne de recherche » avec correspondance exacte
  • « EQUIV, je cherche le numéro de la colonne où se trouve la valeur ‘Y’ dans ma ligne de recherche » avec correspondance exacte
  • puis « INDEX, je cherche la cellule qui se trouve à l’intersection de la ligne, donnée par EQUIV pour la valeur ‘X’ et de la colonne donnée par EQUIV pour la valeur ‘Y’ au sein de ‘Plage de cellules‘ ».
=INDEX(Plage de cellules, EQUIV(Colonne de recherche plage de cellules, 'X', 0), EQUIV(Ligne de recherche plage de cellules, 'Y', 0))
Illustration - Intersection INDEX EQUIV
Illustration - Intersection INDEX EQUIV

Voyons quelques exemples concrets d’utilisation.

INDEX EQUIV : Recherche d'une Ligne

Il est tout à fait possible d’utiliser la combinaison INDEX EQUIV sur une seule colonne. Dans ce cas il n’est pas nécessaire de fournir à INDEX l’argument Numéro de Colonne.

Ci-dessous une Table qui répertorie le code référence d’un article, son intitulé et son prix.

INDEX EQUIV - Rechercher une ligne
INDEX EQUIV - Rechercher une ligne

On se base sur l’intitulé de l’article pour retrouver son Code qui se trouve à gauche de la colonne de recherche (Exemple #1) et son Prix (Exemple #2) qui se trouve à droite de la colonne de recherche. 

En effet, INDEX permet de rechercher ou à droite, ou à gauche de la colonne de référence.

On utilise EQUIV sur la colonne Article (C3:C7) pour retrouver une correspondance exacte avec la valeur passée en argument via C9 (« Soda »). EQUIV renvoie le numéro relatif de la ligne trouvée pour cette valeur au sein de la plage de cellule C3:C7 qui est alors utilisé comme argument Numéro de Ligne pour la fonction INDEX. On utilise ce numéro de ligne pour trouver la correspondance dans les colonnes souhaitées : pour #1 Code (position relative sur la plage B3:B7) et pour #2 Prix (position relative dans la plage D3:D7).

INDEX EQUIV : Recherche d'une Colonne

Dans le cas contraire, il est aussi possible d’utiliser la combinaison INDEX EQUIV sur une seule ligne. Dans ce cas on omettra l’argument Numéro de Ligne de la fonction INDEX, ou on le forcera à « 1 ».

La table ci-dessous reprend des taux de change fictifs pour l’EUR/USD pour différentes dates en colonne.

INDEX EQUIV - Rechercher une colonne
INDEX EQUIV - Rechercher une colonne

On cherche à trouver le taux de change pour une date donnée. On va donc indiquer à EQUIV quelle date nous souhaitons afin qu’il nous indique sa position relative dans la plage de cellules de dates (B4:G4) qui s’étale en ligne. On passe en argument la date « 03/01/2021 » via la cellule C7.

On utilise ainsi la fonction EQUIV imbriquée à l’intérieur de la fonction INDEX pour lui indiquer le numéro de Colonne. Puisque l’on recherche la valeur correspondante dans une plage de cellules qui ne contient qu’une seule ligne (B5:G5), on omet l’argument numéro de ligne ou on le force à « 1 ».

INDEX EQUIV : Recherche à 2 dimensions - Intersection Ligne et Colonne

Comme illustré au début de cette section, l’exemple complet d’une recherche croisée au sein d’une plage de cellules avec INDEX EQUIV.

Le Tableau à double entrée ci-dessous croise différentes devises (en colonne) pour lesquelles on a le taux de change face au GBP à différentes dates (en ligne).

INDEX EQUIV - Recherche Croisée avec Intersection Ligne Colonne
INDEX EQUIV - Recherche Croisée avec Intersection Ligne Colonne

Ici, on cherche à obtenir le taux de change face au GBP pour une devise donnée en argument à une date également donnée en argument.

On utilise donc EQUIV dans un premier temps pour récupérer le numéro de ligne correspondant à la valeur cherchée dans notre plage de données (On cherche la ligne de « 03/01/2021 » dans B4:B10). Puis, on utilise à nouveau EQUIV pour récupérer le numéro de colonne correspondant à la valeur cherchée (On cherche la colonne de « JPY » dans B4:F4).

Pour finir, on imbrique ces deux fonctions EQUIV dans la fonction INDEX, respectivement pour l’argument Numéro de Ligne, puis l’argument Numéro de Colonne que l’on souhaite retrouver relativement au sein de la plage de cellules entière (B4:F10).

Partager :

Laisser un commentaire