Récupérer la nième correspondance avec INDEX
Pour trouver la nième correspondance d'une valeur dans une plage et récupérer les données correspondantes, vous pouvez utiliser une formule basée sur les fonctions INDEX, LIGNE, PETITE.VALEUR et SI.
Comment récupérer les informations associées à la nième correspondance d'une valeur avec INDEX ?
Pour obtenir les informations sur la 3e correspondance d'Usain Bolt dans le tableau ci-dessus, vous pouvez utiliser les fonctions LIGNE et SI ensemble pour obtenir un tableau composé des numéros de ligne de toutes les valeurs Usain Bolt. Ensuite, utilisez la fonction PETITE.VALEUR pour récupérer le numéro de ligne de la nième valeur correspondante spécifiée. Enfin, transmettez le résultat à la fonction INDEX pour obtenir les informations correspondantes sur la même ligne.
Syntaxe générique
=INDEX(plage_retour,PETITE.VALEUR(SI(plage_recherche=valeur_recherche,LIGNE(plage_recherche)-LIGNE(INDEX(plage_recherche,1,1))+1),n))
√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être validée avec Ctrl + Maj + Entrée.
- plage_retour : La plage à partir de laquelle récupérer les informations correspondant à la nième correspondance. Ici, cela fait référence à la plage année ou temps.
- plage_recherche : La plage dans laquelle rechercher la position de ligne de la nième correspondance. Ici, cela fait référence à la plage des gagnants.
- valeur_recherche : La valeur que vous spécifiez pour rechercher sa nième correspondance. Ici, cela fait référence à Usain Bolt.
- n : Indique la nième correspondance. Pour trouver la 1ère correspondance d'une valeur, définissez n comme 1 ; pour trouver la 2e correspondance, définissez n comme 2.
Pour obtenir les informations sur la 3e correspondance d'Usain Bolt, veuillez copier ou saisir les formules ci-dessous dans les cellules G8 et G9, puis appuyez sur Ctrl + Maj + Entrée pour obtenir les résultats :
Année (Cellule G8)
=INDEX(B6:B12,PETITE.VALEUR(SI(C6:C12=G5,LIGNE(C6:C12)-LIGNE(INDEX(C6:C12,1,1))+1),G6))
Temps (Cellule G9)
=INDEX(D6:D12,PETITE.VALEUR(SI(C6:C12=G5,LIGNE(C6:C12)-LIGNE(INDEX(C6:C12,1,1))+1),G6))
Explication de la formule
Ici, nous utilisons la formule ci-dessous comme exemple :
=INDEX(B6:B12,PETITE.VALEUR(SI(C6:C12=G5,LIGNE(C6:C12)-LIGNE(INDEX(C6:C12,1,1))+1),G6))
- LIGNE(C6:C12) : La fonction LIGNE renvoie les numéros de ligne de chaque cellule dans la plage C6:C12 sous forme de tableau comme ceci : {6;7;8;9;10;11;12}.
- LIGNE(INDEX(C6:C12,1,1)): Les fonctions INDEX et LIGNE renvoient le premier numéro de ligne de la plage de recherche C6:C12 dans cette feuille Excel, qui est 6.
- SI(C6:C12=G5,LIGNE(C6:C12)-LIGNE(INDEX(C6:C12,1,1))+1) = SI(C6:C12=G5,{6;7;8;9;10;11;12}-6+1) : La fonction SI vérifie si chaque valeur dans la plage C6:C12 correspond à la valeur dans la cellule G5, Usain Bolt. Si oui, la fonction renverra le résultat de « son numéro de ligne correspondant - 6 + 1 » ; sinon, elle renverra FAUX. Ainsi, les résultats seront dans un tableau comme ceci : {FAUX;2;3;4;FAUX;FAUX;FAUX}.
- PETITE.VALEUR(SI(C6:C12=G5,LIGNE(C6:C12)-LIGNE(INDEX(C6:C12,1,1))+1),G6) = PETITE.VALEUR({FAUX;2;3;4;FAUX;FAUX;FAUX},G6) : L'extrait renvoie la 3e plus petite valeur (valeur dans la cellule G6) du tableau, qui est 4.
- INDEX(B6:B12,PETITE.VALEUR(SI(C6:C12=G5,LIGNE(C6:C12)-LIGNE(INDEX(C6:C12,1,1))+1),G6)) = INDEX(B6:B12,4) La fonction INDEX renvoie la 4e valeur dans la plage année B6:B12, qui est Pékin 2008.
Fonctions connexes
La fonction INDEX d'Excel renvoie la valeur affichée en fonction d'une position donnée dans une plage ou un tableau.
La fonction SI est l'une des fonctions les plus simples et les plus utiles dans un classeur Excel. Elle effectue un test logique simple qui dépend du résultat de la comparaison, et elle renvoie une valeur si le résultat est VRAI, ou une autre valeur si le résultat est FAUX.
La fonction LIGNE d'Excel renvoie le numéro de ligne d'une référence.
Formules connexes
Récupérer la nième correspondance avec RECHERCHEV
Supposons que vous ayez une liste de valeurs et leurs informations correspondantes, pour récupérer les informations liées à la nième correspondance d'une valeur, vous pouvez utiliser une formule basée sur la fonction RECHERCHEV.
Récupérer les informations associées aux n plus petites valeurs
Pour récupérer les données correspondant à la plus petite, 2e plus petite, ou nième plus petite valeur dans une liste, un tableau ou une ligne dans Excel, vous pouvez utiliser une formule INDEX et EQUIV avec la fonction PETITE.VALEUR.
Obtenir des informations correspondant à la valeur maximale
Pour récupérer les données correspondant à la valeur maximale dans une liste, un tableau ou une ligne dans Excel, vous pouvez utiliser une formule INDEX et EQUIV avec la fonction MAX.
Obtenir des informations correspondant à la valeur minimale
Pour récupérer les données correspondant à la valeur minimale dans un tableau, vous pouvez utiliser une formule INDEX et EQUIV avec la fonction MIN.
Les meilleurs outils de productivité pour Office
Kutools pour Excel - Vous aide à vous démarquer de la foule
Kutools pour Excel compte plus de 300 fonctionnalités, garantissant que ce dont vous avez besoin n'est qu'à un clic...
Office Tab - Active la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts !
- Réduit des centaines de clics de souris pour vous chaque jour, dites adieu à la main de la souris.
- Augmente votre productivité de 50 % lors de la consultation et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.