Fórmula genérica
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Resumo
Para localizar a posição de um valor em uma matriz 2D, você pode usar a função SUMPRODUCT. No exemplo mostrado, as fórmulas usadas para localizar os números de linha e coluna do valor máximo na matriz são:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
onde "dados" é o intervalo nomeado C5: G14.
Nota: para este exemplo, encontramos arbitrariamente a localização do valor máximo nos dados, mas você pode substituir data = MAX (dados) por qualquer outro teste lógico que isolará um determinado valor. Observe também que essas fórmulas falharão se houver valores duplicados na matriz.
Explicação
Para obter o número da linha, os dados são comparados ao valor máximo, que gera uma matriz de resultados TRUE FALSE. Estes são multiplicados pelo resultado de ROW (dados) que gera uma matriz de números de linha associados ao intervalo nomeado "dados":
=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))
A operação de multiplicação faz com que o Excel force os valores TRUE FALSE no primeiro array para 1s e 0s, então podemos visualizar uma etapa intermediária como esta:
=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 retorna um resultado de 9, que corresponde à 9ª linha da planilha. Para obter um índice relativo ao intervalo nomeado "dados", usamos:
-ROW(data)+1
O resultado final é a matriz (5; 4; 3; 2; 1; 0; -1), a partir da qual apenas o primeiro valor (5) é exibido.
A fórmula para determinar a posição da coluna funciona da mesma maneira.
Nota: Encontrei essa abordagem em um comentário de Mike Erickson no MrExcel.com. Existem algumas outras boas idéias nesse segmento também, incluindo uma opção de fórmula de matriz.
