Excel est assez puissant pour faire des recherche sur une base de données.
La fonction la plus utilisée est RECHERCHEV. Mais, elle a un gros défaut : elle ne peut retourner les résultats qu’à la droite de la valeur cherchée.
Mais il y a une alternative ! (plusieurs en fait). Pour ceux qui n’ont pas Excel l’abonnement 365 ou une version perpétuelle récente, il vous faut utiliser 2 fonctions d’Excel : INDEX et EQUIV.
Syntaxe et objectif
EQUIV
La fonction EQUIV permet de trouver le numéro de ligne où se trouve une donnée.
Elle s’écrit :
=EQUIV(valeur_cherchée;tableau_recherche;[type])
avec
- valeur_cherchée : valeur/cellule dont on recherche le numéro de ligne dans un tableau/une base
- Tableau_recherche : plage de données dans laquelle se trouve toutes les valeurs
- [type] : argument facultatif. saisissez 0 pour trouver la valeur exacte, 1 pour la valeur la plus élevée qui est inférieure ou égale à Valeur_cherchée ou -1 pour la plus petite valeur qui est supérieure ou égale à Valeur_cherchée.
INDEX
La fonction INDEX permet de trouver une cellule dans une plage de données en fonction de son numéro de ligne.
=INDEX(matrice;no_lig;[no_col])
avec :
- matrice : plage de données où l’on recherche le résultat
- no_ligne : numéro de la ligne où se trouve le résultat
- [no_col] : il s’agit d’un argument facultatif par défaut. Il correspond au n° de colonne dans la plage recherchée (si l’argument matrice contient plusieurs colonnes).
Pourquoi utiliser INDEX+EQUIV plutôt que RECHERCHEV ?
Il y a plusieurs raisons à cela. Je vous en cite au moins trois :
- La recherche peut se faire dans tous les sens alors que la RECHERCHEV ne peut se faire que de gauche à droite
- Quand vous ajoutez ou supprimez une colonne dans votre base de données, vos formules ne sont plus correctes à cause de l’argument no_index_col
- Vous pouvez mettre plusieurs critères de recherche dans une formule de calcul avec INDEX et EQUIV alors qu’il n’y a qu’un critère dans RECHERCHEV.
Créer une recherche
Maintenant qu’on a vu la théorie, mettons en pratique à l’aide d’un exemple.
J’ai fait la liste de produits et je veux trouver la référence d’un objet en fonction de son libellé.
1ère étape : recherche du numéro de ligne
La première chose à faire est de trouver à quelle ligne du tableau correspond le produit que je recherche. Pour cela, j’utilise la fonction EQUIV.
- valeur_cherchée : il s’agit du libellé de produit dans mon tableau de recherche. Comme le libellé change à chaque ligne, on ne fixe pas avec un dollar
- Tableau_recherche : plage de données dans ma base de produits : C2:C17 Quelque soit ma ligne dans mon tableau de recherche, la plage ne doit pas changer. On fixe donc la plage avec des dollars : $C$2:$C$17.
- [type] : je recherche la valeur exacte, je mets donc 0.
Dans G3, ma formule de calcul donne :
=EQUIV(F3;$C$2:$C$17;0)
2ème étape : Rechercher la valeur qui correspond au numéro de ligne
Maintenant qu’on connaît le numéro de ligne pour chaque produit, on peut trouver la référence pour ces lignes respectives
- matrice : plage de données des références (A2:A17). Quelque soit la ligne de mon tableau de recherche, ma plage de références doit rester la même : on la fixe en $A$2:$A$17.
- no_ligne : numéro de la ligne où se trouve le résultat qu’on retrouve dans la colonne G. Comme le numéro de ligne change, on a pas besoin de fixer avec un $.
On obtient :
=INDEX($A$2:$A$17;G3)
3ème étape : fusionner les deux fonctions
=INDEX($A$2:$A$17;EQUIV(F3;$C$2:$C$17;0))
Défaut d'INDEX et EQUIV
Comme toute fonction, elle possède des avantages mais aussi des inconvénients.
Les deux fonctions ne peuvent être utilisées dans tous les cas de figure.
Prenons un exemple :
J’ai récupéré la liste des communes françaises sur l’INSEE.
Dans mon fichier Excel, je veux récupérer les informations d’une commune précise.
En H2, j’ai créé une liste déroulante pour la liste des communes.
En cellule H6 et I6, je vais rechercher les informations de la commune sélectionnée.
Dans les cellules H6 et I6, je créer mes formules de calcul avec les fonctions INDEX et EQUIV. On obtient respectivement :
=INDEX($A$2:$A$50;EQUIV($H$2;$E$2:$E$50))
=INDEX($F$2:$F$50;EQUIV($H$2;$E$2:$E$50))
Si je sélectionne la commune Abancourt, j’obtiens le code COM 60001 et le code CAN 6011.
Or, il y a un problème : il existe 2 communes Abancourt.
INDEX et EQUIV vont récupérer que la première ligne de mon tableau où apparaît Abancourt.
