Fórmula genérica
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Resumen
Para encontrar la coincidencia más cercana en datos numéricos, puede usar INDEX y MATCH, con la ayuda de las funciones ABS y MIN. En el ejemplo que se muestra, la fórmula en F5, copiada, es:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
donde viaje (B5: B14) y costo (C5: C14) son rangos nombrados.
En F5, F6 y F7, la fórmula devuelve el viaje más cercano en costo a 500, 1000 y 1500, respectivamente.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365.
Explicación
En esencia, esta es una fórmula de INDICE y COINCIDIR: COINCIDIR localiza la posición de la coincidencia más cercana, alimenta la posición a INDICE e INDICE devuelve el valor en esa posición en la columna Viaje. El trabajo duro se realiza con la función COINCIDIR, que se configura cuidadosamente para que coincida con la "diferencia mínima" de esta manera:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Tomando las cosas paso a paso, el valor de búsqueda se calcula con MIN y ABS así:
MIN(ABS(cost-E5)
Primero, el valor en E5 se resta del costo del rango nombrado (C5: C14). Esta es una operación de matriz y, dado que hay 10 valores en el rango, el resultado es una matriz con 10 valores como este:
(899;199;250;-201;495;1000;450;-101;500;795)
Estos números representan la diferencia entre cada costo en C5: C15 y el costo en la celda E5, 700. Algunos valores son negativos porque un costo es menor que el número en E5. Para convertir valores negativos en valores positivos, usamos la función ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
que devuelve:
(899;199;250;201;495;1000;450;101;500;795)
Estamos buscando la coincidencia más cercana, por lo que usamos la función MIN para encontrar la diferencia más pequeña, que es 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Este se convierte en el valor de búsqueda dentro de MATCH. La matriz de búsqueda se genera como antes:
ABS(cost-E5) // generate lookup array
que devuelve la misma matriz que vimos anteriormente:
(899;199;250;201;495;1000;450;101;500;795)
Ahora tenemos lo que necesitamos para encontrar la posición de la coincidencia más cercana (diferencia más pequeña), y podemos reescribir la parte COINCIDIR de la fórmula de esta manera:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Con 101 como valor de búsqueda, COINCIDIR devuelve 8, ya que 101 está en la octava posición de la matriz. Finalmente, esta posición se introduce en INDEX como el argumento de fila, con el viaje de rango nombrado como la matriz:
=INDEX(trip,8)
e INDEX devuelve el octavo viaje del rango, "España". Cuando la fórmula se copia en las celdas F6 y F7, encuentra la coincidencia más cercana a 1000 y 1500, "Francia" y "Tailandia" como se muestra.
Nota: si hay un empate, esta fórmula devolverá el primer partido.
Con XLOOKUP
La función XLOOKUP proporciona una forma interesante de resolver este problema, porque un tipo de coincidencia de 1 (coincidencia exacta o siguiente más grande) o -1 (coincidencia exacta o siguiente más pequeño) no requiere que los datos estén ordenados. Esto significa que podemos escribir una fórmula como esta:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Como anteriormente, usamos el valor absoluto de (costo-E5) para crear una matriz de búsqueda:
(899;199;250;201;495;1000;450;101;500;795)
Luego configuramos XLOOKUP para buscar cero, con el tipo de coincidencia establecido en 1, para la coincidencia exacta o la siguiente más grande. Proporcionamos el viaje de rango con nombre como la matriz de retorno, por lo que el resultado es "España" como antes.
