Fonction Excel : INDEX/EQUIV (INDEX/MATCH)

Publié le 28 avril 2011
par Sophie Marchand M.Sc.
Fonction INDEX

Avant de vous familiariser avec la fonction Index/Equiv,  êtes-vous des utilisateurs des fonctions RECHERCHEV (VLOOKUP) et RECHERCHEH (HLOOKUP)?

 

Vous avez répondu non…

Si vous travaillez avec des bases de données Excel et que vous n’utilisez pas encore les fonctions RECHERCHEV (VLOOKUP) et RECHERCHEH (HLOOKUP), il est grand temps d’apprendre à le faire puisque la technique est simple à utiliser et les résultats très efficaces. Pour voir des exemples de l’utilisation des fonctions Recherchev et rechercheh, cliquez ici. Une fois que vous aurez maîtrisé ces fonctions, revenez consulter ce billet pour voir comment la fonction INDEX/EQUIV (INDEX/MATCH) pourra vous permettre d’obtenir des résultats encore plus intéressants!

 

Vous avez répondu oui…

Saviez-vous que vous pouvez à la fois exécuter un RECHERCHEV (VLOOKUP) et un RECHERCHEH (HLOOKUP) dans une base de données? Ceci pourrait être nécessaire dans le cas où vous devez fournir une donnée tirée d’une table à deux dimensions. Vous pourriez alors le faire en utilisant la fonction INDEX, suivi de la fonction EQUIV (ou MATCH en anglais).

 

Comment interpréter la fonction INDEX?

La fonction INDEX de base a 3 paramètres. On recherche d’abord dans une matrice (en bleu), une donnée qui se trouve à l’intersection d’une ligne (désignée par la première fonction EQUIV imbriquée) et d’une colonne (désignée par la deuxième fonction EQUIV).

Par exemple, ci-dessous, on cherche, dans la matrice en bleu;

  • la valeur sur la ligne qui correspond au nombre de pages 55 dans la colonne en mauve
  • et dans la colonne qui correspond au nombre de copies 15,000 dans la ligne en rose.

On obtient ainsi 4,895$.

 

Index/Equiv Fonction INDEX

 

Le deuxième paramètre sert donc à spécifier un numéro de ligne. Mais pour éviter d’entrer une valeur fixe, qui ferait en sorte de mettre à risque la formule par rapport à l’ajout d’une nouvelle ligne dans le tableau, on utilise la fonction EQUIV. La fonction EQUIV ci-dessous permet de retourner la position du nombre 55 dans la liste spécifiant les nombres de pages, ceci retourne la valeur 11.

 

Index/Equiv EQUIV pour numéro de ligne

 

Le troisième paramètre sert à spécifier le numéro de colonne, par le biais de la fonction EQUIV, pour protéger la formule contre un ajout de colonnes. Elle cherche ainsi la position du nombre 15,000 dans la ligne spécifiant les quantités de copies, ceci retourne la valeur 3.

 

Index/Equiv EQUIV pour trouver numéro colonne

 

Autrement dit, le premier EQUIV cherche la ligne qui correspond au nombre de pages spécifié par l’usager, par le biais de la fonction EQUIV…

 

Index/Equiv Trouver no ligne

 

… et le deuxième EQUIV cherche la colonne qui correspond au nombre de copies spécifié par l’usager, par le biais d’une deuxième fonction EQUIV.

 

Index/Equiv Trouver no colonne

 

Au final, ce qu’on se trouve réellement à demander à Excel en utilisant la fonction Index/Equiv (Index/Match), c’est de retourner la valeur à la croisée des de la ligne 11 et de la colonne 3. Sauf qu’au lieu d’utiliser des valeurs fixes, on imbrique des fonctions EQUIV pour se protéger contre l’ajout éventuel de lignes ou de colonnes dans le tableau.

 

Index/Equiv Ligne 11 colonne 3

 

Pourquoi la fonction Index/Equiv (Index/Match) est-elle si utile?

Cette fonction vous permet d’automatiser votre recherche de données dans une table  deux dimensions. Vous économisez ainsi beaucoup de temps et vous évitez de faire des erreurs. Notez que si vous ajoutez des lignes et/ou des colonnes à votre tableau en deux dimensions, la fonction s’ajustera d’elle-même. Vous n’aurez donc pas à faire d’ajustements manuels.

 

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

 

La fonction Index/Equiv (Index/Match) en modélisation financière

Cette fonction devient encore plus intéressante lorsqu’elle est rattachée à un modèle excel.

 

Exemple : produire les prévisions financières d’un magazine

Prenons l’exemple d’un modèle Excel qui serait utilisé pour produire les prévisions financières d’un magazine. Les gestionnaires du magazine voudront sans doute tester divers scénarios sans avoir à refaire chaque fois une nouvelle analyse. Ils voudront ainsi utiliser un modèle flexible qui leur permettra de jouer avec les paramètres du modèle pour générer les résultats de divers scénarios.

Dans ce modèle interactif, il y aura sans doute un paramètre concernant le nombre d’annonces et le nombre d’articles à paraître dans chaque magazine. Ces paramètres permettront au modèle de calculer automatiquement le nombre de pages à imprimer. Parallèlement, des prévisions seront faites concernant la couverture du territoire où seront distribués les magazines.

Par exemple, les gestionnaires pourraient baser cette estimation :

  • sur la population du territoire desservi
  • sur le nombre de présentoirs disponibles et /ou de distributeurs associés
  • il pourrait également s’agir d’un mixte de tout cela.

Ces paramètres influenceront directement le nombre de copies à imprimer et seront donc liés à la cellule sous-jacente dans le modèle Excel.

En somme, les gestionnaires pourront raffiner leurs hypothèses et tester l’impact de divers scénarios sur leurs résultats. Aussi, ils n’auront pas à reprogrammer chaque fois les coûts d’impression du magazine.

 

Voyez la fonction Index/Equiv en action avec ce tutoriel


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Afin d’approfondir vos connaissances avec la fonction INDEX et d’autres fonctions Excel utiles en modélisation financière et en élaboration de fichiers Excel dignes des meilleures pratiques d’affaires, nous vous recommandons de suivre notre formation Excel – Modélisation financière (niveau 1) .

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

6 réflexions sur “Fonction Excel : INDEX/EQUIV (INDEX/MATCH)”

  1. Frédéric Pelletier

    Avec cette fonction, est-il obligatoire d’avoir des chiffres dans la colonne et la ligne de référence ou peut-elle utiliser du texte? Lorsque j’essaie par exemple de croiser “Salaire” (en A2) et “Août” (en I6), la résultante ne renvoie pas valeur que je suis supposé atteindre.

    Merci à l’avance!
    Frédéric

    1. Bonjour Frédéric,

      C’est probablement parce qu’il y a un écart d’épellation dans le mot de référence et les mots de la plage de référence… Si vous le souhaitez, vous pouvez me faire parvenir votre fichier Excel (ou un extrait) à marchandsophie@hotmail.com et je pourrai mieux comprendre le problème et surtout, le résoudre!

      Au plaisir

      Sophie

    2. Bonjour Frédéric,

      Il y avait une petite erreur dans vos formules de “Match”, au niveau du troisième élément de la formule, soit le “match type”.

      Lorsque vous mettez le “match type” à zéro, tout fonctionne bien.

      Au plaisir,

      Sophie

  2. =SI(EQUIV(A3; C1:H1; 0); INDEX(C3:H8; EQUIV(A3; A3:A8; 0); EQUIV(A3; C1:H1; 0)); “#N/A”)
    A B C D E F G H
    1 N° Banque 1000 1100 1200 1300 1400 1500
    2
    3 1000 50 0
    4 1100 100
    5 1200 150
    6 1300 200
    7 1400 250
    8 1500 300
    Il y a une erreur dans la formule excel
    Je désire que les montants de Banque soient répartis dans les colonnes C/D/E/F/G/H
    Pourriez-vous revoir la formule pour obtenir cette répartition.
    D’avance merci

  3. La mise en page prête à confusion de ma demande précédente
    A = N°
    B= Banque
    C= 1000
    D= 1100
    E = 1200
    F = 1300
    G = 1400
    H = 1500

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut