Fórmula genérica
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Resumen
Para localizar la posición de un valor en una matriz 2D, puede utilizar la función SUMPRODUCTO. En el ejemplo que se muestra, las fórmulas utilizadas para ubicar los números de fila y columna del valor máximo en la matriz son:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
donde "datos" es el rango con nombre C5: G14.
Nota: para este ejemplo, encontramos arbitrariamente la ubicación del valor máximo en los datos, pero puede reemplazar data = MAX (datos) con cualquier otra prueba lógica que aislará un valor dado. También tenga en cuenta que estas fórmulas fallarán si hay valores duplicados en la matriz.
Explicación
Para obtener el número de fila, los datos se comparan con el valor máximo, lo que genera una matriz de resultados VERDADERO FALSO. Estos se multiplican por el resultado de ROW (datos) que genera una matriz de números de fila asociados con el rango denominado "datos":
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE)*(5;6;7;8;9;10;11))
La operación de multiplicación hace que Excel coaccione los valores VERDADERO FALSO en la primera matriz a 1 y 0, por lo que podemos visualizar un paso intermedio como este:
=SUMPRODUCT((0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0)*(5;6;7;8;9;10;11))
SUMPRODUCT luego devuelve un resultado de 9, que corresponde a la novena fila de la hoja de trabajo. Para obtener un índice relativo al rango denominado "datos", usamos:
-ROW(data)+1
El resultado final es la matriz (5; 4; 3; 2; 1; 0; -1), de la cual solo se muestra el primer valor (5).
La fórmula para determinar la posición de la columna funciona de la misma manera.
Nota: Me encontré con este enfoque en un comentario de Mike Erickson en MrExcel.com. También hay algunas otras buenas ideas en ese hilo, incluida una opción de fórmula de matriz.
