Formule générique
=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)
Sommaire
Pour obtenir la position de la nième correspondance (par exemple, la 2ème valeur correspondante, la 3ème valeur correspondante, etc.), vous pouvez utiliser une formule basée sur la fonction SMALL. Dans l'exemple illustré, la formule en G5 est:
=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)
Cette formule renvoie la position de la deuxième occurrence de «rouge» dans la liste.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
Cette formule utilise la plage nommée «liste» qui est la plage B5: B11.
Le noyau de cette formule est la fonction SMALL, qui renvoie simplement la nième plus petite valeur dans une liste de valeurs qui correspondent aux numéros de ligne. Les numéros de ligne ont été "filtrés" par l'instruction IF, qui applique la logique d'une correspondance. En travaillant de l'intérieur vers l'extérieur, IF compare toutes les valeurs de la plage nommée «liste» à la valeur de B5, ce qui crée un tableau comme celui-ci:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
La "valeur si vrai" est un ensemble de numéros de ligne relatifs créés par ce code:
ROW(list)-MIN(ROW(list))+1
Le résultat est un tableau comme celui-ci:
(1;2;3;4;5;6;7)
Voir cette page pour une explication complète.
Avec un test logique qui renvoie un tableau de résultats, la fonction IF agit comme un filtre - seuls les numéros de ligne correspondant à une correspondance survivent, les autres renvoient FALSE. Le résultat renvoyé par IF ressemble à ceci:
(1;FALSE;FALSE;FALSE;5;FALSE;7)
Les nombres 1, 5 et 7 correspondent à l'emplacement du "rouge" dans la liste.
Enfin, SMALL renvoie le nième plus petit élément de cette liste, en ignorant les valeurs FALSE. Dans l'exemple, F5 contient 2, donc SMALL renvoie la deuxième plus petite valeur: 5.
Obtenir la valeur associée
Une fois que vous avez la position relative de la nième correspondance, vous pouvez utiliser cette position avec la fonction INDEX pour renvoyer une valeur associée.
