Fórmula genérica
=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)
Resumen
Para obtener la posición de la enésima coincidencia (por ejemplo, el segundo valor coincidente, el tercer valor coincidente, etc.), puede utilizar una fórmula basada en la función PEQUEÑA. En el ejemplo que se muestra, la fórmula en G5 es:
=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)
Esta fórmula devuelve la posición de la segunda aparición de "rojo" en la lista.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
Esta fórmula utiliza el rango denominado "lista", que es el rango B5: B11.
El núcleo de esta fórmula es la función PEQUEÑA, que simplemente devuelve el enésimo valor más pequeño en una lista de valores que corresponden a los números de fila. Los números de fila han sido "filtrados" por la declaración IF, que aplica la lógica para una coincidencia. Trabajando desde adentro hacia afuera, IF compara todos los valores en el rango nombrado "lista" con el valor en B5, lo que crea una matriz como esta:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
El "valor si es verdadero" es un conjunto de números de fila relativos creados por este código:
ROW(list)-MIN(ROW(list))+1
El resultado es una matriz como esta:
(1;2;3;4;5;6;7)
Consulte esta página para obtener una explicación completa.
Con una prueba lógica que devuelve una matriz de resultados, la función SI actúa como un filtro: solo los números de fila que corresponden a una coincidencia sobreviven, el resto devuelve FALSO. El resultado devuelto por IF se ve así:
(1;FALSE;FALSE;FALSE;5;FALSE;7)
Los números 1, 5 y 7 corresponden a la ubicación de "rojo" en la lista.
Finalmente, SMALL devuelve el enésimo elemento más pequeño de esta lista, ignorando los valores FALSE. En el ejemplo, F5 contiene 2, por lo que SMALL devuelve el segundo valor más pequeño: 5.
Obtener valor asociado
Una vez que tenga la posición relativa de la enésima coincidencia, puede usar esa posición con la función INDICE para devolver un valor asociado.
