Lorsque vous utilisez Excel pour la première fois, vous rencontrez probablement quelques fonctions qui sont essentielles pour référencer et rechercher des données. Dans la plupart des cas, vous utiliserez les fonctions de recherche d'Excel.

Dans cet article, nous aborderons les différentes fonctions liées à la recherche d'informations dans Excel et expliquerons comment vous pouvez les utiliser à l'aide d'exemples. Si vous cherchez à affiner vos compétences Excel dans tous les domaines, n'hésitez pas à consulter notre parcours de compétences cursus de compétences Excel Fundamentals.

Que sont les fonctions de recherche Excel ?

Les fonctions de recherche dans Excel permettent aux utilisateurs de rechercher des données spécifiques dans un ensemble de données et de renvoyer les informations correspondantes dans une autre colonne ou ligne. Ils sont essentiels dans l'analyse des données pour référencer, recouper et extraire des informations significatives d'ensembles de données volumineux ou complexes. complexes.

Pourquoi les fonctions de recherche sont-elles importantes ? 

Les fonctions de recherche sont idéales pour une récupération efficace des données. L'extraction efficace des données est cruciale pour la productivité, en particulier lorsqu'il s'agit de traiter des feuilles de calcul volumineuses ou d'automatiser des flux de travail de reporting. Les fonctions de consultation réduisent les recherches manuelles, garantissent la cohérence et permettent des solutions de données évolutives.

Imaginez, par exemple, que vous disposiez d'un tableau de ventes comportant des milliers de tableaux et de colonnes, et que vous deviez trouver le total des ventes pour un produit ou une région spécifique. 

Sans les fonctions de consultation, vous devriez rechercher manuellement chaque ligne pour trouver les informations pertinentes, ce qui prend du temps et est sujet à l'erreur humaine.

Pour en savoir plus sur ces fonctions, consultez notre cours Préparation des données dans Excel.

Types de fonctions de recherche dans Excel

Excel propose plusieurs fonctions de recherche, chacune présentant des atouts uniques adaptés à différents cas d'utilisation. Comprendre les différences entre ces fonctions peut améliorer considérablement votre capacité à gérer et à analyser les données de manière efficace.

Nous allons examiner ci-dessous quelques types de fonctions courantes.

1. LOOKUP

Source : Microsoft

La fonction de recherche originale est la plus basique et fonctionne avec des tableaux triés. Il recherche une valeur dans une seule ligne ou colonne et renvoie une valeur à la même position dans une autre ligne ou colonne. Il ne prend en charge que les correspondances approximatives.

2. VLOOKUP (Recherche verticale)

Source : Microsoft

VLOOKUP est l'une des fonctions les plus utilisées dans les classeurs Excel existants. Il recherche une valeur dans la première colonne d'un tableau et renvoie une valeur dans la même ligne à partir d'une colonne spécifiée. 

Il prend en charge les correspondances exactes et approximatives, mais présente des limites, telles que l'impossibilité d'effectuer une recherche vers la gauche et des problèmes de performance avec les grands ensembles de données.

3. HLOOKUP (recherche horizontale)

Source : Tutoriel HLOOKUP

HLOOKUP fonctionne de la même manière que VLOOKUP, mais recherche une valeur dans la première ligne d'un tableau et renvoie une valeur d'une ligne spécifiée. Il est utile lorsque vos données sont organisées horizontalement plutôt que verticalement.

Vous devriez envisager d'utiliser HLOOKUP plutôt que VLOOKUP lorsque vos données sont organisées en lignes plutôt qu'en colonnes.

4. INDEX et MATCH

L'index et la INDEX et MATCH se caractérise par l'utilisation d'une puissante combinaison de deux fonctions :

• INDEX renvoie la valeur d'une cellule en fonction des numéros de ligne et de colonne.
• MATCH renvoie la position d'une valeur dans une ligne ou une colonne. Utilisés ensemble, ils offrent plus de souplesse que VLOOKUP et sont privilégiés dans les scénarios complexes car ils prennent en charge les recherches de gauche à droite et de droite à gauche et ne requièrent pas de données triées.

5. XLOOKUP

Introduit dans Excel 2019 et Microsoft 365, XLOOKUP est le remplaçant moderne de VLOOKUP et HLOOKUP. Elle permet des correspondances exactes ou approximatives, fonctionne horizontalement ou verticalement et gère les erreurs, ce qui en fait la fonction de recherche la plus polyvalente à ce jour.

La fonction LOOKUP

Commençons par étudier la fonction LOOKUP dans Excel, en explorant la syntaxe et les paramètres, la forme vectorielle et un exemple concret. 

Syntaxe et paramètres

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Voici quelques explications :

• lookup_value: La valeur à rechercher.
• vecteur_de_recherche : La ligne ou la colonne à rechercher.
• vecteur_résultat (facultatif) : La plage contenant la valeur à renvoyer.

En utilisant la forme vectorielle

La forme vectorielle est couramment utilisée et exige que les champs lookup_vector et result_vector soient de la même taille.

Explication avec exemples

=LOOKUP(90, A2:A10, B2:B10)

Cette fonction recherche la plus grande valeur inférieure ou égale à 90 dans A2:A10, puis renvoie la valeur correspondante dans B2:B10.

Dans ce cas, seule la ligne 5 correspond aux paramètres que nous avons définis. Par conséquent, la sortie de la fonction produirait le résultat "moyen-élevé".

Limites de la fonction LOOKUP

• Exigences relatives aux données triées: Le vecteur de recherche doit être trié par ordre croissant.
• Comportement de correspondance approximative: Ne prend pas en charge les correspondances exactes - renvoie la valeur la plus proche inférieure ou égale à la valeur de recherche.

La fonction VLOOKUP

Nex est la fonction VLOOKUP, qui est l'une des fonctions les plus utilisées dans les anciennes versions d'Excel. 

Syntaxe et paramètres

VLOOKUP(valeur_de_regard, tableau, nombre_d'indices_de_col, [intervalle_de_regard])

• lookup_value: La valeur à rechercher dans la première colonne du tableau.
• table_array : La plage de tableaux à rechercher.
• col_index_num: Le numéro de la colonne à partir de laquelle la valeur doit être renvoyée.
• range_lookup : Facultatif ; VRAI pour une correspondance approximative, FAUX pour une correspondance exacte.

Comment utiliser VLOOKUP : Guide étape par étape avec des exemples

Voyons maintenant rapidement comment fonctionne cette fonction à l'aide d'un exemple.

Nous allons utiliser la fonction suivante dans un ensemble de données simple.

=VLOOKUP("John", A2:C10, 3, FALSE)

Cette fonction recherchera toutes les entrées qui appartiennent à Jean et éditera le montant de son salaire.

Recherche "John" dans la colonne A et renvoie la valeur de la troisième colonne (colonne C) de la même ligne.

Comme prévu, le résultat de la fonction VLOOKUP est 6000, ce qui correspond à la ligne 6, qui est la valeur que nous recherchions, puisqu'elle appartient à Jean.

Correspondance exacte ou approximative

Les correspondances exactes sont le type de correspondance le plus couramment utilisé dans VLOOKUP. Cela signifie que la fonction ne renverra un résultat que si elle trouve une correspondance exacte pour la valeur de recherche dans la première colonne de la plage spécifiée.

Par exemple, si nous devions utiliser une correspondance exacte pour rechercher le salaire de Jean, la fonction VLOOKUP ne renverrait un résultat que s'il y avait une ligne avec "Jean" dans la première colonne et son salaire dans la deuxième colonne.

D'autre part, les correspondances approximatives permettent une certaine flexibilité dans la recherche des valeurs. Cette fonction est utile lorsque vous travaillez avec des données numériques ou des données susceptibles de présenter de légères variations (par exemple, en raison d'un arrondi ou d'un formatage).

En résumé :

• Exact (FAUX) : Trouve une correspondance précise.
• Approximatif (VRAI) : Requiert des données triées et trouve la correspondance la plus proche.

Erreurs courantes et dépannage

Lorsque vous utilisez VLOOKUP pour la première fois, certaines erreurs courantes peuvent survenir. 

Traitement des erreurs #N/A

L'une des erreurs les plus fréquentes lors de l'utilisation de VLOOKUP est #N/A, qui signifie "Non disponible". Cela se produit généralement lorsque la valeur recherchée ne peut être trouvée dans l'intervalle spécifié.

Cela peut également être dû à une valeur de référence manquante ou à des données non triées lors de l'utilisation d'une correspondance approximative.

Pour résoudre cette erreur, vérifiez que votre valeur de recherche est correctement orthographiée et qu'elle existe dans la première colonne de la plage dans laquelle vous effectuez votre recherche. Veillez également à ce que vos données soient triées par ordre croissant si vous utilisez une correspondance approximative.

Un autre problème potentiel est la non-concordance entre les types de données. Par exemple, si une colonne contient des nombres représentés sous forme de texte et qu'une autre colonne contient des nombres réels, VLOOKUP risque de ne pas fonctionner correctement.

Problèmes de numéro d'index de colonne

Une autre erreur qui peut se produire est l'erreur #REF ! Erreur. Si col_index_num dépasse le nombre de colonnes dans table_array, une erreur #REF ! se produit.

La fonction HLOOKUP

Pour rechercher une valeur dans la ligne supérieure d'un tableau et renvoyer une valeur d'une ligne spécifiée dans la même colonne, la fonction HLOOKUP est souvent le meilleur choix. Voyons comment cela fonctionne.

Syntaxe et paramètres

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: La valeur à rechercher dans la première ligne.
• table_array : Le tableau à rechercher.
• row_index_num: Le numéro de ligne à partir duquel une valeur doit être renvoyée.
• range_lookup : VRAI (approximatif) ou FAUX (exact).

Comment utiliser HLOOKUP : Guide étape par étape avec des exemples

=HLOOKUP("Q1", A1:D3, 2, FALSE)

La fonction HLOOKUP ci-dessus recherche "Q1" dans la ligne 1 et renvoie la valeur de la ligne 2 dans la même colonne. 

Exécutons la fonction et voyons les résultats.

Comme vous pouvez le voir ci-dessus, la fonction devrait renvoyer 1200. Étant donné que la fonction recherche la valeur trouvée à la ligne 2 sur la base de la valeur spécifiée à la ligne 1, 1200 est la sortie correcte que nous recherchons.

Les fonctions INDEX et MATCH

Voyons ensuite comment les fonctions INDEX et MATCH d'Excel se combinent pour rechercher des valeurs. 

Syntaxe et exemples d'utilisation

=INDEX(array, row_num, [column_num])

La fonction INDEX renvoie la valeur d'une ligne et d'une colonne spécifiques dans un intervalle donné.

=MATCH(lookup_value, lookup_array, [match_type])

La fonction MATCH renvoie la position relative de lookup_value dans lookup_array.

Utilisation conjointe d'INDEX et de MATCH

Lorsque les fonctions INDEX et MATCH sont utilisées conjointement, elles constituent un outil puissant pour rechercher des valeurs dans un tableau ou une plage. Cette combinaison vous permet de spécifier l'emplacement exact de la valeur que vous souhaitez renvoyer, plutôt que de devoir saisir manuellement les numéros de ligne et de colonne.

Prenons un exemple :

=INDEX(B2:B10, MATCH("John", A2:A10, 0))

Cette combinaison recherche la ligne où "John" apparaît dans A2:A10, puis renvoie la valeur correspondante dans B2:B10.

Voyons ce que la fonction produira.

Lorsque les deux fonctions sont utilisées ensemble, elles permettent d'obtenir le même résultat qu'avec la fonction VLOOKUP. Cependant, l'utilisation d'INDEX et de MATCH permet une plus grande flexibilité dans le choix de la colonne dans laquelle effectuer la recherche, ainsi que la possibilité d'effectuer une recherche dans plusieurs colonnes.

La fonction XLOOKUP (Excel 2019 et versions ultérieures)

Enfin, examinons l'une des fonctions de recherche Excel les plus récentes, XLOOKUP.

Syntaxe et paramètres

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Exemples pratiques

Nous appliquerons la fonction à un jeu de données simple et à un exemple.

=XLOOKUP("John", A2:A10, B2:B10, "Not Found")

La fonction ci-dessus recherche "John" dans A2:A10 et renvoie la valeur de B2:B10 ; elle renvoie "Non trouvé" en cas d'absence.

En examinant la sortie de la formule donnée ci-dessus, nous pouvons voir que la sortie "Not Found" a été affichée. Cela signifie qu'il n'y a pas d'entrée avec le nom de l'employé "John".

Comparaison : LOOKUP vs. VLOOKUP vs. XLOOKUP

Rassemblons toutes ces fonctions pour voir comment elles s'accordent et quelles sont leurs différences.

Conclusion

Les fonctions de recherche sont des outils essentiels dans Excel pour une récupération efficace des données. Si LOOKUP, VLOOKUP et HLOOKUP ont leur utilité, INDEX/MATCH et XLOOKUP offrent plus de souplesse et de puissance. Comprendre quand et comment utiliser chacun d'entre eux peut considérablement améliorer l'efficacité de votre feuille de calcul et vos capacités d'analyse.

Si vous cherchez d'autres ressources sur Excel, notre cours sur la Cours sur la préparation des données dans Excel est un excellent point de départ. Pour maîtriser les bases d'Excel, notre cursus de compétences Excel Fundamentals est le meilleur point de départ. Vous pouvez également consulter nos tutoriels Excel sur les sujets suivants Formules Excel de base ou Vlookup à partir d'autres feuilles dans Excel également.