EXCEL: INDICE y COINCIDIR orden descendente

Para recuperar valores de una tabla donde los valores de búsqueda se ordenan en orden descendente [Z-A], puede usar INDICE y COINCIDIR, con COINCIDIR configurado para una coincidencia aproximada utilizando un tipo de coincidencia de -1. En el ejemplo que se muestra, la fórmula en F5 es:

=INDICE(C5:C9,COINCIDIR(F4,B5:B9,-1))

donde los valores en B5: B9 se ordenan en orden descendente.

Contexto

Suponga que tiene un producto que se vende en rollos de 100 pies y que los pedidos se permiten solo en rollos enteros. Por ejemplo, si necesita 200 pies de material, necesita dos rollos en total, y si necesita 275 pies, deberá comprar tres rollos. En este caso, desea que la fórmula devuelva el "siguiente nivel más alto" siempre que cruce un múltiplo par de 100.

Cómo funciona esta fórmula

Esta fórmula usa -1 para el tipo de coincidencia para permitir una coincidencia aproximada en valores ordenados en orden descendente. La parte COINCIDIR de la fórmula se ve así:

COINCIDIR(F4,B5:B9,-1)

Usando el valor de búsqueda en la celda F4, COINCIDIR encuentra el primer valor en B5: B9 que es mayor o igual que el valor de búsqueda. Si se encuentra una coincidencia exacta, COINCIDIR devuelve el número de fila relativo para esa coincidencia. Cuando no se encuentra una coincidencia exacta, COINCIDIR continúa a través de los valores en B5: B9 hasta que se encuentra un valor más pequeño, luego "retrocede" y devuelve el número de fila anterior.

En el ejemplo que se muestra, el valor de búsqueda es 275, por lo que COINCIDIR devuelve un número de fila de 3 a INDICE:

=INDICE(C5:C9,3)

La función INDICE devuelve el tercer valor en el rango C5: C9, que es $ 383.