Comment afficher une valeur présente dans un tableau et qui répond à deux critères ? Avec INDEX & EQUIV

Bonjour à toi qui est derrière ton écran ! Ici c’est Noël de Kitcreanet et aujourd’hui on va répondre à une problématique sous Excel : Comment afficher une valeur présente dans un tableau et qui répond à deux critères ?

Exemple avec ce tableau de gestion des cotisations pour un club de sport.

Quel sera le montant de la cotisation pour Jean qui est un vétéran et qui a une licence Loisir ? Si je regarde dans le petit tableau de référence, je constate que c’est 30€, mais comment afficher ça automatiquement dans la colonne COTISATION ?

Pour afficher le montant de la cotisation, nous allons faire appel à deux fonctions très puissantes pour les recherches que nous allons utiliser conjointement et de façon imbriquée : les fonctions INDEX et EQUIV, ou INDEX et MATCH si tu travailles sur le tableur de Google — comme dans mon exemple. Donc, à chaque fois que tu vois MATCH dans ce tuto, il faut que tu remplaces par EQUIV si tu travailles avec Excel. 

Si tu veux en savoir plus sur ces fonctions et sur beaucoup d’autres, je t’invite à télécharger mon petit Excel illustré, un ebook gratuit de plus de 100 pages qui te donnera toutes les clefs pour bien démarrer avec Excel.

Commençons par analyser notre fichier de travail. Nous avons un tableau de référence qui contient nos tarifs par catégorie de joueurs et par type de licence. On appelle ça aussi une matrice. 

Notre matrice démarre en B2 et s’achève en D5.

Puisque JEAN appartient à la catégorie des VÉTÉRANS et qu’il possède une licence LOISIR, nous devons afficher le montant de sa cotisation qui se trouve : 

• à la ligne n° 4 de la matrice (puisqu’il appartient à la catégorie des vétérans)
• et dans la colonne 2 de cette même matrice (qui contient les tarifs pour la licence LOISIR).

Pour ça on va utiliser la fonction INDEX, dont c’est précisément le rôle : afficher ce qui se trouve à une intersection dans un tableau !

En E8, je vais donc utiliser la fonction INDEX. La fonction INDEX a besoin de trois arguments pour fonctionner : 

Premier argument, je dois indiquer la table matrice qui contient mes données. Ici il est important de bien sélectionner toute la table, à savoir de B2 à D5. Avant de taper le second argument, je place un point-virgule.

Deuxième argument,  je dois indiquer le numéro de la ligne où se situe la catégorie que je cherche : VÉTÉRANS est sur la 4e ligne de ma matrice de recherche, je tape donc 4. Et je tape un autre point virgule.

Troisième et dernier argument, je dois maintenant indiquer dans quelle colonne de ma matrice de recherche se situe la licence LOISIR. Ici c’est la colonne n°2, je vais donc taper 2. Je ferme la parenthèse et je valide par ENTRÉE.

=INDEX(B2:D5;4;2)

Le résultat s’affiche, c’est bien 30,00 €.

Oh la oh la oh la… ARNAQUE me direz-vous, il n’y a rien d’automatique là-dedans, puisque j’ai saisi moi-même le numéro de ligne et de colonne. 

C’est vrai. Mais c’est parce-que nous n’avons pas fini ! 

En effet, ici, si je modifie le type de licence pour passer en COMPÉTITION, le montant de la cotisation ne changera pas car la fonction INDEX continue à faire ce que je lui ai demandé, à savoir afficher ce qui se trouve à la ligne 4 dans la colonne 2 de ma matrice. Et c’est bien 30,00 €. Pour afficher le montant correct de 35,00 €, je vais devoir indiquer à INDEX le bon numéro de colonne. Si je corrige 2 par 3, le montant correct s’affiche. 35,00€.

Ce qu’il faudrait c’est que la fonction puisse lire directement ce qui se trouve dans la cellule D8, aller voir à quelle colonne se situe ce mot dans la matrice de recherche et fournir le bon numéro de colonne directement. Mais ça, INDEX ne sait pas le faire. La fonction va avoir besoin d’un petit coup de pouce.

Pour aider la fonction INDEX() à récupérer de façon automatique les numéros de ligne et de colonne, on va utiliser une autre fonction, la fonction EQUIV().

EQUIV() ça sert à chercher quelque chose dans une plage et la fonction renvoie la position de l’élément trouvé dans cette même  plage. C’est exactement ce dont nous avons besoin !

Par exemple, si je demande à EQUIV de chercher dans la plage B2:D2 le mot LOISIR, elle va me donner comme réponse 2. Puisque LOISIR est trouvé dans la seconde cellule de la plage B2:D2.

De la même manière, si je demande à EQUIV de me chercher le mot VÉTÉRANS dans la plage B2:B5, la réponse sera 4 puisque VÉTÉRANS se trouve dans la quatrième cellule de la plage de recherche B2:B5.

On va donc devoir dire à EQUIV ce qu’il doit chercher, et où il doit le chercher.

Comme souvent lorsque l’on fait appel à des formules qui comprennent des fonctions imbriquées, il est plus facile de décomposer son travail avant de créer la formule définitive. C’est ce que nous allons faire.

J’ai préparé un petit tableau pour décomposer notre formule, fonction par fonction.

Retapons rapidement en C13 notre fonction INDEX. Je tape ÉGAL INDEX, j’ouvre la parenthèse, et j’indique la plage dans laquelle va s’effectuer la recherche : B2:D5. puis un point virgule, puis la ligne 4, point virgule, puis la colonne 3, je ferme la parenthèse et je valide par ENTRÉE. Ici, nous avons fait exactement la même chose que dans la cellule E8. 

L’objectif maintenant, est de remplacer le numéro de ligne et le numéro de colonne que j’ai saisis manuellement, par la fonction EQUIV qui va aller chercher pour moi cette information.

Dans la cellule du dessous (C14) nous allons afficher le numéro de ligne dans la matrice où se trouve la catégorie du joueur.

Je vais donc utiliser EQUIV qui a besoin de 3 arguments pour fonctionner : 

• Premier argument, ce que je recherche, ici la catégorie du joueur qui est contenu dans la cellule C8.
• Deuxième argument, la plage de cellule de notre matrice dans laquelle je vais chercher ce texte, ici ce sera donc  B2:B5. Attention ici à ne pas faire l’erreur de ne pas prendre la cellule B2. Certes, elle est vide mais elle fait partie intégrante de la matrice de recherche dans laquelle INDEX va chercher…
• Troisième et dernier argument, le type de recherche, et sans rentrer dans les détails, nous voulons un résultat exact et dans la fonction EQUIV(), ça se traduit par un 0.

Donc on y va ! 

Je tape ÉGAL EQUIV, puis j’indique ce que je recherche. Ici, je cherche la catégorie à laquelle appartient Jean, je vais donc indiquer la cellule C8 puisque c’est dans cette cellule que la catégorie de Jean est affichée.

Je saisis un point virgule puis j’indique à EQUIV la plage au sein de la matrice où aller chercher cette information. B2:B5. Un point virgule et un zéro pour indiquer que je souhaite un résultat exact. Fermeture de la parenthèse et Entrée. J’obtiens comme résultat : 4. C’est correct, Jean est un VÉTÉRAN, et les vétérans sont à la ligne 4 de la matrice.

Regardons tout de suite ce qu’il se passe si je change de catégorie. Si je choisis ADULTES, j’obtiens 3. Et si je choisis JUNIOR, j’obtiens 2. Super !

On va faire la même chose pour la colonne dans la cellule du dessous (C15).

Je tape égal EQUIV, puis j’ouvre la parenthèse. Je recherche le type de licence de Jean, je vais donc indiquer la cellule D8. Je tape un point virgule puis la plage où chercher cette information dans la matrice. B2:D2, un point virgule et zéro pour un résultat exact. Fermeture de la parenthèse et Entrée. J’obtiens le résultat attendu : 3. Compétition est bien dans la troisième colonne de la matrice. Je change le type de licence pour une licence LOISIR et j’obtiens 2 ce qui est bien le numéro de colonne qui contient LOISIR.

Maintenant que nous avons les deux fonctions pour afficher notre n° de ligne et notre n° de colonne, on va pouvoir compléter notre formule initiale en remplaçant les paramètres 2 et 3 de la fonction INDEX par ces deux fonctions EQUIV(). C’est un bel exemple de fonctions imbriquées pour une formule un peu complexe. 

Modifions la formule de calcul en E8.

Le premier paramètre ne change pas, notre matrice reste identique : B2:D5.

Supprimons le second paramètre (le numéro de ligne que j’avais saisi manuellement) et remplaçons le par la fonction EQUIV qui fait le travail à ma place :

Vous pouvez copier-coller la formule (sans le symbole égal) ou la retaper complètement. Retapons-la !

je tape EQUIV, parenthèse, C8 qui est la cellule qui contient ce que je cherche, un point virgule et la plage de recherche B2:B4, un point virgule et un zéro 0 pour un résultat exact. Je ferme la parenthèse.

On remplace maintenant le troisième paramètre (le numéro de colonne que j’avais saisis manuellement) par la fonctions EQUIV qui fait le même travail : 

Je tape EQUIV, parenthèse, D8 qui est la cellule qui contient ce que je cherche (le type de licence pour Jean), un point virgule et la plage de recherche dans la matrice B2:D2 , puis un point virgule et un zéro 0 pour un résultat exact. Je ferme la parenthèse, et je valide par ENTRÉE.

La formule fonctionne et me renvoie 10,00 € ce qui est bien le montant de la cotisation pour un membre JUNIOR avec une licence LOISIR.

Changeons la catégorie : Passons en ADULTE. Si je regarde dans la matrice, un adhérent ADULTE en LOISIR paye 20,00 €. C’est OK.

Et si je passe en VÉTÉRANS / COMPÉTITION, le résultat passe à 35,00 €, c’est exact.

Bien, il ne nous reste plus qu’à faire glisser notre cellule avec la poignée de recopie pour recopier la formule dans les cellules du dessous.

Oups.. J’ai tout cassé ! Qu’est-ce qui ne fonctionne pas ?

Ne vous inquiétez pas, tout est sous contrôle. C’est une petite erreur que j’ai fait sciemment pour pouvoir y revenir à présent. 

Lorsque l’on fait glisser une formule avec la poignée de recopie, Excel utilise des valeurs relatives pour les formules recopiées et c’est ce qui provoque notre erreur… Regardez la fonction INDEX en E8 : La matrice indiquée et B2:D5. CE QUI EST CORRECT.

Mais en descendant d’une ligne, elle est devenue B3:D6. Et ce décalage s’est produit dans toutes les plages mentionnées dans notre formule, la plage mentionnée dans la fonction INDEX, mais également les plages mentionnées dans les deux fonctions EQUIV. Or, ces plages ne doivent jamais changer car c’est dans ces plages et dans ces plages seulement que les recherches doivent s’effectuer.

Il nous reste donc une petite chose à faire pour que tout fonctionne correctement : utiliser des références absolues.

— Vous pouvez consulter mon tuto sur les références relatives et absolues dans la description de la vidéo.

Revenons dans notre cellule E8. Placez votre curseur sur les plages indiquées dans vos fonctions et bloquez-les pour en faire des références absolues, avec le raccourci clavier F4 sur Windows ou Fn F4 sur les ordinateurs Apple. Ça revient à placer des signes $ devant les intitulés de ligne et de colonne de vos plages. Ainsi B2:D5 devient $B$2:$D$5. Faites cette manipulation pour la plage de la fonction INDEX et pour les plages des deux fonctions EQUIV.

Ceci étant fait, vous pouvez de nouveau faire glisser la formule vers le bas et cette fois-ci tout fonctionne parfaitement !

Voilà, tu sais désormais comment rechercher une valeur dans un tableau bi-dimensionnel (c’est-à-dire avec deux critères de recherche) avec les fonctions INDEX et EQUIV ! Si tu veux reproduire l’exercice, tu trouveras un lien dans la description de la vidéo pour télécharger un fichier au format Excel qui contient l’exercice et son corrigé.

C’est tout pour cette fois, j’espère que ce tuto t’a plu, n’hésite pas à me laisser un petit commentaire.C’était Noël de Kitcreanet, à bientôt !