La función DESREF aplicada a búsquedas.

Hace algunos días me llegó un correo planteándome una cuestión sobre la forma de construir un condicional SI anidado:

...En la formula siguiente hay algo que está mal y se me escapa: error #¡VALOR!, ¿sabe alquien por qué? Un saludo =SI(Y(A2<=100;C2="contado");$B$14;$C$14);SI(Y(A2>100;A2<150;C2="contado");$B$15;$C$15);SI(Y(A2>150;A2<150;C2="contado");$B$16;$C$16);SI(Y(A2>200;C2="contado");$B$17;$C$17) Te adjunto el problema. En la columna F, tengo que hallar los dtos, que dependerán del tamaño del pedido y de si el pago es al contado o a 30 días exclusivamente...

Mi respuesta, después de ver el ejemplo, fue clara, no siempre la función SI condicional es la respuesta óptima. Mi recomendación fue el uso de la función DESREF, de la que ya hablé en esta entrada del blog.
En definitiva se trata de determinar un porcentaje de 'Descuento' condicionado a dos variables ('Cantidad de pedido' y 'Forma de pago'), siendo nuestras tablas de trabajo:

Sobre el rango A8:E12 deberemos obtener el porcentaje de descuento que le corresponde, descuento sólo válido para los pagos al contado o aplazados 30 días; esto es, para el resto de formas de pago (60 días o 90 días) no existe descuento, o lo que es lo mismo, el descuento es 0%.
En nuestra columna F, para cada registro de pedido añadiremos una función DESREF en su modalidad de búsqueda:
=DESREF(celda ancla; filas; columnas)
siendo nuestra 'celda ancla' la celda A8, celda superior izquierda de la tabla de datos. La clave es, por tanto, determinar correctamente el cruce de filas por columnas que devuelva el 'descuento' correcto. Esto lo conseguiremos con otra función ya conocida:
=COINCIDIR(valor buscado; en matriz; [coincidencia])
fundamentas en este caso es el argumento [coincidencia], ya que para la 'Cantidad de pedido', la primera de nuestras variables, se requiere una búsqueda por tramos, es decir, fila 9 cantidades entre 0 y 99, fila 10 cantidades entre 100 y 149, fila 11 cantidades entre 150 y 199, y por último, fila 9 cantidades superiores a 199.
La correcta asunción de fila la lograremos con un tipo de coincidencia 1; que encuentra el mayor valor que es menor o igual que el valor buscado. Es importante que los valores de la matriz de busqueda se encuentren ordenados de forma ascendente!!
=COINCIDIR(A2;$A$9:$A$12;1)
De manera similar realizaremos una búsqueda con la función COINCIDIR para la otra variable ('Forma de pago'), pero en esta ocasión el argumento 'coincidencia' deberá ser 0, i.e., coincidencia exacta:
=COINCIDIR(C2;$B$8:$E$8;0)
En ambos casos obtendremos un valor numérico que definirá la situación del porcentaje correspondiente; para la coincidencia de la variable 'Cantidad de pedido' tendremos el número de filas hacia abajo (ya que es un valor positivo) y para la variable 'Forma de pago' el número de columnas a la derecha (también un valor positivo) que nos debemos desplazar desde la celda ancla (celda A8) dentro de la tabla de porcentajes (rango A8:E12).

haz click en la imagen