EXCEL: Obtener la ubicación del valor en una matriz 2D

Para ubicar la posición de un valor en una matriz 2D, puede usar la función SUMAPRODUCTO. 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:

=SUMAPRODUCTO((data=MAX(data))*FILA(data))-FILA(data)+1
=SUMAPRODUCTO((data=MAX(data))*COLUMNA(data))-COLUMNA(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 (data) con cualquier otra prueba lógica que aisle un valor dado. También tenga en cuenta que estas fórmulas fallarán si hay valores duplicados en la matriz.

Cómo funcionan estas fórmulas

Para obtener el número de fila, los datos se comparan con el valor máximo, que genera una matriz de resultados VERDADERO FALSO. Estos se multiplican por el resultado de FILA (datos) que genera y una matriz de números de fila asociados con el rango de "datos" con nombre:

=SUMAPRODUCTO({FALSO,FALSO,FALSO,FALSO,FALSO;FALSO,FALSO,FALSO,FALSO,FALSO;FALSO,FALSO,FALSO,FALSO,FALSO;FALSO,FALSO,FALSO,FALSO,FALSO;FALSO,FALSO,VERDADERO,FALSO,FALSO;FALSO,FALSO,FALSO,FALSO,FALSO;FALSO,FALSO,FALSO,FALSO,FALSO}*{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 1s y 0s, por lo que podemos visualizar un paso intermedio como este:

=SUMAPRODUCTO({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})

SUMAPRODUCTO luego devuelve un resultado de 9, que corresponde a la novena fila de la hoja de trabajo. Para obtener un índice relativo al rango "datos" con nombre, utilizamos:

-FILA(data)+1

El resultado final es la matriz {5; 4; 3; 2; 1; 0; -1}, de la que 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 otras buenas ideas en ese hilo, incluida una opción de fórmula de matriz.