Fórmula genérica
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Resumen
Para obtener la posición de la última coincidencia (es decir, la última aparición) de un valor de búsqueda, puede utilizar una fórmula de matriz basada en las funciones IF, ROW, INDEX, MATCH y MAX. En el ejemplo que se muestra, la fórmula en H6 es:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Donde "nombres" es el rango con nombre C4: C11.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
La esencia de esta fórmula es que construimos una lista de números de fila para un rango dado, haciendo coincidir un valor, y luego usamos la función MAX para obtener el número de fila más grande, que corresponde al último valor coincidente. Estamos usando los "nombres" de rango con nombre sólo por conveniencia.
Trabajando desde adentro hacia afuera, esta parte de la fórmula generará un conjunto relativo de números de fila:
ROW(names)-MIN(ROW(names))+1
El resultado de la expresión anterior es una matriz de números como esta:
(1;2;3;4;5;6;7;8)
Observe que obtenemos 8 números, correspondientes a las 8 filas de la tabla. Consulte esta página para obtener detalles sobre cómo funciona esta parte de la fórmula.
Para el propósito de esta fórmula, solo queremos números de fila para valores coincidentes, por lo que usamos la función SI para filtrar los valores así:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Esto da como resultado una matriz que se ve así:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Tenga en cuenta que esta matriz todavía contiene ocho elementos. Sin embargo, solo han sobrevivido los números de fila donde el valor en el rango nombrado "nombres" es igual a "amy" (es decir, 1, 4, 7). Todos los demás elementos de la matriz son FALSOS, ya que fallaron la prueba lógica en la función SI.
Finalmente, la función SI entrega esta matriz a la función MAX. MAX devuelve el valor más alto de la matriz, el número 7, que corresponde al último número de fila donde el nombre es "amy". Una vez que conocemos el último número de fila coincidente, podemos usar INDICE para recuperar un valor en esa posición.
De penúltimo, etc.
Para obtener la penúltima posición, la penúltima, etc., puede cambiar de la función MIN a la función LARGE de esta manera:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
donde k representa "n-ésimo más grande". Por ejemplo, para obtener la penúltima coincidencia en el ejemplo anterior, puede usar:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Como antes, esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
