Como obter a referência do resultado de uma fórmula de matriz em uma lista?

Question

Eu tenho duas matrizes, cada uma inclui ids de lojas, coordenadas xe coordenadas y para nós e para a competição.

Eu estou procurando por cada loja da ABC corp, sua concorrente mais próxima na lista XYZ corp.

Até agora, consegui obter a distância entre cada loja ABC e sua comp. XYZ mais próxima, com uma fórmula de matriz na col. H:

'{MIN((sqrt((Power(B3-$F$3:$F$6;2)+(power(C3-$G$3:$G$6;2))))))}

Eu estou preso em encontrar para cada ABCi o nome XYZi (ou referência de linha) correspondente ao valor calculado em H.

Eu tenho trabalhado por duas horas com Match () e Index () - como sugerido em várias perguntas semelhantes no SE - e pesquisando tudo o que posso pensar, mas sem sucesso.

(Eu pareço lembrar que para as coordenadas XY em um esferóide a fórmula para a distância entre dois pontos em um mapa pode ser diferente da usada aqui, mas para o propósito da minha vida real é bom o suficiente.)

    

Answer 1

A partir da solução @pnuts, podemos ignorar a representação da matriz.

Para obter a distância mínima entre dois pontos em uma grade entre duas coordenadas e um conjunto (fórmula de matriz ctrl + Deslocamento + Enter ) :

'={MIN((sqrt((Power(B3-$F$3:$F$6;2)+(power(C3-$G$3:$G$6;2))))))}

Para pular a exibição da matriz, usamos o cálculo da distância (sem o MIN ) como o segundo argumento para MATCH() . Ele retornará a posição do índice do valor dentro da matriz.

Em seguida, CHOOSE para obter o membro k na lista E3:E6 , que é a origem do valor mínimo:

{CHOOSE(MATCH(MIN((sqrt((B3-$F$3:$F$6)^2+((C3-$G$3:$G$6)^2))))),
(sqrt(((B3-$F$3:$F$6)^2+((C3-$G$3:$G$6)^2)))),0),$E$3;$E$4;$E$5:$E$6)}

Como é uma fórmula de matriz, ela deve ser validada com ctrl + Deslocamento + Enter

A única coisa que resta é encontrar uma forma de contornar as 255 limitações de valores individuais da fórmula CHOOSE para a lista acima desse intervalo.

(A fórmula para obter a distância entre duas coordenadas XY em um esferóide é diferente da usada aqui, mas para uma pequena área de 2/3 de latitude e longitude está perto o suficiente.)

    

Answer 2

Acho que a fórmula do array pode estar complicando as coisas. Uma matriz real poderia mostrar todas as distâncias =SQRT((E$2-$B4)^2+(E$3-$C4)^2) e, em seguida:

=CHOOSE(MATCH(MIN(E4:H4),$E4:$H4,0),E$1,F$1,G$1,H$1)

para escolher o mais próximo em cada caso:

Ou para destacar o mínimo para cada linha, você pode usar a formatação condicional com a fórmula =E4=MIN($E4:$H4) .