Valor más próximo por defecto o por exceso

Hoy proporcionaremos la manera de localizar el valor más próximo por exceso o por defecto (por encima o por debajo) dentro de un rango.

Todo nace a partir de la duda planteada por un usuario en u comentario:

[...]Como hago para buscar el valor mas aproximado ya sea superior o inferior. Ejemplo: Columna A 5 10 13 100 120 122 105 y si en otro base de datos tengo otra columna A con la siguiente información 14 101 119 Quiero que al hacer la búsqueda de esta ultima información me arroje el resultado mas próximo ya sea inferior o superior, es decir que para el 14 me arroje el 13, para el 101 me de el 100 y para el 119 me arroje el 120. Como podrás ver en el primero y segundo identifico el 13 y 100 que son los valores mas próximo inferiores y en el ultimo caso identifico el mas próximo superior.[...]

Veamos el planteamiento:

Nos fijamos en la fórmula matricial desarrollada:
=INDICE($B$2:$B$8;COINCIDIR(MIN(ABS($B$2:$B$8-D4));ABS($B$2:$B$8-D4);0))
la clave está en la regla matemática que nos dice que el valor más próximo es aquel valor mínimo entre las diferencias de los distintos valores y el valor buscado.
Esto es, con COINCIDIR localizamos la posición de la mínima diferencia en valor absoluto (entre los diferentes valores del rango y el valor buscado) y todas las diferencias, igualmente en valor absoluto, del dicho valor buscado con los valores del rango.

Por ejemplo, si buscamos el valor 14 entre los valores del rango, tendríamos como diferencias del valor absoluto:
9
4
1
86
106
108
111
donde el valor mínimo se consigue en el importe más próximo, en este caso el 13...

Solucionando la cuestión del lector.

Otro problema relacionado sería conocer, para un valor buscado, cuáles son las cantidades por encima o por debajo (por exceso o por defecto) para dicho valor.
Para descubrir estos valores podemos optar al menos por dos métodos.

Una primer manera podría ser empleando matricialmente las funciones SI y MIN o MAX según el valor buscado (por defecto o por exceso):

haz clic en la imagen

Las primeras fórmulas matriciales serían.
1-para los valores por encima:
=MIN(SI($B$2:$B$8>=D4;$B$2:$B$8))
retornando el valro más bajo de entre los que sean mayores, es decir, justo el que esté por encima (o sea igual).
2-para los valores por debajo:
=MAX(SI($B$2:$B$8<=D4;$B$2:$B$8))
en este caso la fórmula nos devuelve de entre los valores inferiores (o iguales) el más alto, o lo que es lo mismo el dato que está justo por debajo.

Otra forma de conseguir el mismo dato, sin fórmulas matriciales, sería combinando las funciones CONTAR.SI y K.ESIMO.MAYO O K.ESIMO.MENOR.
1-para los valores por encima:
=K.ESIMO.MAYOR($B$2:$B$8;CONTAR.SI($B$2:$B$8;">="&D9))
contando el número de valores que existen por encima del valor buscado, para luego devolver el que se encuentra en esa posición (de mayor a menor) entre los valores ordenados... i.e., el valor justo por encima del buscado.
2-para los valores por debajo:
=K.ESIMO.MENOR($B$2:$B$8;CONTAR.SI($B$2:$B$8;"<="&D9))
contando el número de valores que existen por debajo del valor buscado, para luego devolver el que se encuentra en esa posición (de menor a mayor) entre los valores ordenados... i.e., el valor justo por debajo del buscado.

Dos formas de encontrar los valores que rodean el dato buscado...