Après avoir vu la fonction RECHERCHEV, vous allez voir aujourd’hui comment rechercher une valeur dans un tableau Excel à l’aide de la fonction EQUIV.
Vous découvrirez une application pratique de cette fonction : la comparaison de 2 listes de valeurs.
Puis vous verrez comment EQUIV, couplée à la fonction INDEX, peut pallier les limitations de la fonction RECHERCHEV.
Présentation de la fonction EQUIV d’Excel
Comme son nom ne l’indique pas, EQUIV permet de rechercher une valeur dans une plage de cellules, typiquement une colonne de tableau. Elle renvoie le numéro de la ligne qui contient la valeur recherchée.
Reprenons l’exemple du catalogue de fournitures de notre artisan couvreur, développé dans l’article précédent :
Pour rechercher le numéro de ligne correspondant à la référence « ITRI24-16 » par exemple, on utilise la formule suivante :
EQUIV("ITRI24-16";tabCatalogue[Référence];0)
Cette formule renvoie la valeur 5, car la valeur recherchée est située sur la 5ème ligne de la colonne Référence.
Le dernier paramètre (0) indique qu’on ne veut pas de recherche approximative, c’est-à-dire que la correspondance doit être exacte.
Plutôt simple, n’est-ce pas ?
En fait, seul le dernier paramètre demande une vigilance particulière. Il peut prendre 3 valeurs différentes :
- 0 pour faire une recherche exacte
- -1 pour rechercher la plus petite valeur supérieure ou égale à celle passée en premier paramètre. Dans ce cas, le tableau doit être trié de façon décroissante selon la colonne spécifiée.
- 1 pour rechercher la grande valeur inférieure ou égale à celle passée en premier paramètre. Dans ce cas, le tableau doit être trié de façon croissante selon la colonne spécifiée.
La fonction EQUIV est détaillée sur cette page de la documentation Excel de Microsoft.
Mais à quoi peut bien servir le numéro de ligne obtenu ?
C’est ce que nous allons voir dans l’exemple suivant.
Trouver les éléments communs entre 2 listes avec EQUIV
Dans Excel, EQUIV est très utile pour trouver les éléments communs (ou au contraire différents) entre deux listes.
Pour comprendre comment, reprenons notre exemple précédent.
Supposons que notre artisan veuille vérifier si le catalogue qu’il utilise pour ses devis est assez complet. Pour cela, il aimerait le comparer avec le catalogue de son fournisseur de bois.
Il aimerait colorer toutes les lignes du catalogue de son fournisseur qui sont aussi présentes dans son propre catalogue. Et pour être plus précis, les lignes qui ont le même code de référence (puisque c’est ce qui identifie une ligne dans cet exemple).
Ci-dessous un exemple visuel de ce qu’il aimerait obtenir.
NB/ Le catalogue est ici extrêmement réduit, pour des raisons de simplification.
En orange, les lignes pour lesquelles le code a été trouvé dans la colonne Référence du catalogue de l’artisan.
Nous supposerons ici que les 2 catalogues (celui de l’artisan et celui du fournisseur) sont dans 2 feuilles du même classeur Excel, mais ce n’est absolument pas une obligation.
Pour colorer les lignes en orange, on peut appliquer une mise en forme conditionnelle sur le tableau, avec la condition suivante :
=EQUIV($A2;INDIRECT("tabCatalogue[Référence]");0)
Explications
$A2 est la première cellule de la colonne Code du catalogue du fournisseur. C’est une référence mixte. Comme la mise en forme conditionnelle est appliquée sur un objet tableau, la formule est adaptée automatiquement à toutes les lignes du tableau, et la référence est incrémentée automatiquement en $A3, $A4… etc.
Dans les formules de mises en forme conditionnelles, on ne peut pas faire directement référence à une colonne de tableau, comme nous l’avons fait jusqu’ici. Il faut y faire référence de façon indirecte sous forme de chaîne de caractères, avec la fonction… INDIRECT. C’est pourquoi on écrit INDIRECT(« tabCatalogue[Référence] »).
Pour savoir comment appliquer cette mise en forme conditionnelle à l’ensemble du tableau, allez vite voir cette vidéo !
Et si ce n’est pas déjà fait, lisez mon livret gratuit pour bien comprendre tous les avantages des objets tableaux (cf. formulaire en bas de page pour le télécharger) !
Variante : trouver les éléments non communs entre deux listes
Si on voulait au contraire mettre en évidence les articles du fournisseur qui ne sont pas présents dans le catalogue de l’artisan, on utiliserait la formule suivante :
=ESTNA(EQUIV($A2;INDIRECT("tabCatalogue[Référence]");0))
En effet, comme RECHERCHEV, EQUIV renvoie une erreur #N/A lorsque la valeur recherchée n’est pas trouvée. Dans ce cas, la fonction ESTNA renvoie vrai, ce qui permet d’appliquer la mise en forme uniquement aux lignes non trouvées. On pourrait aussi utiliser la fonction ESTERREUR à la place de ESTNA.
Remplacer RECHERCHEV par EQUIV et INDEX
La fonction EQUIV d’Excel peut également vous rendre de fiers services lorsque vous êtes confronté à la limitation de RECHERCHEV.
Les limites de la fonction RECHERCHEV
Si vous ne la connaissez pas bien, je vous invite à découvrir cette fonction incontournable d’Excel dans cet exemple typique d’utilisation.
RECHERCHEV permet de récupérer une information dans une ligne de tableau. La ligne est identifiée par la valeur de la première colonne, tandis que l’information à récupérer est précisée par son numéro de colonne, passé en paramètre.
On peut rechercher par exemple le prix d’un article à partir de sa référence avec la formule suivante :
=RECHERCHEV("ITRI24-16";tabCatalogue;5;FAUX)
Dans laquelle :
- « ITRI24-16 » est la référence qui sera recherchée dans la première colonne du tableau tabCatalogue
- 5 est le numéro de la colonne qui contient le prix
L’avantage de RECHERCHEV est qu’elle renvoie directement la valeur demandée (ici le prix), et non pas seulement un numéro de ligne.
En revanche, la colonne qui sert à rechercher (ici la référence) doit obligatoirement être la première colonne du tableau, et pas une autre. Dans certains cas, cette limitation peut être gênante. Elle interdit par exemple d’utiliser cette fonction sur des critères (colonnes) différents dans un même tableau.
EQUIV ne présente pas ce problème, puisqu’elle permet de spécifier le nom de la colonne de recherche. Cependant, elle ne fait que la moitié du travail, car elle renvoie simplement un numéro de ligne, et non la valeur d’une colonne. C’est pourquoi, il faut la coupler avec une autre fonction d’Excel : INDEX
La fonction INDEX
Index renvoie justement la valeur d’une colonne pour un numéro de ligne donné.
Exemple :
INDEX(tabCatalogue[PU HT];5)
…renvoie la valeur de la colonne « PU HT » pour la ligne numéro 5, c’est-à-dire « 14,00 » dans notre exemple. Humm, intéressant…
La fonction INDEX est détaillée sur cette page de la documentation Microsoft.
Combiner INDEX et EQUIV
Vous devinez maintenant sans doute comment remplacer
RECHERCHEV par EQUIV et INDEX ?
Oui, c’est bien ça, nous allons utiliser EQUIV à l’intérieur d’INDEX, comme
ceci :
=INDEX(tabCatalogue[PU HT]; EQUIV("ITRI24-16";tabCatalogue[Référence];0))La seconde partie renvoie le numéro de ligne de la valeur recherchée dans la colonne Référence, c’est-à-dire 5.
La première partie renvoie la valeur du prix pour cette ligne, c’est-à-dire 14,00 dans notre exemple.
Cette formule est certes plus compliquée que celle utilisant RECHERCHEV, mais elle présente un avantage majeur : on peut préciser le nom de la colonne dans laquelle rechercher la valeur (ici, la colonne Référence). Cette colonne peut donc être située à n’importe quelle position dans le tableau.
Ainsi, si la colonne Référence n’était pas en première position dans le tableau, on ne pourrait pas utiliser RECHERCHEV, mais on pourrait utiliser INDEX et EQUIV.
Dans quel cas utiliser RECHERCHEV ou EQUIV dans un tableau Excel ?
Si les données du tableau dans lequel vous recherchez sont identifiées par une colonne unique, le plus simple est de mettre cette colonne en premier et d’utiliser RECHERCHEV pour récupérer les valeurs des autres colonnes.
En revanche, si les données du tableau dans lequel vous recherchez peuvent être identifiées par plusieurs colonnes (exemple : un numéro ou un nom) et que vous souhaitez pourvoir utiliser indifféremment l’une ou l’autre, utilisez EQUIV et INDEX
En synthèse
Vous avez vu deux exemples typiques d’utilisation de la fonction EQUIV d’Excel :
- La recherche des éléments communs (ou différents) entre 2 listes
- La récupération de valeurs dans un tableau, dans le cas où RECHERCHEV ne peut pas être utilisée
Au travers des exemples, vous avez vu aussi comment associer différentes techniques pour obtenir le résultat souhaité :
- Faire référence à une colonne de tableau dans une condition de mise en forme conditionnelle, grâce à INDIRECT
- Comment EQUIV et INDEX se complètent pour faire un équivalent de RECHERCHEV
- Traiter la valeur #N/A retournée par EQUIV à l’aide de la fonction ESTNA
Ces techniques, ainsi que celles exposées dans mon e-book ci-dessous, vous seront très utiles dans de nombreuses situations. Entraînez-vous à les utiliser si elles ne sont pas déjà familières pour vous 😉