Oubliez RECHERCHEV() ! La fonction INDEX-EQUIV pour réaliser des recherches complexes sur Excel

Aujourd’hui je vous propose de découvrir comment effectuer des recherches complexes dans Excel en utilisant les fonctions dédiées proposées par le tableur. Nous commencerons donc par découvrir les deux fonctions historiques dédiées à cet usage, c’est-à-dire les fonctions RECHERCHEV() et RECHERCHEH(). Nous verrons alors très rapidement que ces fonctions sont très limitées, et qu’il faut mieux leur préférer la fonction INDEX-EQUIV(), bien plus souple et puissante !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Dans un précédent tutoriel, nous avons déjà eu l'occasion de découvrir comment effectuer des recherches complexes au sein d'une base de données en utilisant les fonctions dédiées : RECHERCHEV() et RECHERCHEH().

Nous avions également vu dans ce tutoriel qu'il existe une autre possibilité qui permet d’effectuer des recherches, qui est encore plus puissante et qui consiste à utiliser un mélange de deux fonctions : les fonctions INDEX() et EQUIV().

Dans ce nouveau tutoriel nous allons revenir sur ces notions en redécouvrant rapidement le fonctionnement des fonction RECHERCHEV et RECHERCHEH, puis en nous attardant davantage sur la puissance de ce que nous pourrions appeler la fonction INDEX-EQUIV.

2. Effectuer une recherche horizontale : la fonction RECHERCHEV()

Comme son nom l’indique, la fonction RECHERCHEV permet d’effectuer une recherche dite verticale au sein d’une base de données.

Comprenez par-là que partant d’un tableau, nous allons chercher à effectuer une recherche sur la première colonne afin d’en extraire la donnée qui correspond et qui est donc située sur la même ligne, mais sur une colonne différente.

Pour bien comprendre cette notion, partons de l’exemple suivant :

Nous avons ici le tableau de détermination de l’EBE mensuel d’une entreprise, et souhaitons dans un premier temps connaitre le montant de cet EBE qui correspond au mois de mars.

Pour cela nous allons donc pouvoir utiliser la fonction RECHERCHEV :

 =RECHERCHEV(K10;A9:G20;7) 

Nous commençons par saisir la valeur qui servira de clé de recherche (le mois de mars, situé en cellule K10).

Ensuite, nous venons saisir les coordonnées de l’ensemble des cellules du tableau d’analyse (les cellules A9 à G20).

Et pour finir, il ne reste plus qu’à renseigner le numéro de la colonne dans laquelle se trouve l’information à extraire de la base (le calcul de l’EBE se trouve dans la septième colonne du tableau).

3. Effectuer une recherche horizontale : la fonction RECHERCHEH()

L’équivalent de la fonction que nous venons de découvrir et qui permet d’effectuer des recherches horizontales est la fonction RECHERCHEH().

Celle-ci fonctionne exactement de la même manière.

Par contre, nous ne pouvons pas utiliser cette fonction avec notre tableau, étant donné que les en-têtes de colonnes ne sont pas classés suivant un ordre alphabétique…

Et nous en arrivons donc aux limites des fonctions de recherches traditionnelles que nous allons maintenant détailler.

4. Les limites de ces fonctions

En effet, comme nous venons de le voir les fonctions RECHERCHEV() et RECHERCHEH() connaissent un certain de limitations qu’il est important de garder en tête :

• La première limite, et surement la plus contraignante, c’est que la colonne sur laquelle nous allons mettre en place la recherche doit OBLIGATOIREMENT être classée par ordre alphabétique ! C’est la raison pour laquelle nous ne pouvons pas simplement saisir les noms de mois dans la colonne mois et que nous avons dû contourner le problème en saisissant tout d’abord le numéro du mois,
• La seconde limite, également très contraignante, c’est que la colonne de recherche doit impérativement être la première colonne de la plage des cellules utilisées dans le second argument de la fonction ! Impossible donc d’extraire une information située sur la gauche de celle-ci
• Et enfin la troisième limite c’est que le numéro de la colonne sur laquelle nous souhaitons extraire l’information doit obligatoirement être renseigné en tant que nombre. Même s’il est possible de la récupérer dynamiquement, cette solution est toujours plus complexe à mettre en place que d’utiliser un index logique, tel que le nom de la colonne. De plus, étant donné qu’il ne s’agit que d’une valeur numérique, et non pas d’une référence à une plage de cellules, le fait de modifier la structure du tableau en ajoutant ou supprimant des colonnes va fausser le résultat retourné par la fonction…

Heureusement, les fonctions que nous venons de (re)voir ici connaissent des alternatives très intéressantes et tout aussi simples à mettre en place.

La première d’entre elles est la toute jeune fonction RECHERCHEX() introduite avec Excel 365, et qui n’est malheureusement pas accessible au plus grand nombre.

La seconde alternative consiste à utiliser un mélange de deux fonctions : INDEX() et EQUIV(), et c’est que nous allons pouvoir découvrir maintenant.

5. La fonction INDEX-EQUIV

Comme nous venons de le voir, la fonction INDEX-EQUIV() est en fait une combinaison de deux fonctions.

   5.1. La fonction INDEX()

Tout d’abord la fonction INDEX() qui permet d’afficher la valeur de la cellule située à une position données d’une plage de cellules.

Dans sa version classique (nous découvrirons la version évoluée plus tard dans la vidéo), la fonction INDEX() attend deux arguments :

= INDEX(matrice;no_ligne) 

Où :

• Matrice : correspond à la plage des cellules dans laquelle se trouve l’information à extraire,
• No_ligne : correspond à la place dans laquelle se trouve l’information à extraire au sein de matrice. Dans le cas présent, la valeur à donner à l’argument no_ligne est déterminée par la formule EQUIV(),

   5.2. La fonction EQUIV()

Ensuite, la formule EQUIV() permet de connaître la position de la cellule que nous recherchons au sein de la plage des cellules correspondante.

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

Où :

• Valeur_cherchée est la valeur de recherche,
• Tableau_recherche est la plage des cellules dans laquelle nous souhaitons effectuer la recherche.
• Le paramètre optionnel [type] permet de spécifier si l’on souhaite effectuer une recherche exacte (dans ce cas type vaut 0), ou alors la valeur la plus élevée qui est inférieure ou égale à celle de valeur_cherchée (1), ou enfin la plus petite valeur qui est supérieure ou égale à celle à valeur_cherchée (-1).

   5.3. La combinaison des fonctions INDEX() et EQUIV()

Dans cette combinaison de fonctions, EQUIV() est à utiliser en tant qu’argument no_ligne de la fonction principale INDEX() et retournera alors la position de l’élément à récupérer au sein de la plage des cellules matrice.

 =INDEX(plage_retour;EQUIV(valeur_cherchée;plage_recherche;[type]))  

Où :

• plage_retour: il s’agit de la plage des cellules (lignes ou colonne) dans laquelle se trouve la valeur à extraire,
• valeur_cherchée : valeur à retrouver dans le plage_recherche,
• plage_recherche : plage des cellules dans laquelle nous souhaitons effectuer la recherche
• [type] : défini le type de recherche, généralement, nous souhaiterons effectuer une recherche exacte, et donc nous lui affecterons la valeur 0

Reprenons l’exemple vu un peu plus tôt, dans lequel nous souhaitons connaître le montant de l’EBE du mois de mars :

 =INDEX(G9:G20;EQUIV(K10;A9:A20;0)) 

Maintenant que nous utilisons cette fonction, nous pouvons sans problème renommer les cellules dans lesquelles se trouvent les mois de l’année :

Le résultat retourné sera alors toujours correct, même si les cellules ne suivent pas l’ordre alphabétique, ce que la fonction RECHERCHEV() ne permettrait pas.

L’autre aspect très intéressant de la formule INDEX-EQUIV() est que l’ordre des colonnes n’a aucune importance. En effet, dans la formule INDEX-EQUIV(), il n’y a pas de connexion directe entre la colonne de recherche et celle qui contient le résultat, aussi leur ordre n’a aucune importance.

Pour illustrer cette notion, nous allons construire la fonction inverse à celle de l’exemple précédent : c’est-à-dire que nous allons chercher à savoir pour quel mois de l’année l’entreprise a dégagée un EBE correspondant au montant obtenu, c’est -à-dire 37757€ :

 =INDEX(A9:A20;EQUIV(K11;G9:G20;)) 

Une autre force de la formule INDEX-EQUIV(), c’est qu’en plus des recherches verticales classiques, nous pouvons également effectuer une recherche horizontale avec exactement la même fonction :

 =INDEX(B11:G11;EQUIV(K9;B8:G8;)) 

Nous pouvons également effectuer des recherches 3D, c’est-à-dire en utilisant un paramètre horizontal et un autre vertical !

Pour cela, pour allons simplement utiliser l’argument facultatif no_col de la fonction INDEX.

Nous allons tout d’abord devoir saisir le numéro de ligne en utilisant la fonction EQUIV, puis dans un second temps le numéro de la ligne de la même manière :

 =INDEX(B9:G20;EQUIV(K10;A9:A20;);EQUIV(K9;B8:G8;))  

Bien entendu, à ce moment-là la plage des cellules dans laquelle se trouve le résultat à extraire doit être constituée d’un nombre de lignes et de colonnes équivalent aux plages appelées par la suite.

Et plus de cela, nous pouvons modifier la structure du tableau en ajoutant ou supprimant des lignes ou colonnes, le résultat sera toujours correct !

En conclusion, en plus d’être très puissance, la formule INDEX-EQUIV, telle que nous venons de la découvrir permet également de comprendre que parfois nous pouvons détourner Excel de l’usage prévu à l’origine par le développeur pour arriver à nos fins !