Avec RECHERCHEV, nous obtenons toujours le premier match. La même chose se produit avec la fonction INDEX MATCH. Alors, comment VLOOKUP deuxième match ou 3e ou nième?

Dans cet article, nous allons apprendre comment obtenir la Nième occurrence d’une valeur dans la plage.

0055

Formule générique

{=SMALL(IF(range=value,ROW(range)-ROW(first_cell_in_range)+1),n)}

Remarque: ceci est un lien: / excel-array-formulas-arrays-in-excel-formula [array formula] . Vous devez le saisir avec CTRL + MAJ + ENTRÉE . Plage: la plage dans laquelle vous souhaitez rechercher la nième position de valeur *.

Valeur: la valeur dont vous recherchez la nième position dans la plage. First_cell_in_range: la première cellule de la plage. Si la plage est A2: A10, la première cellule de la plage est A2. *

n: le nombre d’occurrences de valeurs.

Voyons un exemple pour clarifier les choses.

===

Exemple: trouver la deuxième correspondance dans Excel

J’ai donc ici cette liste de noms dans la gamme Excel A2: A10. J’ai nommé cette gamme comme des noms. Maintenant, je veux obtenir la position de la deuxième occurrence de «Rony» dans les noms.

0056

Dans l’image ci-dessus, nous pouvons voir qu’il est en 7ème position dans la plage A2: A10 (noms). Nous devons maintenant obtenir sa position en utilisant une formule Excel.

Appliquez la formule générique ci-dessus dans C2 pour rechercher la deuxième occurrence de Rony dans la liste.

{=SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2)}

Entrez-le avec CTRL + SHIFT + ENTER ..

0057

Et nous avons une réponse. Il montre 7, ce qui est correct. Si vous changez la valeur de n à 3, cela donnera 8. Si vous changez la valeur de n supérieure à l’occurrence de la valeur dans la plage, elle renverra l’erreur #NUM.

===

Comment ça marche?

Eh bien, c’est assez facile. Voyons chaque partie une par une.

link: / tips-if-condition-in-excel [IF] (names = “Rony”, link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) + 1 *)

:

Dans IF, names = « Rony » renvoie un tableau de TRUE et FALSE. TRUE chaque fois qu’une cellule dans les noms de plage (A2: A10) correspond à «Rony». \ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}.

Suivant link: / lookup-and-reference-excel-row-function [ROW] (names) –link: / lookup-and-reference-excel-row-function [ROW] `(A2) +1: lien: / lookup-and-reference-excel-row-function [ROW] `(names *):

ici la fonction ROW renvoie le numéro de ligne de chaque cellule dans les noms.

\ {2; 3; 4; 5; 6; 7; 8; 9; 10}.

link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) * Ensuite, nous soustrayons la ligne nombre de A2 de chaque valeur dans le tableau donné. Cela nous donne un tableau de numéros de série à partir de 0.

\ {0; 1; 2; 3; 4; 5; 6; 7; 8}.

link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) +1: Pour obtenir numéros de série à partir de 1, nous ajoutons 1 à chaque valeur de ce tableau. Cela nous donne le numéro de série à partir de 1.

\ {1; 2; 3; 4; 5; 6; 7; 8; 9}.

Nous avons maintenant IF (\ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}, \ {1; 2; 3; 4; 5; 6; 7; 8; 9}).

Cela se résout en \ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}.

Maintenant, nous avons la formule résolue en lien: / formules-statistiques-excel-petite-fonction [PETIT] (\ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}, 2).

Maintenant SMALL renvoie la deuxième plus petite valeur de la plage, qui est 7.

Comment l’utilisons-nous?

La question se pose: quel est l’avantage d’obtenir un index brut du nième match? Il serait plus utile de récupérer les informations associées à partir de la nième valeur. Eh bien, cela peut être fait aussi. Si nous voulons obtenir une valeur à partir de la valeur de nième correspondance de la cellule adjacente dans les noms de plage (A2: A10).

{=INDEX(B2:B10, SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2))}

Alors oui les gars, voici comment vous pouvez obtenir le nième match dans une plage. J’espère avoir été suffisamment explicatif. Si vous avez des doutes concernant cet article ou tout autre sujet lié à Excel / VBA, écrivez dans la section commentaires ci-dessous.

Articles liés:

link: / information-formulas-how-to-get-sequential-row-number-in-excel [Comment obtenir un numéro de ligne séquentiel dans Excel]

link: / lookup-formulas-vlookup-top-5-values-with-duplicate-values-using-index-match-in-excel [Vlookup Top 5 Values ​​with Duplicate Values ​​Using INDEX-MATCH in Excel]

lien: / lookup-formulas-vlookup-multiple-values ​​[VLOOKUP Multiple Values]

link: / lookup-formulas-use-index-and-match-to-lookup-value [Utiliser INDEX et MATCH pour rechercher la valeur]

link: / lookup-formulas-lookup-value-with-multiple-criteria [Valeur de recherche avec plusieurs critères]

Articles populaires:

link: / formulas-and-functions-introduction-of-vlookup-function [La fonction RECHERCHEV dans Excel]

lien: / tips-countif-in-microsoft-excel [COUNTIF dans Excel 2016]

link: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel]