Fórmula genérica
=INDEX(location,XMATCH(0,distance,1))
Resumo
Para localizar o local mais próximo pela distância, você pode usar uma fórmula baseada na função XMATCH com a função INDEX. No exemplo mostrado, a fórmula na célula E5 é:
=INDEX(location,XMATCH(0,distance,1))
onde localização (B5: B12) e distância (C5: C12) são intervalos nomeados.
Explicação
Basicamente, esta fórmula é uma fórmula básica de ÍNDICE e CORRESPONDÊNCIA. No entanto, em vez de usar a função MATCH mais antiga, estamos usando a função XMATCH, que fornece uma configuração de modo de correspondência mais poderosa:
=INDEX(location,XMATCH(0,distance,1))
Trabalhando de dentro para fora, estamos usando a função XMATCH para encontrar a posição do local mais próximo:
XMATCH(0,distance,1) // find row nearest zero
Fazemos isso definindo o valor de pesquisa como zero (0), a matriz de pesquisa como a distância (C5: C12) e o modo de correspondência como 1.
Um valor de modo de correspondência de 1 informa ao XMATCH para encontrar uma correspondência exata ou o próximo maior valor. Visto que o valor de pesquisa é fornecido como zero (0), o XMATCH encontrará a primeira distância maior que zero. Um grande benefício do XMATCH - o que o diferencia do MATCH - é que não é o array de pesquisa a ser classificado. Independentemente da ordem, MATCH retornará a primeira correspondência exata ou o próximo maior valor.
No exemplo, XMATCH retorna 5, já que a menor distância é 7 (localização G), que aparece em quinto lugar na lista. A fórmula resolve para:
=INDEX(location,5) // returns "G"
e INDEX retorna o quinto item do local do intervalo nomeado (B5: B12), que é "G".
Nota: em caso de empate, o XMATCH retornará a primeira correspondência para os valores empatados.
Obter distância
A fórmula para retornar a distância real do local mais próximo é quase a mesma. Em vez de dar a INDEX os nomes dos locais, damos a INDEX as distâncias. A fórmula em F5 é:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH retorna o mesmo resultado acima (5) e INDEX retorna 7.
