RECHERCHEV plusieurs valeurs

Article mis à jour le 25/07/2023

Résumé

Pour réaliser une fonction RECHERCHEV plusieurs valeurs, il existe plusieurs solutions techniques. Elles dépendront en premier lieu de votre version d’Excel. Chacune des formules présentées permet de récupérer tous les résultats de la RECHERCHEV.

Si vous avez la version Office 365 ou supérieure, tournez-vous vers la fonction FILTRE. Elle vous permettra de récupérer plusieurs valeurs en fonction d’une autre valeur connue.

= FILTRE( F6:F10 ; D6:D10=J5 ; "" )

--> Résultat : {128;113;109}

Il est possible également possible d’afficher tous les résultats dans une seule cellule :

= JOINDRE.TEXTE( ", " ; VRAI ; FILTRE( F6:F10 ; D6:D10=D17 ; "" ) )

--> Résultat : "128, 113, 109"

Si vous avez antérieure à la version Office 365, plusieurs formules s’offrent à vous. La plus simple suppose la création d’une colonne à gauche de votre tableau. Cette solution n’est pas présentée dans le présent article puisque nous nous concentrerons sur une solution ne nécessitant pas de modifier le tableau d’origine. La solution présentée a aussi l’avantage de pouvoir générer une RECHERCHEV avec plusieurs valeurs et plusieurs critères.

Si vous êtes favorable à la création d’une colonne (et que vous ne souhaitez appliquer qu’un seule critère), je vous invite à vous rendre sur l’article d’excel-en-ligne.fr.

Les versions d’Excel antérieures à Office 365 ne permettent pas de générer un résultat dynamique (tableau). Pour contourner le problème, nous pouvons créer un tableau avec le numéro de l’occurrence dans la première colonne et la valeur associée (résultat de la RECHERCHEV). Il faudra utiliser une formule de la forme suivante :

= INDIRECT(
   ADRESSE(
    PETITE.VALEUR(
     SI(
      ($D$6:$D$10=$K$11);
      LIGNE($F$6:$F$10)
      ;"");
     $J12);
    COLONNE($F$5)))

--> Résultat : 128

Cette formule (ainsi que la suivante) doit être validée par Ctrl + MAJ + Entrée puisqu’il s’agit d’une formule matricielle.

Il est possible également possible d’afficher tous les résultats dans une seule cellule :

= JOINDRE.TEXTE(
   ", ";
   VRAI;
   INDEX( 
    F:F;
    PETITE.VALEUR(
     SI(
      ($D$6:$D$10=$D$20);
      LIGNE($F$6:$F$10);
      "");
     LIGNE(
      INDIRECT(
       ADRESSE(
        NB.SI(D6:D10;D20);1) & ":" & ADRESSE(1;1) )))))	

--> Résultat : "128, 113, 109"

Formule Excel – RECHERCHEV plusieurs valeurs

À partir d’Office 365

Affichage en colonne
= FILTRE( plage_résultats ; plage_critère=critère ; "" )

Affichage dans une seule cellule
= JOINDRE.TEXTE( ", " ; VRAI ; 
    FILTRE( plage_résultats ; plage_critère=critère ; "" ) )

Avant Office 365

Affichage en colonne
= INDIRECT(
   ADRESSE(
    PETITE.VALEUR(
     SI(
      (plage_critère=critère);
      LIGNE(plage_résultats);
      "");
     num_occurence);
    COLONNE(entête_résultats)))

Affichage dans une seule cellule
= JOINDRE.TEXTE(
   ", " ;
   VRAI ;
   INDEX(
    col_résultats ; 
    PETITE.VALEUR(
     SI(
      (plage_critère=critère) ;
      LIGNE(plage_résultats);
      "");
    LIGNE(
     INDIRECT(
      ADRESSE(
       NB.SI(plage_critère;critère);1)&":"&ADRESSE(1;1))))))

Explications

Dans notre exemple, on veut retrouver toutes les valeurs des stocks pour l’article « chaise ». Habituellement, pour récupérer une valeur à partir d’une autre valeur connue, on pourrait utiliser la fonction RECHERCHEV. Cependant, cette fonction ne permet de récupérer qu’une seule valeur.

Pour faire une RECHERCHEV avec plusieurs valeurs, il faudra se tourner vers la fonction FILTRE si l’on possède Office 365. Si ça n’est pas le cas, il faudra se tourner vers une solution mettant en œuvre différentes fonctions (voir ci-après).

Fonction FILTRE récupérer plusieurs résultats à une RECHERCHEV

Si vous avez Office 365, utilisez la fonction FILTRE pour obtenir l’équivalent d’une RECHERCHEV avec plusieurs résultats. Elle permet de filtrer une plage de données sur la base d’un critère que vous définissez.

= FILTRE( col_résultats ; plage_critère=critère ; résultat_si_vide )

= FILTRE( F6:F10 ; D6:D10=J5 ; "" )

--> Résultat : {128;113;109}

La formule ci-dessus indique que l’on veut récupérer les valeurs dans F6:F10 si D6:D10 sont égales à « chaise ». La fonction FILTRE va alors produire un tableau de 3 lignes avec les valeurs des stocks.

Il est possible de regrouper tous les résultats dans une même cellule en utilisant la fonction JOINDRE.TEXTE. Cette fonction permet de fusionner plusieurs valeurs en une seule en précisant le délimiteur.

= JOINDRE.TEXTE( délimiteur ; ignorer_vide ; valeurs )

= JOINDRE.TEXTE( ", " ; VRAI ; {128;113;109} )

--> Résultat : "128, 113, 109"

Formule pour récupérer plusieurs résultats (si version antérieure à Office 365)

Si vous avez une version d’Excel antérieure à Office 365, vous ne pouvez pas générer un tableau avec plusieurs valeurs comme peuvent le faire les nouvelles fonctions dynamiques. Cependant, la formule présentée ci-après pourra vous permettre de créer un tableau qui récupérera automatiquement les résultats.

La plupart des solutions que vous trouverez pour récupérer plusieurs résultats d’une RECHERCHEV supposent la création d’une colonne d’aide. Elles sont intéressantes car plus simples mais je n’apprécie pas de devoir créer une nouvelle colonne et altérer le visuel de mes données initiales. De fait, la formule suivante permet de générer un tableau sans devoir créer de nouvelles colonnes dans les données initiales.

= INDIRECT(
   ADRESSE(
    PETITE.VALEUR(
     SI(
      ($D$6:$D$10=$K$11);
      LIGNE($F$6:$F$10)
      ;"");
     $J12);
    COLONNE($F$5)))

Nous allons voir pas à pas comment cette formule fonctionne. Commençons par l’élément central :

= SI(
   ($D$6:$D$10=$K$11);
   LIGNE($F$6:$F$10)
   ;"")

La fonction SI permet d’effectuer un test logique. S’il se révèle VRAI, la fonction renvoie la valeur indiquée en tant que second argument sinon elle renvoie la valeur indiquée dans le troisième argument.

Il est possible d’effectuer une formule matricielle. C’est-à-dire que l’on va effectuer l’opération sur un ensemble de cellules. Le résultat ne sera pas une seule valeur mais une matrice de même dimension (plusieurs valeurs). Malheureusement, sur les versions Excel antérieures à Office 365 on ne peut pas visualiser cette matrice (voir capture ci-dessous effectuée sur Excel 365).

Dans notre cas, si les cellules en D6:D10 sont égales à « chaise » alors on récupère le numéro de la ligne. Dans le cas contraire, nous affichons une valeur vide.

= SI(
   ($D$6:$D$10=$K$11);
   LIGNE($F$6:$F$10)
   ;"")

--> Résultat : {6;"";8;9;""}

Si vous souhaitez appliquer plusieurs critères, c’est parfaitement possible en indiquant plusieurs tests logiques séparés par des astérisques :

= SI(
   (test_logique_1)*(test_logique_2) ;
   LIGNE(plage_résultats)
   ;"")

La fonction PETITE.VALEUR permet de récupérer la énième plus petite valeur dans une liste. Associé à une colonne sur laquelle on inscrit le numéro de chaque occurrence (manuelle ou à l’aide d’une formule comme dans notre cas), on extrait à chaque ligne une valeur.

= PETITE.VALEUR( {6;"";8;9;""} ; $J12 )

--> Résultat : 9
    J12 contient le numéro de la 3e valeur à récupérer
    en étirant la formule vers le bas, on récupère également
    la 2nd et 1ère valeur la plus petite (9, 8 et 6).

La fonction ADRESSE permet de générer les coordonnées d’une cellule en fonction du numéro de la ligne et du numéro de la colonne. Les étapes précédentes nous ont permis de récupérer chaque numéro de ligne. Pour récupérer le numéro de la colonne des résultats, nous utilisons la fonction COLONNE avec comme argument l’entête de la colonne des résultats.

= ADRESSE( 6 ; COLONNE($F$5) )

--> Résultat : "$F$6"

Enfin, la fonction INDIRECT permet de convertir des coordonnées au format texte (généré notamment par la fonction ADRESSE) en coordonnées valides.

= INDIRECT( "$F$6" )

--> Résultat : 109

Il ne reste plus qu’à étirer la formule vers le bas pour extraire chaque occurrence.

Formule pour récupérer plusieurs résultats et les mettre dans une seule cellule (si version antérieure à Office 365)

La formule suivante partage une part des calculs avec la formule précédente.

= JOINDRE.TEXTE(
   ", ";
   VRAI;
   INDEX( 
    F:F;
    PETITE.VALEUR(
----- Idem formule précédente -----
     SI(
      ($D$6:$D$10=$D$20);
      LIGNE($F$6:$F$10);
      "");
-----------------------------------
     LIGNE(
      INDIRECT(
       ADRESSE(NB.SI(D6:D10;D20);1) 
       & ":" &
       ADRESSE(1;1) )))))	


= JOINDRE.TEXTE(
   ", ";
   VRAI;
   INDEX( 
    F:F;
    PETITE.VALEUR(
------------ Résultat ------------
     {6;"";8;9;""}
----------------------------------
     LIGNE(
      INDIRECT(
       ADRESSE(NB.SI(D6:D10;D20);1) 
       & ":" &
       ADRESSE(1;1) )))))

On va utiliser à nouveau la fonction PETITE.VALEUR pour extraire les numéros des lignes correspondant aux valeurs cherchées.

Le second argument de cette fonction est le numéro de l’occurrence souhaitée. Dans notre cas, nous voulons une matrice comprenant les valeurs de 1 à x (x étant le nombre d’occurrences – dans notre cas 3 occurrences). Pour cela, nous allons utiliser une formule associant la fonction LIGNE, la fonction INDIRECT, la fonction NB.SI et la fonction ADRESSE.

L’idée est de générer des coordonnées de cellules de type A1:Ax (x étant le nombre d’occurrences) et d’extraire les numéros de lignes sous format matrice : {1;2;...;x}.

= LIGNE(
   INDIRECT(
    ADRESSE(NB.SI(D6:D10;D20);1) & ":" & ADRESSE(1;1) ))

= LIGNE(
   INDIRECT(
    ADRESSE( 3 ; 1 ) & ":" & ADRESSE( 1 ; 1 ) ))

= LIGNE(
   INDIRECT(
    "$A$3" & ":" & "$A$1" ))	

= LIGNE(
   INDIRECT( "$A$3" & ":" & "$A$1" ) )

= LIGNE(
   INDIRECT( "$A$3:$A$1" ) )

= LIGNE( $A$3:$A$1 )

--> Résultat : {1;2;3}

Une fois ce résultat obtenu et intégré dans la fonction PETITE.VALEUR, nous obtenons :

= PETITE.VALEUR( {6;"";8;9;""} ; {1;2;3} )

--> Résultat : {6;8;9}

Il nous reste à convertir les numéros des lignes en valeurs (6 => 128). Pour cela, il faudra utiliser la fonction INDEX. Cette fonction permet de récupérer une valeur en fonction de ses coordonnées. Nous allons demander à Excel de récupérer les valeurs dans la colonne F aux lignes trouvées précédemment.

= INDEX( F:F ; {6;7;8} )

--> Résultat : {128;113;109}

Enfin, il ne nous reste plus qu’à utiliser la fonction JOINDRE.TEXTE pour fusionner les valeurs trouvées dans une valeur unique.

= JOINDRE.TEXTE( ", "; VRAI ; {128;113;109} )

--> Résultat : "128, 113, 109"

Vous avez à présent plusieurs solutions pour effectuer une RECHERCHEV avec plusieurs valeurs (résultats). N’oubliez pas de valider la formule par Ctrl + MAJ + Entrée si vous travaillez sur une version d’Excel antérieure à Office 365.

Formules en lien

4.7/5 - (50 votes)

Vous avez apprécié cet article ?

Pour me soutenir, cliquez sur cette publicité. Pas besoin d’acheter, un clic suffit.

Merci. 🤩

Annonces

Yann

Créateur de Cleex, ancien contrôleur de gestion dans l’industrie, j’ai à coeur de vous proposer des ressources de qualité pour vous aider à travailler avec Excel.