RECHERCHEV: recherche le nième élément

RECHERCHEV: recherche le nième élément (sans colonnes d’assistance)

Dans cette série, Mastering VLOOKUP, nous avons couvert beaucoup de terrain. Mais ce post est l’un des plus diaboliques de tous. VLOOKUP, de par sa conception, est destiné à renvoyer la première correspondance trouvée. Mais, si nous voulons le 2e, 3e ou nième RECHERCHEV, ce n’est pas si facile. Souvent, les gens ont recours à des colonnes d’aide, mais ce n’est vraiment pas nécessaire (et loin d’être aussi amusant!)

Recherche de la nième recherche

Regardez la capture d’écran ci-dessous. Nos données sont triées par ordre d’âge, mais comment pourrions-nous trouver l’âge du 3e Dave dans la liste?

RECHERCHEV Rechercher le nième élément

La formule dans la cellule E6 est:

{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)}

Tout d’abord – c’est une formule matricielle. Ne tapez pas le {} au début et à la fin, mais entrez la formule avec Ctrl + Maj + Entrée et Excel les ajoutera automatiquement.

Deuxièmement – oui, cette formule fait peur! Mais restez avec moi, je ferai de mon mieux pour l’expliquer.

Comment fonctionne la formule?

La clé de cette formule fonctionne correctement dans la fonction SMALL avec la fonction ROW. Construisons la formule petit à petit.

Fonction IF

{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)}

La section en surbrillance rouge est presque une fonction IF standard. Cependant, il travaille sur une plage de cellules plutôt que sur une seule cellule. Si une cellule dans A2-A8 est égale à la valeur de recherche, Dave dans notre exemple, le numéro de ligne de cette cellule est renvoyé. Si le nom n’est pas Dave, FALSE est renvoyé. Le nom Dave est dans les rangées 3, 4 et 8, donc la fonction IF renverrait un tableau de {FALSE, 3, 4, FALSE, FALSE, FALSE, 8}.

SMALL Fonction

{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)}

Nous avons encapsulé le résultat de la fonction IF dans la fonction SMALL. La cellule E4 est la nième valeur que nous recherchons. Dans notre exemple, E4 est 3, car nous recherchons la 3ème recherche. Cela recherchera maintenant la troisième plus petite valeur du tableau de fonctions IF renvoyé ci-dessus. Le troisième plus petit résultat du tableau est 8; {FALSE, 3,4, FALSE, FALSE, FALSE,8}.

Ceci termine le premier argument de la fonction RECHERCHEV; la valeur de recherche est 8. Même si nous recherchons le 3ème RECHERCHEV de Dave, le 3ème Dave est dans la 8ème ligne. Donc, nous recherchons 8. Espérons que ce n’est pas trop déroutant.

CHOISIR la fonction

La fonction CHOOSE fonctionne de la même manière que celle que nous avons rencontrée lors d’une RECHERCHEV vers la gauche. Cependant, nous utiliserons le numéro de ligne comme colonne de recherche.

Dans la fonction CHOOSE, les nombres dans {} déterminent quelle colonne est laquelle.

{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)}

La colonne 1, la colonne de recherche, est simplement le numéro de ligne. Cela renverrait un tableau de {2, 3, 4, 5, 6, 7, 8}. Notre valeur de recherche est 8, donc notre RECHERCHEV sera en mesure de trouver une valeur.

{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)} 

Fonction RECHERCHEV
{=VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},
ROW(A2:A8),B2:B8),2,0)}

La fonction RECHERCHEV est utilisée pour effectuer une correspondance exacte de la ligne numéro 8 et renvoyer la valeur de la colonne 2 (cellules B2-B8).

Le résultat

Le résultat de notre formule est 47. Nous pouvons adapter cette formule pour trouver la nième recherche à partir de n’importe quel ensemble de données.

VLOOKUP Lookup the nth item

Cela a été difficile d’y aller pendant un moment, mais j’espère que vous avez réussi.

Quelques commentaires / conseils

Existe-t-il un moyen plus efficace?

Oui il y a. Une fois que nous avons obtenu le numéro de ligne d’une valeur correspondante, il serait probablement plus facile d’appliquer une formule INDEX plutôt qu’une RECHERCHEV. Mais le fait est que VLOOKUP est une option possible. Si RECHERCHEV est l’une des rares fonctions que vous connaissez, alors il l’utilise.

Caractères d’espacement

Nous pourrions / devrions utiliser des caractères d’espacement dans cette formule. Bien qu’il soit très peu probable de renvoyer un résultat incorrect avec notre exemple, cela pourrait être possible avec d’autres ensembles de données, il est donc préférable d’utiliser des caractères d’espacement. Compte tenu de la complexité de l’exemple, j’ai décidé de les laisser de côté. Si nous les voulions, la formule serait:

{=VLOOKUP(E3&"^"&SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),
CHOOSE({1,2},A2:A8&"^"&ROW(A2:A8),B2:B8),2,0)}

Colonnes d’assistance

Si cet exemple semble trop complexe, l’option la plus simple est simplement d’utiliser une colonne d’aide.

RECHERCHEV recherche le nieme element sans colonnes dassistance

La formule en B2 est:

=A2&"^"&COUNTIF($A$2:A2,A2)

Copiez cette formule en B8. Alors la formule en F6 pourrait être:

=VLOOKUP(F3&"^"&F4,B2:C8,2,0)

Facile.

 Autres articles de la série Mastering VLOOKUP

 

N’oubliez pas:

Si vous avez trouvé cet article utile ou si vous avez une meilleure approche, veuillez laisser un commentaire ci-dessous.

Avez-vous besoin d’aide pour l’adapter à vos besoins?

Je suppose que les exemples de cet article ne correspondaient pas exactement à votre situation. Nous utilisons tous Excel différemment, il est donc impossible d’écrire un article qui répondra aux besoins de chacun. En prenant le temps de comprendre les techniques et principes de cet article (et ailleurs sur ce site) vous devriez pouvoir l’adapter à vos besoins.

Mais si vous avez encore du mal :

  1. Lisez d’autres blogs ou regardez des vidéos YouTube sur le même sujet. Vous en bénéficierez beaucoup plus en découvrant vos propres solutions.
  2. Demandez le «Excel Ninja» dans votre bureau. C’est incroyable ce que les autres savent.
  3. Posez une question dans un forum   Excel, ou la Communauté Microsoft Answers. N’oubliez pas que les personnes présentes sur ces forums donnent généralement leur temps gratuitement. Veillez donc à rédiger votre question, assurez-vous qu’elle est claire et concise. Répertoriez toutes les choses que vous avez essayées et fournissez des captures d’écran, des segments de code et des exemples de classeurs.

Et ensuite?
N’y allez pas encore, il y a beaucoup plus à apprendre sur comment-supprimer. Consultez les derniers articles:

Laisser un commentaire

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