Coincidencias aproximadas para tablas por niveles

Una tabla de niveles clasifica valores continuos en intervalos. Algunos ejemplos son los tramos de impuestos, las tarifas de envío según el peso, los descuentos por volumen y las calificaciones con letras según la puntuación.

No necesita una fila para cada valor posible, sino únicamente el umbral inicial de cada intervalo. Una puntuación de 87 no tiene una entrada exacta, pero pertenece al intervalo que comienza en 80.

Aquí es donde la coincidencia aproximada resulta especialmente útil: encuentra el intervalo correcto en lugar de exigir una coincidencia exacta.

Hasta ahora hemos usado MATCH(value, range, 0) para una coincidencia exacta. El tercer argumento 0 significa «buscar este valor con precisión o devolver #N/A».

Para los niveles usamos el tipo de coincidencia 1. Este encuentra el valor más grande que sea menor o igual que el valor buscado. Así es exactamente como debe comportarse una búsqueda por intervalos.

Una regla fundamental: con el tipo de coincidencia 1, la lista de umbrales debe estar ordenada de forma ascendente.

Imagine una tabla de calificaciones. La columna E contiene los umbrales inferiores, ordenados de forma ascendente: 0, 60, 70, 80, 90. La columna F contiene las etiquetas: F, D, C, B, A.

Una puntuación de 0 a 59 corresponde a F, de 60 a 69 a D, y así sucesivamente. Solo almacenamos el inicio de cada intervalo, no todas las puntuaciones.

Nuestro objetivo es devolver la calificación con letra correspondiente a una puntuación situada en G1.

Use MATCH aproximado para encontrar en qué intervalo se encuentra una puntuación. MATCH(G1, E2:E6, 1), con una puntuación de 87, busca el umbral más grande que sea menor o igual que 87.

Los umbrales son 0, 60, 70, 80 y 90. El mayor que no supera 87 es 80, que ocupa la posición 4. Por tanto, MATCH devuelve 4.

Esa posición apunta al intervalo correcto, aunque 87 no aparezca en la lista.

Ahora introduzca la posición en INDEX sobre la columna de etiquetas F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) toma la posición 4 y devuelve la cuarta etiqueta, «B».

Por tanto, una puntuación de 87 se asigna correctamente a la calificación B. Cambie G1 a 95 y MATCH devolverá 5, lo que dará «A»; cámbielo a 55 y MATCH devolverá 1, lo que dará «F».

MATCH aproximado (tipo 1) requiere un orden ascendente en el rango de búsqueda. Da por supuesto que los datos aumentan y se detiene en cuanto supera el valor buscado.

Si los umbrales no están ordenados, MATCH puede detenerse demasiado pronto y devolver una posición incorrecta sin avisar mediante ningún error. Ordene siempre la columna de umbrales de menor a mayor antes de basarse en una búsqueda por niveles.

XLOOKUP también puede realizar coincidencias aproximadas. Su quinto argumento, el modo de coincidencia, acepta -1 para «coincidencia exacta o el siguiente elemento menor», una opción perfecta para las tablas de niveles.

Esto encuentra el umbral más grande que sea menor o igual que G1 y devuelve la etiqueta correspondiente, sin necesidad de usar INDEX. Para las búsquedas por intervalos, suele ser más fácil de leer que INDEX-MATCH.

Veamos ahora un descuento por volumen. Los umbrales de E (cantidad pedida) son: 0, 10, 50 y 100. Los descuentos de F son: 0 %, 5 %, 10 % y 15 %.

• Pedido de 7: el umbral más grande menor o igual que 7 es 0, ocupa la posición 1 y devuelve 0 %.
• Pedido de 60: el mayor umbral menor o igual que 60 es 50, ocupa la posición 3 y devuelve 10 %.
• Pedido de 200: el mayor umbral menor o igual que 200 es 100, ocupa la posición 4 y devuelve 15 %.

Una sola fórmula gestiona cualquier cantidad.

¿Qué ocurre si un valor es menor que todos los umbrales? Con MATCH aproximado no existe ningún valor menor o igual que él, por lo que MATCH devuelve #N/A.

Para evitarlo, asegúrese de que el primer umbral cubra el límite inferior (a menudo 0) o incluya la fórmula en IFERROR para mostrar un mensaje claro cuando una entrada esté fuera de rango.

Preste atención a estas trampas de las tablas de niveles:

• Umbrales sin ordenar: la principal causa de resultados incorrectos que no generan ningún aviso.
• Usar el tipo de coincidencia 0: obliga a encontrar una coincidencia exacta y devuelve #N/A para cualquier valor intermedio.
• Guardar los extremos de los intervalos en lugar de los inicios: MATCH de tipo 1 espera el límite inferior de cada intervalo, no el superior.
• Umbrales de texto: los números almacenados como texto impiden realizar correctamente la comparación; manténgalos como valores numéricos.

Puede combinar la coincidencia aproximada con la técnica de doble búsqueda. Imagine un coste de envío determinado tanto por el intervalo de peso (filas) como por el intervalo de zona (columnas).

Use un MATCH aproximado (tipo 1) para encontrar la fila del peso y otro para encontrar la columna de la zona; después, introduzca ambos resultados en INDEX. Como ambos ejes contienen umbrales ordenados, cada MATCH se sitúa en el intervalo correcto.

Esto combina INDEX-MATCH-MATCH con la lógica de niveles para crear tablas de tarifas completas.

Compruebe que entiende las búsquedas aproximadas por niveles.

Para las búsquedas por niveles e intervalos:

• Guarde el umbral inferior de cada intervalo y ordénelos de forma ascendente.
• Use MATCH(value, thresholds, 1) para encontrar la posición del intervalo (el valor más grande menor o igual que la entrada).
• Inclúyalo en INDEX(labels, ...) para devolver el intervalo, o use XLOOKUP(..., -1) para obtener el mismo resultado.

Cubra el límite inferior con un umbral de 0 o use IFERROR para las entradas fuera de rango; nunca deje los umbrales sin ordenar.