Excel: Recherchev (vlookup) avec résultats multiples

Publié le 26 octobre 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Recherchev multiple

Excel: Recherchev (vlookup) avec résultats multiples

Un lecteur a posé une question sur notre forum, à savoir comment dresser une liste de tous les résultats possibles d’un recherchev (vlookup), sans utiliser de tableau croisé dynamique. Nous pouvons résoudre cette problématique de différentes façons. Cet article de blogue vise à en présenter une, que nous jugeons efficace. Si vous avez d’autres solutions à proposer, nous vous invitons à utiliser l’espace commentaires, à la suite de cet article.

 

Mettre les données sous forme de tableau

D’abord, nous recommandons de mettre les données de départ sous forme de tableau. Pour savoir comment faire et pour comprendre tous les avantages de la mise sous forme de tableau, vous pouvez relire l’article Découvrez la magie des tableaux Excel. Dans notre exemple ci-bas, nous avons donc mis nos produits et nos ventes sous forme de tableau. Il sera facile de mettre à jour ce tableau, qui pourra comprendre plus ou moins de lignes au fil du temps et qui s’ajustera automatiquement.

Recherchev multiple

 

Ajouter une colonne calculée avec la fonction Si (ou If)

Ensuite, nous recommandons d’ajouter une colonne à votre tableau dans le but d’identifier clairement les lignes du tableau qui doivent être retenues. Dans notre exemple ci-bas, l’usager peut faire un choix dans la cellule H5. Le choix actuel est le produit A et c’est pourquoi la fonction Si (ou If) retourne un 1 pour tous les produits A, dans notre tableau.

Recherchev multiple

 

Ajouter une colonne calculée avec les fonctions Si (ou If) et Somme (ou Sum)

Nous proposons d’ajouter ensuite une seconde colonne calculée pour indiquer la séquence de l’apparition des produits A dans notre tableau. Dans notre exemple ci-bas, nous avons 3 produits A et chacun est numéroté de 1 à 3 selon son rang d’apparition dans le tableau. À noter que, puisque les données ont été mises sous forme de tableau, ces 2 colonnes calculées s’ajusteront si le tableau évolue au fil du temps.

Recherchev multiple

 

Calculer le nombre d’occurences

Dans la cellule H6, ci-bas, nous avons utilisé la fonction Somme (ou Sum) sur la colonne Occurence pour indiquer combien de fois le produit A apparaît dans notre tableau. C’est aussi le nombre de lignes que devra comprendre notre table de résultats.Recherchev multiple

 

Construire la table de résultats

Nous suggérons de créer une table de résultats avec 3 colonnes. La première étant simplement un index pour indiquer le rang du résultat. Pour cela, nous utilisons la fonction Ligne (ou Row), imbriquée dans une fonction Si (ou If), tel qu’illustré ci-dessous. Ainsi, la fonction retourne le rang du résultat dans le tableau, de 1 à 3 dans notre exemple, et retourne du vide pour les autres lignes. Ce faisant, nous serons assurés qu’un produit qui est présent plus de 3 fois dans la table, verra sa table de résultats s’allonger.

Recherchev multiple

 

Ensuite, nous ajoutons une colonne, à la l’aide de la fonction Si (ou If) pour indiquer le nom du produit.Recherchev multiple

 

Et finalement, nous utilisons une fonction Index/Equiv (ou Index/Match) pour retourner les ventes correspondantes de chaque produit.

Recherchev multiple

 

Résultat final

Lorsque l’usager choisit un autre produit, par exemple, le produit C, les résultats s’ajustent automatiquement.

Recherchev multiple

 

Vous avez des questions Excel vous aussi?

Utilisez notre forum pour poser vos questions ou encore, utilisez les zones de commentaires dans le bas de chacun de nos articles.

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

12 réflexions sur “Excel: Recherchev (vlookup) avec résultats multiples”

  1. Bonjour,
    Il existe une possibilité avec 2 formules, que j’avais glané sur un forum,(je ne me souviens plus de l’auteur) et que j’utilise régulièrement pour des extractions sans TCD. A juger si pratique ou non….

    En D6 : =SI(ESTERREUR(CHERCHE($H$5;[@Produit]));””;LIGNE())
    En K6 : =SIERREUR(INDEX(C:C;PETITE.VALEUR(D:D;LIGNE()-LIGNE($D$5)));””)

    Pour l’index
    En I6 : =SI(NBCAR(k6)>1;I6;””)
    En I7 : =SI(NBCAR(k7)>1;I6+1;””) puis étendre la formule

    Très cordialement
    Stéphane

  2. Bonjour,
    Une autre façon avec une formule plus complexe mais moins d’opérations serait (Désolé je n’ai pas les formules en Français mais Sophie pourra traduire):
    Vous créer une liste de chiffre dans colonne i (1 à 16 disons dans cet exemple) et vous appliquez la formule suivante dans la colonne J.
    ={IF(SMALL(IF(Table1[Produit]=$H$1,Table1[Ventes],MAX(Table1[Ventes])+1),$I2)=MAX(Table1[Ventes])+1,””,SMALL(IF(Table1[Produit]=$H$1,Table1[Ventes],MAX(Table1[Ventes])+1),$I2))}
    Ne pas oublier Ctrl-Shift-Enter car c’est une formule “Array” 🙂

    La seule variante, la liste sort en ordre de chiffre de vente et non en ordre selon la liste.

    Bonne journée,
    Hugo

  3. Très intéressant comme technique. J’aime beaucoup ce genre de technique.

    Mais pour obtenir pratiquement le même résultat, il y a aussi le filtre avancé sous l’onglet “Données”. À la différence que la mise à jour du tableau ne se fait pas automatiquement. Toutefois, le filtre peut avoir des “wildcard”, ce qui peut être très pratique aussi.

    Hugues

  4. Bonjour,
    Le problème avec cette solution est qu’il y a une rétroaction entre la base de donnée et la recherche sur celle-ci qu’il me semble souhaitable d’éviter.
    Pour cela, je propose de supprimer la colonne “Occurence” et dans la colonne “Séquence” de remplacer la formule par :
    =[Produit] &”-“& NB.SI($B$2:B2;”=”&[Produit])
    Ainsi, chaque cellule contient une valeur de type ‘NomProduit-NumSequence’ unique.
    En H6 mettre la formule
    =NB.SI([Produit];G6)
    Dans la colonne I, pour retrouver dans chaque cellule ‘NomProduit-NumSequence’, étendre la formule suivante à partir de I6 :
    =SI(LIGNE()-5<$H$6;$G$6&"-"&LIGNE()-5;"")
    En espérant que cela sera utile.
    A bientôt
    Guy

  5. Bonjour,
    Merci pour ce tutoriel j’ai réussi à tout faire sauf la dernière étape avec index équiv.
    En fait au lieu de mes produits il s’agit de codes postaux et je veux que les ventes (pour moi le nom des clients) s’affiche mais il y a zéro à la place…. une idée ?
    dans l’attente de votre retour,
    Bien cordialement

  6. Dominic Boudreau

    Bonjour et merci mais j’aimerais savoir,
    Est-ce possible de faire ce genre de formule dans une liste déroulante pour obtenir ce résultat comme dans des tableaux croisés dynamiques parce moi je fais un formulaire userform pour créer des plan d’entraînement
    Dans un cbobox choisir un sport dans un autre le niveau et un autre le type d’entraînement qui serait afficher selon le niveau que je choisis et ensuite une autre qui condiendrait les entraînements selon le type choisis. Est-ce possible?

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top