Comment RECHERCHEV Excel peut-elle renvoyer plusieurs valeurs ?

RECHERCHEV Excel et la recherche de plusieurs valeurs : Le défi

La fonction RECHERCHEV (VLOOKUP en anglais) est un pilier d'Excel pour la recherche de données. Sa syntaxe est simple : =RECHERCHEV(valeur_recherchée, table_matrice, no_index_col, [valeur_proche]). Elle permet de trouver une valeur dans une colonne (la première colonne de la table_matrice) et de renvoyer une valeur correspondante d'une autre colonne.

Le problème de la première correspondance

Par défaut, RECHERCHEV s'arrête à la première correspondance qu'elle trouve. Si votre tableau contient plusieurs lignes avec la même valeur_recherchée, seule la première sera prise en compte. C'est là que réside la limitation et que des techniques alternatives deviennent nécessaires pour extraire toutes les valeurs correspondantes.

Méthodes alternatives pour obtenir plusieurs résultats avec RECHERCHEV Excel

Plusieurs approches permettent de contourner la limitation de RECHERCHEV et d'obtenir plusieurs résultats. Nous allons explorer les plus courantes et efficaces :

1. Utilisation de la fonction INDEX et de la fonction PETITE.VALEUR

Cette méthode est une des plus populaires et flexibles. Elle combine la puissance de INDEX et PETITE.VALEUR pour identifier et extraire toutes les correspondances.

Explication de la méthode

L'idée est de créer une colonne auxiliaire qui identifie les lignes où la valeur_recherchée est présente. Ensuite, on utilise PETITE.VALEUR pour obtenir les numéros de ligne de ces correspondances, et enfin INDEX pour extraire les valeurs correspondantes dans la colonne souhaitée.

Étapes détaillées avec un exemple

Supposons que vous ayez un tableau avec des noms de produits dans la colonne A et leurs prix dans la colonne B. Vous voulez trouver tous les prix pour un produit spécifique.

1. Création de la colonne auxiliaire (Colonne C) : Dans la cellule C2, entrez la formule : =SI(A2="VotreProduit",LIGNE(),""). Remplacez "VotreProduit" par le nom du produit que vous recherchez. Copiez cette formule vers le bas pour toutes les lignes de votre tableau. Cette colonne affichera le numéro de ligne si le produit correspond, sinon elle sera vide.

Exemple : Si vous recherchez le produit "Pomme", la colonne C affichera le numéro de ligne pour chaque ligne où "Pomme" apparaît dans la colonne A.

1. Extraction des numéros de ligne correspondants : Dans une autre colonne (par exemple, la colonne E), utilisez la fonction PETITE.VALEUR pour obtenir les numéros de ligne des correspondances. Dans la cellule E2, entrez la formule : =SIERREUR(PETITE.VALEUR($C:$C,LIGNE()-1),""). Copiez cette formule vers le bas. Cette formule renverra le plus petit numéro de ligne non vide de la colonne C (c'est-à-dire la première occurrence de "VotreProduit"), puis le deuxième plus petit, et ainsi de suite. SIERREUR est utilisé pour gérer les erreurs lorsque toutes les correspondances ont été trouvées.

2. Extraction des valeurs correspondantes (Prix) : Enfin, utilisez la fonction INDEX pour extraire les prix correspondants aux numéros de ligne obtenus à l'étape précédente. Dans la cellule F2, entrez la formule : =SIERREUR(INDEX($B:$B,E2),""). Copiez cette formule vers le bas. Cette formule utilisera le numéro de ligne trouvé dans la colonne E pour extraire le prix correspondant de la colonne B.

Exemple : Si E2 contient le numéro de ligne 5, la formule INDEX extraira la valeur de la cellule B5 (le prix du produit trouvé à la ligne 5).

Avantages et inconvénients

• Avantages : Très flexible, fonctionne avec des données non triées, ne nécessite pas de modifications importantes de votre tableau.
• Inconvénients : Nécessite une colonne auxiliaire, les formules peuvent être un peu complexes à comprendre au début.

2. Utilisation de la fonction FILTRE (Excel 365 et versions ultérieures)

La fonction FILTRE est une fonction plus récente disponible dans Excel 365 et les versions ultérieures. Elle est conçue spécifiquement pour filtrer des données et renvoyer un tableau de résultats, ce qui la rend idéale pour obtenir plusieurs correspondances.

Explication de la méthode

La fonction FILTRE prend un tableau de données et une condition de filtrage. Elle renvoie un nouveau tableau contenant uniquement les lignes qui satisfont la condition.

Étapes détaillées avec un exemple

Reprenons notre exemple avec les noms de produits (colonne A) et les prix (colonne B).

1. Utilisation de la fonction FILTRE : Dans une cellule vide, entrez la formule : =FILTRE(B:B,A:A="VotreProduit",""). Remplacez "VotreProduit" par le nom du produit que vous recherchez. Cette formule filtrera la colonne B (prix) en fonction de la condition que la colonne A (nom du produit) soit égale à "VotreProduit". Le troisième argument, "", est utilisé pour renvoyer une cellule vide si aucune correspondance n'est trouvée.

Exemple : Si vous recherchez le produit "Pomme", la fonction FILTRE renverra une liste de tous les prix associés au produit "Pomme".

Avantages et inconvénients

• Avantages : Très simple à utiliser, ne nécessite pas de colonne auxiliaire, renvoie un tableau de résultats directement.
• Inconvénients : Disponible uniquement dans Excel 365 et versions ultérieures, moins flexible que la méthode INDEX et PETITE.VALEUR pour des scénarios de recherche plus complexes.

3. Utilisation de Power Query (Get & Transform Data)

Power Query est un outil puissant intégré à Excel qui permet de transformer et de charger des données à partir de différentes sources. Il peut également être utilisé pour obtenir plusieurs résultats avec RECHERCHEV.

Explication de la méthode

Avec Power Query, vous pouvez charger votre tableau de données, filtrer les lignes correspondantes à votre valeur_recherchée, et charger les résultats dans une nouvelle feuille de calcul.

Étapes détaillées avec un exemple

1. Chargement des données dans Power Query : Sélectionnez votre tableau de données. Allez dans l'onglet Données et cliquez sur À partir d'un tableau/d'une plage. Cela ouvrira l'éditeur Power Query.

2. Filtrage des données : Dans l'éditeur Power Query, sélectionnez la colonne contenant la valeur_recherchée (par exemple, la colonne A). Cliquez sur la flèche vers le bas de l'en-tête de la colonne et choisissez Filtres de texte -> Égal à. Entrez votre valeur_recherchée (par exemple, "Pomme") et cliquez sur OK.

3. Chargement des résultats : Cliquez sur Fermer et charger -> Fermer et charger dans.... Choisissez où vous voulez charger les résultats (par exemple, une nouvelle feuille de calcul) et cliquez sur OK. Power Query créera une nouvelle feuille avec uniquement les lignes correspondant à votre critère de recherche.

Avantages et inconvénients

• Avantages : Très puissant pour la transformation de données, peut gérer des ensembles de données volumineux, automatise le processus de recherche.
• Inconvénients : Nécessite une certaine familiarité avec Power Query, peut être plus complexe à utiliser que les méthodes basées sur les formules.

Conseils pratiques et bonnes pratiques pour la recherche de plusieurs valeurs avec RECHERCHEV Excel

• Choisissez la méthode adaptée à votre situation : Si vous utilisez Excel 365 ou une version ultérieure et que vous avez besoin d'une solution simple, FILTRE est un excellent choix. Si vous avez besoin de plus de flexibilité et que vous êtes à l'aise avec les formules, INDEX et PETITE.VALEUR sont une bonne option. Si vous travaillez avec des ensembles de données volumineux et que vous avez besoin d'automatiser le processus, Power Query est la meilleure solution.
• Utilisez des noms de plages : Au lieu d'utiliser des références de cellules directement dans vos formules, utilisez des noms de plages. Cela rendra vos formules plus lisibles et plus faciles à maintenir. Par exemple, au lieu d'utiliser $A:$A, vous pouvez définir un nom de plage appelé Produits qui fait référence à la colonne A.
• Gérez les erreurs : Utilisez la fonction SIERREUR pour gérer les erreurs potentielles dans vos formules. Cela rendra votre feuille de calcul plus robuste et plus facile à utiliser.
• Commentez vos formules : Ajoutez des commentaires à vos formules pour expliquer ce qu'elles font. Cela facilitera la compréhension et la maintenance de vos feuilles de calcul, surtout si vous travaillez en équipe.
• Testez vos formules : Vérifiez toujours que vos formules fonctionnent correctement avant de les utiliser dans un environnement de production. Utilisez des données de test pour vous assurer que vous obtenez les résultats attendus.

Erreurs courantes à éviter lors de la recherche de plusieurs valeurs avec RECHERCHEV Excel

• Oublier de verrouiller les références de cellules : Lorsque vous copiez des formules, assurez-vous de verrouiller les références de cellules appropriées en utilisant le symbole $. Cela garantit que les références restent correctes lorsque vous copiez la formule.
• Utiliser des données non triées avec RECHERCHEV et VALEUR_PROCHE : Si vous utilisez l'argument VALEUR_PROCHE de RECHERCHEV, assurez-vous que vos données sont triées par ordre croissant. Sinon, vous risquez d'obtenir des résultats incorrects.
• Ne pas gérer les erreurs : Comme mentionné précédemment, il est important de gérer les erreurs potentielles dans vos formules. Utilisez la fonction SIERREUR pour afficher un message d'erreur clair ou pour renvoyer une valeur par défaut si une erreur se produit.
• Complexifier inutilement les formules : Essayez de garder vos formules aussi simples que possible. Des formules complexes sont plus difficiles à comprendre et à maintenir. Si vous avez besoin d'effectuer des calculs complexes, envisagez d'utiliser des colonnes auxiliaires pour décomposer le problème en étapes plus simples.

Conclusion

Bien que la fonction RECHERCHEV d'Excel soit limitée à la recherche de la première correspondance, il existe plusieurs méthodes alternatives pour contourner cette limitation et obtenir plusieurs résultats. En utilisant INDEX et PETITE.VALEUR, FILTRE (pour les versions récentes d'Excel) ou Power Query, vous pouvez extraire toutes les valeurs correspondantes à votre critère de recherche. Choisissez la méthode qui convient le mieux à votre situation et suivez les conseils pratiques et les bonnes pratiques présentés dans cet article pour une recherche efficace et précise dans Excel.