Fórmula genérica
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Resumo
Para encontrar a correspondência mais próxima em dados numéricos, você pode usar INDEX e MATCH, com ajuda das funções ABS e MIN. No exemplo mostrado, a fórmula em F5, copiada, é:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
onde viagem (B5: B14) e custo (C5: C14) são intervalos nomeados.
Em F5, F6 e F7, a fórmula retorna a viagem mais próxima em custo de 500, 1000 e 1500, respectivamente.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter, exceto no Excel 365.
Explicação
Basicamente, esta é uma fórmula INDEX e MATCH: MATCH localiza a posição da correspondência mais próxima, alimenta a posição para INDEX e INDEX retorna o valor naquela posição na coluna Trip. O trabalho árduo é feito com a função MATCH, que é cuidadosamente configurada para corresponder à "diferença mínima" assim:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Fazendo as coisas passo a passo, o valor de pesquisa é calculado com MIN e ABS assim:
MIN(ABS(cost-E5)
Primeiro, o valor em E5 é subtraído do custo do intervalo nomeado (C5: C14). Esta é uma operação de matriz e, como há 10 valores no intervalo, o resultado é uma matriz com 10 valores como este:
(899;199;250;-201;495;1000;450;-101;500;795)
Esses números representam a diferença entre cada custo em C5: C15 e o custo na célula E5, 700. Alguns valores são negativos porque um custo é menor que o número em E5. Para converter valores negativos em valores positivos, usamos a função ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
que retorna:
(899;199;250;201;495;1000;450;101;500;795)
Estamos procurando a correspondência mais próxima, então usamos a função MIN para encontrar a menor diferença, que é 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Este se torna o valor de pesquisa dentro de MATCH. A matriz de pesquisa é gerada como antes:
ABS(cost-E5) // generate lookup array
que retorna a mesma matriz que vimos anteriormente:
(899;199;250;201;495;1000;450;101;500;795)
Agora temos o que precisamos para encontrar a posição da correspondência mais próxima (menor diferença) e podemos reescrever a parte da correspondência da fórmula assim:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Com 101 como o valor de pesquisa, MATCH retorna 8, já que 101 está na 8ª posição na matriz. Finalmente, esta posição é alimentada em INDEX como o argumento da linha, com a viagem de intervalo nomeada como a matriz:
=INDEX(trip,8)
e INDEX retorna a 8ª viagem no intervalo, "Espanha". Quando a fórmula é copiada para as células F6 e F7, ela encontra a correspondência mais próxima de 1000 e 1500, "França" e "Tailândia", conforme mostrado.
Nota: se houver empate, esta fórmula retornará a primeira correspondência.
Com XLOOKUP
A função XLOOKUP fornece uma maneira interessante de resolver esse problema, porque um tipo de correspondência 1 (correspondência exata ou próximo maior) ou -1 (correspondência exata ou próximo menor) não requer que os dados sejam classificados. Isso significa que podemos escrever uma fórmula como esta:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Como acima, usamos o valor absoluto de (custo-E5) para criar uma matriz de pesquisa:
(899;199;250;201;495;1000;450;101;500;795)
Em seguida, configuramos o XLOOKUP para procurar zero, com o tipo de correspondência definido como 1, para correspondência exata ou o próximo maior. Fornecemos a viagem de intervalo nomeada como a matriz de retorno, portanto, o resultado é "Espanha" como antes.
