Maîtriser les fonctions de recherche d'Excel : Guide Complet

L’importance de la recherche

La recherche est un élément fondamental d’Excel, quel que soit l'usage que vous en faites, vous avez sans doute déjà dû chercher des données. Mais faire une recherche peut être compliqué, en particulier lorsque la taille des tableaux augmente.

Ce problème n’est pas nouveau. C’est pourquoi, dès sa première version, Excel a eu des fonctions pour chercher des données.

Malheureusement, elles font partie des fonctions causant le plus d’erreurs, mal utilisées par de nombreuses personnes. En effet, elles peuvent être difficiles à comprendre et utiliser, et les messages d’erreurs d’Excel ne sont pas toujours clairs.

Dans cet article, je vais vous aider à comprendre comment marche la recherche sur Excel, comment l’utiliser et comment choisir la fonction de recherche la plus adaptée à votre situation.

La fonction RechercheV

On commence par certaines des fonctions les plus utilisées et les plus anciennes d’Excel : La RechercheV (VLookup en anglais) permettant de faire une recherche à la verticale et la RechercheH (HLookup en anglais), son équivalent à l’horizontal.

Malgré leurs 35 ans, elles restent parmi les fonctions des plus utilisées encore aujourd’hui aux côtés des fonctions Somme & Moyenne !

La RechercheV permet de chercher une valeur dans la 1ère colonne d'un tableau et d’extraire une valeur de cette ligne dans la colonne de son choix. La RechercheH fonctionne de la même manière, mais à l’horizontale (ici on se concentrera sur la RechercheV, mais tout s’applique également à la RechercheH).

La fonction se présente comme ceci

RECHERCHEV (valeur_cherchée; matrice; no_index_col)

valeur_cherchée: la valeur recherchée dans la 1ère colonne de la matrice.
matrice: le tableau de donnée dans lequel on veut faire notre recherche. Il doit contenir toutes les informations.
no_index_col: l'index de la colonne dans laquelle se trouve la donnée que l’on veut extraire.

La valeur cherchée doit être dans la 1ère colonne du tableau.
La valeur cherchée est insensible à la casse, c’est-à-dire aux majuscules & minuscules, donc “clé” matchera avec “Clé”.
Si la valeur cherchée est présente plusieurs fois dans le tableau, seule la 1ère occurrence sera sélectionnée.
La valeur cherchée et la cellule doivent avoir le même format, par exemple si la valeur cherchée est la chaîne de caractères 1 et une cellule contient le chiffre 1, il n’y aura pas de correspondance !

Exemple d'utilisation de la fonction RechercheV sur Excel

Exemple d'utilisation de la RechercheV

Supposons que l’on a un tableau contenant les identifiants des employés et leurs informations. On peut utiliser la RechercheV, en utilisant l’identifiant comme valeur cherchée et l'index de la colonne contenant l’information que l’on veut, afin d'accéder aux informations d’un employé.

Dans l’exemple à droite, on utilise une RechercheV pour chercher le contenu de la colonne 4 (l'adresse) de l'employée ayant l'identifiant "2".

L'index de colonne doit faire référence à une colonne qui fait partie de la sélection, si on avait entré 5 pour faire référence à la colonne “Téléphone” on aurait eu une erreur.

La RechercheV peut aussi prendre un autre paramètre facultatif "valeur_proche" qui permet de choisir le type de correspondance.

RECHERCHEV (valeur_cherchée; matrice; no_index_col, valeur_proche)

Si l’argument vaut Faux la recherche sera exacte, c’est-à-dire qu’Excel cherchera la clé exactement, s’il ne la trouve pas, il renverra une erreur.
Si l’argument vaut Vrai la recherche sera approximative, Excel cherchera la clé, s’il ne la retrouve pas, il renverra la valeur plus petite la plus proche.

Par exemple si on reprend l’exemple précédent, si on utilise 10 comme valeur cherchée, avec la correspondance exacte on aura une erreur, mais avec la correspondance approximative on aura, le résultat précédent, c’est-à-dire celui qui correspond à l’ID 9.

Pour que la correspondance approximative fonctionne correctement le tableau doit être trié dans l’ordre croissant

La plupart du temps vous aurez besoin d’une correspondance exacte. Malheureusement, la valeur de base de l’argument est VRAI, et donc une comparaison inexacte. Penser bien à ajouter cet argument si vous voulez une comparaison exacte.

Les valeurs retournées par la RechercheV

La RechercheV retourne soit la valeur trouvée soit une erreur, les erreurs possibles sont :

#N/A : La valeur cherchée n’est pas trouvée. Si vous faites une recherche approximative , cette erreur s’affichera, si votre clé est plus petite que la 1re valeur du tableau
#REF! : La colonne indiquée ne fait pas partie du tableau du tableau
#NOM! : Excel ne trouve pas l’un de vos arguments

Vous pouvez utiliser la fonction SIERREUR (IFERROR en anglais) pour mettre une valeur spécifique lorsqu’il y a une erreur.

Comme nous l’avons vu, la RechercheV est une fonction très puissante et pratique pour chercher des données, cependant elle a aussi des défauts.

La valeur cherchée doit être la 1ère colonne du tableau. La RechercheV ne peut pas faire de recherche vers la gauche, seulement vers la droite.
L'index de la colonne est un nombre, si on supprime, ajoute et déplace une colonne, la fonction ne marchera plus !
La correspondance est approximative par défaut
La recherche est seulement verticale, il faut utiliser une rechercheH pour le faire dans l’autre sens

Ces limitations rendent la RechercheV moins flexible. Elles vous forcent à structurer votre tableau d’une manière spécifique et peuvent causer des erreurs difficiles à repérer.

Pour en savoir plus vous pouvez aller voir notre article dedié à la rechercheV

La fonction Equiv (Match en anglais)

L’un des moyens d'éviter que la recherche ne cesse de fonctionner lorsque l'on modifie la structure des colonnes est d’utiliser la fonction Equiv. Elle permet de chercher une valeur et de renvoyer sa position. On peut s'en servir pour obtenir l'index d'une colonne via son en-tête et donc de ne plus avoir à saisir manuellement d'index de colonne.

EQUIV (valeur_cherchée, matrice_recherche, [type])

valeur_cherchée : la valeur recherchée.
matrice_recherche: le tableau dans lequel se trouve la valeur recherchée.
type (facultatif) : le mode de correspondance (par défaut 1)
0 : correspondance exacte.
1 : correspondance approximative. Si la valeur cherchée n'est pas trouvée, renvoie la valeur inférieure ou égale la plus proche. Le tableau doit être tiré dans l’ordre croissant.
-1 : correspondance approximative. Si la valeur cherchée n'est pas trouvée, renvoie la valeur supérieure ou égale la plus proche. Le tableau doit être tiré dans l’ordre décroissant.

Tout comme pour la RechercheV, par défaut le type de correspondance est approximatif
La matrice doit être une ligne ou une colonne

Exemple d'utilisation de la fonction Equiv

Reprenons l'exemple précédent.

En utilisant la fonction Equiv pour avoir l'index de la colonne adresse, on peut maintenant supprimer, ajouter ou déplacer des colonnes sans risque que la formule arrête de marcher !

Cependant, cette méthode demande d’utiliser deux fonctions, ce qui la rend un peu plus compliquée à lire et utiliser.

De plus, elle ne suffit pas à corriger les autres défauts de la RechercheV tels que l'obligation d’avoir la valeur cherchée dans la première colonne du tableau ou l'impossibilité de faire une recherche vers la gauche.

Le combo Index / Equiv (Index / Match en anglais)

Afin de corriger les autres problèmes de la RechercheV, il faut utiliser une autre fonction, la fonction Index.

INDEX(matrice; no_lig; no_col)

matrice: Le tableau dans lequel se trouve la valeur recherchée
no_lig : le numéro de la ligne de la valeur recherchée
no_col : le numéro de colonne de la valeur recherché

Si la matrice est une ligne ou une colonne, vous pouvez n’utiliser qu’un des arguments no_lig et no_col

Exemple d'utilisation des fonctions de recherche Equiv & Index sur Excel

Exemple d'utilisation des fonctions Equiv & Index

En utilisant Index et Equiv ensemble, on peut avoir le même comportement qu’avec une RechercheV ou une RechercheH. Il n’est plus nécessaire d’avoir la clé en première colonne du tableau et il est désormais possible de faire une recherche vers la gauche !

Cette méthode est aussi plus rapide qu’une RechercheV, ce qui peut être utile si l’on manipule de grands tableaux.

La fonction RechercheX

Mais, cette méthode toujours des problèmes, par exemple, le besoin d'utiliser deux fonctions. Cela rend la formule plus compliquée à lire et utiliser qu'une RechercheV.

Ce n’est pas vraiment surprenant, ces fonctions sont vieilles de plus de 30ans. À l’époque Excel était loin d'être le géant qu’il est aujourd’hui.

Microsoft est bien conscient de ce problème. C’est pour cela qu’il y a peu, une nouvelle fonction a été développée afin de régler ces problèmes et de simplifier la recherche.

La RechercheX !

Cette fonction simplifie la recherche sur Excel et combine les points forts de la RechercheV et de la combinaison Index/Equiv en une seule fonction simple à utiliser.

La RechercheX n’est accessible que sous Microsoft 365 (anciennement Office 365), si vous utilisez une autre version, vous devrez utiliser la RechercheV ou Index/Equiv.

Cette fonction a beaucoup d’arguments, ce qui peut être intimidant au début, mais la majorité du temps vous n’aurez à en utiliser que quelques-uns.

RECHERCHEX (Valeur_cherchée; tableau_recherche; tableau_result; si_non_trouvé; mode_correspondance; mode_recherche)

valeur_cherchée : La valeur recherchée.
tableau_recherche : La colonne ou ligne contenant la valeur recherchée.
tableau_renvoyé : Le tableau ou les valeurs trouvés à renvoyer.
Si_non_trouvé (facultatif) : La valeur à afficher si la valeur cherchée n’est pas trouvée.
mode_correspondance (facultatif): Le mode de correspondance
0 : Correspondance exacte.
-1 : Correspondance approximative. Si la valeur cherchée n'est pas trouvée, renvoi la valeur inférieure ou égale la plus proche.
1 : Correspondance approximative. Si la valeur cherchée n'est pas trouvée, renvoi la valeur supérieure ou égale la plus proche.
2 : Correspondance générique. Les caractères spéciaux “*” et “?” peuvent en remplacer d’autres.
mode_recherche (facultatif): Le mode de recherche
1 : Recherche du 1er au dernier élément
-1 : Recherche du dernier au 1er élément
2 : Recherche dichotomique croissante : une méthode de recherche très rapide, mais qui nécessite d’avoir un tableau trié dans l’ordre croissant
-2 : Recherche dichotomique décroissante : une méthode de recherche très rapide, mais qui nécessite d’avoir un tableau trié dans l’ordre décroissant

Par défaut la correspondance est exacte !
Plus besoin d'utiliser SIERREUR pour gérer les erreurs.
Plus besoin de trier son tableau pour utiliser une recherche approximative
Choisir un mode de recherche adapté peut accélérer votre recherche.
Le fait d’utiliser un tableau de recherche au lieu d'un index, fait que l’ajout ou la suppression de colonnes ne casse plus la formule.
Il est possible de renvoyer plusieurs valeurs.

Si vous utilisez une recherche dichotomique avec un tableau non trié, vous n’aurez pas de message d’erreur, mais risquez d’avoir un mauvais résultat.
Si les deux tableaux valeur_cherchée et tableau_renvoyé n’ont pas le même format ou pas la même taille, il y aura une erreur #VALEUR

Exemple d'utilisation de la fonction RechercheX

Avec la RechercheX au lieu d’entrer l'index de la colonne ou de la ligne dans laquelle vous voulez chercher votre valeur cherchée, vous devez entrer une référence vous permettant d’être indépendant du changement des autres colonnes et de pouvoir renvoyer les résultats de plusieurs lignes/ colonnes en une seule fonction !

Nous avons vu les différentes fonctions de recherche d’Excel. Vous devriez maintenant être capable de choisir la méthode la plus adaptée à vos besoins. Dans un prochain article nous verrons des astuces pour éviter les erreurs et pour utiliser ces méthodes de façon plus efficaces !