Si vous êtes ici, cela signifie que vous essayez d’utiliser VLOOKUP pour récupérer plusieurs valeurs d’un ensemble de données. Mais soyons clairs, RECHERCHEV NE PEUT RETOURNER PLUSIEURS VALEURS. Mais cela ne veut pas dire que nous ne pouvons pas le faire. Nous pouvons faire une recherche qui récupère plusieurs valeurs au lieu de la première uniquement.

286

Formule générique

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

Array: la plage à partir de laquelle vous souhaitez récupérer les données.

lookup_value: Votre lookup_value que vous souhaitez filtrer.

lookup_value_range: La plage dans laquelle vous souhaitez filtrer lookup_value.

La première cellule de la plage lookup_value: si votre plage lookup_value est $ A $ 5: $ A $ 100 alors son $ A $ 5.

Important: tout doit être lien: / excel-range-name-valeur-absolue-référence-dans-excel [référence absolue]. lookup_value peut être relatif selon les besoins.

Entrez-le sous forme de formule matricielle. Après avoir écrit la formule, appuyez sur la touche CTRL + MAJ + ENTRÉE pour en faire une formule matricielle.

Exemple de recherche de résultats multiples J’ai ces données d’élève dans la plage A2: E14. Dans la cellule G1, j’ai une liste déroulante des valeurs de région, par exemple Centre, Est, Nord, Sud et Ouest.

Maintenant, je veux, quelle que soit la région que j’ai dans G1, une liste de tous les étudiants de cette région devrait être affichée dans la colonne H.

287

Pour rechercher plusieurs valeurs dans Excel, identifions nos variables.

Tableau: $ C $ 2: $ C $ 14 valeur_recherche: $ G $ 1 plage_valeur_recherche: $ A $ 2: $ A $ 14 La première cellule de la plage lookup_value: $ A $ 2 Selon les données ci-dessus, notre formule pour récupérer plusieurs valeurs dans Excel sera:

{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}

Copiez cette formule dans H2 et appuyez sur CTRL + MAJ + ENTRÉE. Faites maintenant glisser cette formule jusqu’à ce que vous obteniez une erreur #NUM. #NUM indique qu’il n’y a plus d’autres valeurs correspondantes pour cette valeur_recherche, et c’est la fin de la liste.

288

Si #NOMBRE vous ennuie, vous pouvez avoir le lien: / formules-logiques-excel-iferror-function [fonction IFERROR] `devant la formule et afficher des informations significatives au lieu de l’erreur.

{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}

Cela affichera –List Ends– lorsque tous les éléments sont affichés.

Voyons maintenant comment ça marche.

Même si la formule peut sembler complexe, mais l’idée est simple. Nous devons obtenir le numéro d’index de chaque occurrence de valeur, puis récupérer les valeurs en utilisant le`link: / lookup-formulas-excel-index-function [fonction INDEX] `d’Excel.

Par conséquent, le défi principal est d’obtenir un tableau de numéros d’index de lookup_value. Pour obtenir les numéros d’index, nous avons utilisé link: / tips-if-condition-in-excel [IF] ʻand`link: / lookup-and-reference-excel-row-function [ROW]

les fonctions. La formule est en effet tout à fait complexe, décomposons-la.

Nous voulons obtenir des valeurs de la colonne student, donc notre tableau pour`link: / lookup-formulas-excel-index-function [INDEX function] `est $ C $ 2: $ C $ 14. Nous devons maintenant donner les numéros de ligne de $ A $ 2: $ A $ 14 (valeur de recherche) dans lesquels la valeur de G1 existe (pour l’instant, disons que G1 a le centre).

IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Maintenant, cette partie renvoie le numéro de ligne si la valeur d’un compte de cellule de G1 (central) dans la plage $ A $ 2: $ A $ 14 else renvoie FALSE. Dans cet exemple, il renvoie

\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.

  • Maintenant, puisque le tableau ci-dessus contient le numéro de ligne de la 1ère ligne (1: 1) et que nous avons besoin d’une ligne à partir de notre tableau (A2: A14). Pour ce faire, nous utilisons -ROW ($ A $ 2) +1 dans la formule IF. Cela renverra un certain nombre de lignes avant de démarrer notre tableau.

Pour cet exemple, il vaut -1. S’il partait de A3, il renverrait -2 et ainsi de suite. nombre sera soustrait de chaque nombre dans le tableau renvoyé par IF. Donc finalement IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) cela se traduira par \ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}.

  • Ensuite, ce tableau est entouré par`link: / formule-statistique-excel-petite-fonction [SMALL] `

fonction. Cette fonction renvoie la Nième petite valeur est dans le tableau donné. Maintenant, nous avons lien: / formules-statistiques-excel-petite-fonction [SMALL] (\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE} , RANG (1: 1)).

ROW (1: 1) renverra 1. Par conséquent, la fonction ci-dessus renverra la première plus petite valeur du tableau, qui est 2.

Lorsque vous copiez cette formule dans les cellules ci-dessous, ROW (1: 1) devient ROW (2: 2) et renvoie la deuxième plus petite valeur du tableau, à savoir 7 et ainsi de suite. Cela permet à la fonction de renvoyer la première valeur trouvée en premier. Mais si vous voulez d’abord obtenir la dernière valeur trouvée, utilisez la fonction LARGE au lieu de la fonction SMALL.

En utilisant désormais les valeurs renvoyées ci-dessus, la fonction INDEX renvoie facilement chaque valeur correspondante à partir d’une plage.

Donc, oui les gars, vous pouvez RECHERCHER plusieurs valeurs correspondantes par une valeur de recherche. Dans le gif ci-dessus, j’ai utilisé la fonction IFERROR pour détecter les erreurs et le formatage conditionnel pour le rendre un peu visuel. Dans l’exemple ci-dessus, j’ai utilisé de nombreuses autres fonctions et techniques que j’ai expliquées dans d’autres articles.

Télécharger le fichier: