EXCEL: Obtener última posición de coincidencia encontrada

Para obtener la posición de la última coincidencia (es decir, la última aparición) de un valor de búsqueda, puede usar una fórmula de matriz basada en las funciones SI, FILA, INDICE, COINCIDIR y MAX. En el ejemplo que se muestra, la fórmula en H6 es:

{=MAX(SI(names=H5,FILA(names)-MIN(FILA(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.

Cómo funciona esta fórmula

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 utilizando los "nombres" de rango con nombre solo por conveniencia.
Trabajando de adentro hacia afuera, esta parte de la fórmula generará un conjunto relativo de números de fila:

FILA(names)-MIN(FILA(names))+1

El resultado de la expresión anterior es una matriz de números como este:

{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 de esta manera:

SI(names=H5,FILA(names)-MIN(FILA(names))+1)

Esto da como resultado una matriz que se ve así:

{1;FALSO;FALSO;4;FALSO;FALSO;7;FALSO}

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 con nombre "nombres" es igual a "amy" (es decir, 1, 4, 7). Todos los demás elementos de la matriz son FALSO, 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 en 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.

Penúltimo, etc.

Para obtener la penúltima posición, la penúltima posición, etc., puede cambiar de la función MIN a la función K.ESIMO.MAYOR de esta manera:

{=K.ESIMO.MAYOR(SI(criteria,FILA(rng)-MIN(FILA(rng))+1),k)}

donde k representa "enésimo más grande". Por ejemplo, para obtener la penúltima coincidencia en el ejemplo anterior, puede usar:

{=K.ESIMO.MAYOR(SI(names=H5,FILA(names)-MIN(FILA(names))+1),2)}

Como antes, esta es una fórmula de matriz y debe ingresarse con control + shift + enter.