Filtrar datos por código parcial y crear listas desplegables dependientes

El problema: filtrar por el principio de un código jerárquico

Rosario pone en práctica lo aprendido en la comunidad y se queda atascada en un punto muy concreto. Tiene una tabla con códigos jerárquicos del tipo 1.01, 1.02, 2.01, 2.02... y necesita que, al escribir "1" en una celda, se filtren solo los elementos que empiezan por "1.".

Su primer intento fue con BUSCARX, y ahí está el malentendido: BUSCARX devuelve un resultado, el primero que encuentra. Para obtener todos los que coinciden hace falta otra herramienta.

La comunidad respondió con tres enfoques que van de lo literal a lo ingenioso.

Enfoque 1: FILTRAR con HALLAR en la posición 1

Hugo va directo a la interpretación textual del problema: "que el código empiece por el número escrito".

=FILTRAR(D4:D27; SI.ERROR(HALLAR(H4; C4:C27) = 1; 0); "")

HALLAR devuelve la posición en la que aparece el texto buscado dentro de cada código. Si esa posición es 1, el código empieza por ahí. La comparación genera una matriz de verdaderos y falsos que FILTRAR usa como condición.

El SI.ERROR no es decorativo: cuando HALLAR no encuentra nada devuelve error, y un error dentro de la condición de FILTRAR tumba toda la fórmula. Convirtiéndolo en cero, esas filas simplemente no pasan el filtro.

Funciona, pero tiene un matiz: buscar "1" en la posición 1 también dejaría pasar un código como 1.15 o incluso 10.01, según cómo estén formateados los datos.

Enfoque 2: FILTRAR con ENTERO, mucho más limpio

Bolivia se dio cuenta de algo que cambia el problema por completo: si los códigos son números con decimales (1.01, 2.03), entonces el nivel jerárquico es literalmente su parte entera.

=FILTRAR(D4:D27; ENTERO(C4:C27) = H4; "")

Se acabaron las búsquedas de texto. ENTERO trunca cada código a su parte entera y se compara directamente con el número introducido. Una comparación numérica exacta, sin errores que capturar y sin ambigüedades de posición.

Es un buen recordatorio: antes de atacar un dato con funciones de texto, comprueba si en realidad es un número. Muchos problemas de "empieza por" se convierten en una simple comparación aritmética.

Enfoque 3: BUSCARX con comodín para crear un rango dinámico

Y aquí llega la solución más avanzada, la de John, que resuelve algo que las anteriores no: generar un rango utilizable en una lista de validación de datos.

El problema de fondo es que la validación de datos de Excel necesita un rango, no una matriz derramada. Así que John construye el rango buscando su primer y su último elemento:

=BUSCARX(G4&".**"; C$4:C$27; D$4:D$27;; 2) : BUSCARX(G4&".**"; C$4:C$27; D$4:D$27;; 2; inverso)

Hay tres piezas que hacen que esto funcione:

• El comodín. Concatenando el número con "." se busca "el código que empieza por 1. seguido de cualquier cosa". El quinto argumento con valor 2** activa el modo de coincidencia por comodines.
• El orden de búsqueda inverso. El sexto argumento, que aquí llamamos inverso y en la fórmula real es el valor menos uno, hace que BUSCARX recorra el rango de abajo arriba. Así el segundo BUSCARX devuelve el último elemento que coincide en lugar del primero.
• El operador de rango. Los dos puntos entre ambas fórmulas construyen un rango real que va desde la primera coincidencia hasta la última.

El resultado es un rango dinámico y contiguo. Se guarda como nombre definido y se usa como origen de una lista desplegable: eliges el nivel en la primera celda y la segunda ofrece solo sus elementos. Listas dependientes sin tablas auxiliares ni fórmulas indirectas.

Ojo con la condición que impone este truco: los datos tienen que estar ordenados, porque el rango construido incluye todo lo que hay entre la primera y la última coincidencia.

Funciones clave

• FILTRAR — devuelve todas las filas que cumplen una condición. La respuesta correcta cuando BUSCARX se queda corto.
• HALLAR — localiza la posición de un texto dentro de otro. Comparar el resultado con 1 equivale a "empieza por".
• SI.ERROR — imprescindible al meter HALLAR dentro de una condición de filtro, para que las no coincidencias no rompan la fórmula.
• ENTERO — trunca la parte decimal. En códigos numéricos jerárquicos, devuelve el nivel superior directamente.
• BUSCARX — con modo de coincidencia 2 acepta comodines, y con orden de búsqueda inverso devuelve la última coincidencia en lugar de la primera.

Conclusión

Tres formas de resolver lo mismo, cada una con su terreno. HALLAR es la traducción literal del enunciado. ENTERO es la que aprovecha la naturaleza real del dato y gana en simplicidad. Y el doble BUSCARX es la única que produce un rango de verdad, que es lo que necesita la validación de datos.

Casos como este se plantean a diario en la comunidad de InflueXcel: alguien llega con una fórmula que no hace lo que espera y, en unas horas, tiene tres enfoques distintos con sus ventajas y sus condiciones de uso explicadas.

Más casos con estas funciones

• FILTRAR
• BUSCARX

Más contenido de Excel en InflueXcel

• FILTRAR con ELEGIR y BUSCARX para consultas cruzadas entre tablas Caso
  Interesante problema planteado por Hector sobre cómo construir una consulta dinámica que combine columnas de una tabla con datos cruzados de
• Cómo dejar una celda realmente vacía en fórmulas SI, BUSCARX y FILTRAR Caso
  Interesante problema planteado por un miembro de la comunidad: quiere que sus fórmulas devuelvan una celda realmente vacía cuando no hay res
• Encontrar en qué columna está el máximo: BYCOL + BUSCARX vs TOMAR + SI Caso
  A partir de una tabla de datos donde cada fila tiene valores distribuidos en varias columnas con cabeceras compuestas (ej: "Madrid - Ventas"
• Buscar todos los valores coincidentes cuando BUSCARX solo devuelve uno Caso
  Juan se encuentra con un problema frecuente: tiene una tabla de búsqueda con claves duplicadas (A aparece varias veces con valores distintos
• Categorizar automáticamente conceptos con BUSCARX REGEX y MAP Caso
  Un miembro de la comunidad tiene una lista de compra (conceptos como "leche entera", "pan integral", etc.) y una tabla de categorías donde c
• FILTRAR que devuelve múltiples filas: por qué falla y cómo resolverlo Caso
  Interesante problema que aparece con frecuencia cuando se combinan FILTRAR con funciones iterativas como MAP o BYROW. Un miembro tiene una t
• BUSCARX explicado fácil Tutorial
  Desde la sintaxis básica hasta trucos avanzados como expresiones regulares y cruce de rangos. Además, aprenderás a manejar errores y a usar
• Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa Tutorial
  Te pasan una lista de 30 números de albarán y hay que sacar esas filas de una tabla de miles. Con el autofiltro es marcar casillas una a una
• BUSCARX con valor devuelto dinámico: elige la columna con un botón o un segmentador Caso
  Una integrante de la comunidad planteó un reto muy habitual al trabajar con tablas de varias columnas: tiene una lista de municipios con cua
• ¿Clasificación ABC en PowerQuery? Sí, es posible Tutorial
  ¿PowerQuery puede hacer una clasificación ABC sin SCAN ni BUSCARX? ¡Sí, es posible! En este tutorial te muestro cómo transformar una tabla d