Formule Excel: obtenir la nième correspondance avec INDEX / MATCH -

Formule générique

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Sommaire

Pour récupérer plusieurs valeurs correspondantes à partir d'un ensemble de données avec une formule, vous pouvez utiliser les fonctions IF et SMALL pour déterminer le numéro de ligne de chaque correspondance et renvoyer cette valeur à INDEX. Dans l'exemple illustré, la formule en I7 est:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Où les plages nommées sont amts (D4: D11), id (I3) et ids (C4: C11).

Notez qu'il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.

Explication

Au fond, cette formule est simplement une formule INDEX qui récupère la valeur dans un tableau à une position donnée. La valeur de n est fournie dans la colonne H, et tout le travail "lourd" que la formule fait est de déterminer la ligne à partir de laquelle récupérer une valeur, où la ligne correspond à la "nième" correspondance.

La fonction IF fait le travail de déterminer quelles lignes contiennent une correspondance, et la fonction SMALL renvoie la nième valeur de cette liste. À l'intérieur de IF, le test logique est:

ids=id

ce qui donne ce tableau:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Notez que l'ID client correspond aux 1ère et 4ème positions, qui apparaissent comme TRUE. L'argument "valeur si vrai" dans IF génère une liste de numéros de ligne relatifs avec cette expression:

ROW(ids)-ROW(INDEX(ids,1,1))+1

qui produit ce tableau:

(1;2;3;4;5;6;7)

Ce tableau est ensuite «filtré» par les résultats du test logique et la fonction IF renvoie le résultat du tableau suivant:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Notez que nous avons des numéros de ligne valides pour les lignes 1 et 2.

Ce tableau est ensuite traité par SMALL, qui est configuré pour utiliser les valeurs de la colonne H pour renvoyer "nième" valeurs. La fonction SMALL ignore automatiquement les valeurs logiques TRUE et FALSE dans le tableau. Au final, les formules se réduisent à:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Gestion des erreurs

Une fois qu'il n'y a plus de correspondance pour un identifiant donné, la fonction SMALL renverra une erreur #NUM. Vous pouvez gérer cette erreur avec la fonction IFERROR ou en ajoutant une logique pour compter les correspondances et interrompre le traitement une fois que le nombre de la colonne H est supérieur au nombre de correspondances. L'exemple ici montre une approche.