Buscar múltiples palabras simultáneamente con FILTRAR y BYROW

El problema: la búsqueda funciona con una palabra y revienta con dos

Juan llega con una fórmula que ya le funciona. Filtra una tabla quedándose con las filas cuya descripción contiene una palabra concreta:

=FILTRAR(E12:G15; ESNUMERO(HALLAR(K16; G12:G15)))

La lógica es la clásica: HALLAR devuelve la posición del texto buscado si lo encuentra, o un error si no. ESNUMERO convierte eso en VERDADERO o FALSO, y FILTRAR se queda con las filas buenas.

El problema aparece cuando quiere buscar dos palabras a la vez. Cambia el criterio de una celda a un rango de dos, y la fórmula devuelve un error de valor.

Por qué falla: el resultado deja de ser un vector

La causa es interesante y explica muchos otros errores parecidos.

Al pasarle un rango horizontal de dos criterios y un rango vertical de cuatro descripciones, HALLAR no devuelve cuatro respuestas: devuelve una matriz de dos dimensiones, de cuatro filas por dos columnas. Cada celda dice si esa palabra concreta está en esa descripción concreta.

Y FILTRAR no sabe qué hacer con eso. Su segundo argumento tiene que ser un vector con una respuesta por fila, no una tabla. De ahí el error.

Dicho de otra forma: la fórmula ya ha calculado todo lo que hacía falta, pero le sobra una dimensión. Lo que falta es colapsar cada fila en un único valor.

La solución elegante: BYROW con CONTAR

John da con la respuesta más limpia del hilo:

=FILTRAR(E12:G15; BYROW(HALLAR(K16:L16; G12:G15); CONTAR))

BYROW recorre la matriz fila a fila y aplica la función que le indiques a cada una. Aquí aplica CONTAR, que cuenta cuántos números hay ignorando los errores.

El resultado por fila es directamente lo que queríamos: 0 si ninguna de las palabras aparece, 1 si aparece una, 2 si aparecen las dos. Y como FILTRAR interpreta cualquier valor distinto de cero como VERDADERO, se queda con las filas que contienen al menos una de las palabras.

Fíjate en lo que no hay en esta fórmula: no hay ESNUMERO, no hay SI.ERROR, no hay una función de agregación envolviendo a otra. CONTAR ya ignora los errores por diseño, y eso es justo lo que hacía falta. Hugo lo resume en el hilo con un "espectacular el concepto detrás", y tiene razón.

Cambiar el O por un Y

Como el resultado de BYROW es un número, pasar de "alguna de las palabras" a "todas las palabras" es trivial: en vez de aceptar cualquier valor distinto de cero, exige que sea igual al número de criterios.

Una fila que contiene las dos palabras da 2; una que solo contiene una da 1. Comparar ese resultado con el total de criterios te da el Y lógico sin tocar el resto de la fórmula. Es una de esas ventajas que aparecen solas cuando eliges una agregación que devuelve un recuento en lugar de un simple verdadero o falso.

El enfoque alternativo de Hugo

Hugo propone otro camino, con los criterios en una columna en lugar de una fila:

=FILTRAR(E12:G15;
    BYROW(N(NO(ESERROR(HALLAR(ENFILA(H23:H24); G12:G15; 1)))); SUMA)
)

Aquí ENFILA aplana los criterios verticales a horizontal, que es la orientación que necesita la comparación cruzada. Después ESERROR y NO convierten los aciertos en VERDADERO, N los pasa a unos y ceros, y BYROW con SUMA los agrega por fila.

Llega al mismo sitio dando más pasos, pero tiene una virtud didáctica: deja explícita cada transformación. Si estás aprendiendo cómo se construyen estas fórmulas, esta versión enseña más que la corta.

El tercer argumento de HALLAR, ese 1 del final, es la posición desde la que empezar a buscar. Es su valor por defecto, así que se puede omitir.

Un año después: las palabras en columna y el #¡CALC! de MAP

En septiembre de 2026 Juan vuelve al grupo con la misma necesidad, esta vez con las palabras en una columna, y prueba otra vía:

=MAP(M1:M2;LAMBDA(x;HALLAR(x;F2:F409)))

Devuelve #¡CALC!. Nacho le explica el motivo: MAP recorre cada palabra, pero en cada vuelta HALLAR compara esa palabra contra todos los textos y devuelve una matriz entera. MAP solo acepta un único valor por elemento, y Excel no admite matrices dentro de matrices.

Rommel da la vuelta al planteamiento: que MAP recorra los textos y no las palabras. Así, en cada vuelta se pregunta si ese texto contiene alguna palabra, y la respuesta sí es un solo valor:

=FILTRAR(F2:F409;MAP(F2:F409;LAMBDA(x;SUMA(--ESNUMERO(HALLAR(M1:M2;x)))>0));"No encontrado")

Tiene además un detalle práctico: el tercer argumento de FILTRAR devuelve un mensaje cuando no hay coincidencias, en lugar de #¡CALC!.

John recupera su fórmula de hace un año con un solo cambio, girar las palabras a fila con ENFILA:

=FILTRAR(F2:F409;BYROW(HALLAR(ENFILA(M1:M2);F2:F409);CONTAR))

Ese giro es imprescindible, y la trampa es que la alternativa equivocada no avisa. Con ENCOL la fórmula no da error: como las dos matrices tienen una sola columna, HALLAR las empareja fila a fila (la primera palabra con el primer texto, la segunda con el segundo) y el resto sale #N/D. Comprobado en Excel: con doce textos, nueve de ellos con alguna de las palabras, la versión con ENCOL devolvió uno.

Coincidencia exacta y celdas vacías

Si lo que se busca es que el texto sea igual a una de las palabras, no que la contenga, el hilo trae dos opciones. La de Nacho gira la lista, la compara contra la columna y suma por filas:

=FILTRAR(F2:F409;BYROW(--(TRANSPONER(M1:M2)=F2:F409);SUMA))

La de John, más corta, da el mismo resultado:

=FILTRAR(F2:F409;CONTAR.SI(M1:M2;F2:F409))

Un aviso que conviene conocer: si la lista de palabras incluye una celda vacía, las fórmulas de «contiene» dejan pasar todas las filas, porque HALLAR con un texto vacío devuelve 1. ENFILA(M1:M3;1) descarta las vacías. A las de coincidencia exacta no les afecta.

La versión reutilizable: FILTRARCONTIENE

Todo lo anterior está empaquetado en FILTRARCONTIENE, una LAMBDA de la biblioteca del complemento de InflueXcel que sale de este hilo:

=FILTRARCONTIENE(tabla;columna;palabras;[todas])

Filtra la tabla por las filas cuyo texto, en la columna indicada, contiene alguna de las palabras, o todas si el cuarto argumento es VERDADERO. Acepta las palabras en fila o en columna, ignora las vacías y devuelve un mensaje si no encuentra nada.

Funciones clave

• HALLAR: devuelve la posición del texto buscado, o un error si no aparece. Con un rango de criterios genera una matriz de dos dimensiones, y con un texto vacío devuelve 1.
• BYROW: colapsa una matriz fila a fila aplicando una función de agregación. Es la pieza que resuelve el caso.
• CONTAR: cuenta números ignorando errores, lo que la convierte en la agregación ideal aquí.
• ENFILA: aplana una matriz a horizontal. Útil para orientar los criterios y provocar la comparación cruzada; con 1 como segundo argumento, ignora las celdas vacías.
• MAP: aplica una LAMBDA a cada elemento y espera un único valor por vuelta; si recibe una matriz, devuelve #¡CALC!.
• CONTAR.SI: con la lista de palabras como rango y los textos como criterio, resuelve la coincidencia exacta en una sola función.
• N: convierte valores lógicos en unos y ceros cuando quieres sumarlos.
• FILTRAR: necesita un vector con una respuesta por fila; ese requisito es el origen del error inicial. Su tercer argumento evita el #¡CALC! cuando no hay resultados.

Conclusión

La lección que se lleva uno de este caso va mucho más allá de buscar dos palabras: cuando una comparación cruza un rango vertical con uno horizontal, el resultado tiene dos dimensiones, y casi siempre habrá que colapsarlo con BYROW o BYCOL antes de usarlo.

El hilo de 2026 añade la otra cara del mismo patrón: MAP también necesita un único valor por vuelta, y girar la lista con la función equivocada no da error, da un resultado incompleto.

Ese es el patrón. Reconocerlo convierte un error de valor incomprensible en un problema con solución inmediata, y aparece constantemente en los casos de la comunidad de InflueXcel.

Más casos con estas funciones

• LAMBDA
• FILTRAR
• BYROW
• TRANSPONER

Más contenido de Excel en InflueXcel

• Filtrar con múltiples condiciones usando matrices booleanas y BYROW Caso
  Interesante problema planteado por un miembro sobre cómo filtrar datos que cumplan varias condiciones simultáneas usando operaciones matrici
• Buscar prefijos de longitud variable en otra columna: BYROW, MAP, REGEX y COINCIDIRX Caso
  Interesante problema planteado por un miembro: tiene una columna A con ~1.200 referencias de longitud variable y una columna C con ~276 text
• Filtro multicriteria dinámico con LET, FILTRAR y LAMBDA Caso
  Héctor comparte con la comunidad una fórmula avanzada para filtrar una tabla de productos/servicios por múltiples criterios opcionales (clav
• Repetir un rango N veces según marca con MAP y CONTAR.SI Caso
  Un usuario necesita repetir un listado de locales (D2:D10) un número variable de veces para cada marca de la columna B. El número de repetic
• Eliminar filas en blanco de una tabla con FILTRAR y BYROW Caso
  Óscar plantea una duda práctica: tiene una tabla en Excel con filas que contienen información y otras vacías, y necesita extraer solo las fi
• 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
• ¿MAP o BYROW? Descubre cuándo usar cada una Tutorial
  ¿MAP o BYROW? Descubre cuándo usar cada una y cómo estas funciones pueden transformar tu forma de trabajar en Excel. En este vídeo aprenderá
• 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
• VALORCUBO devuelve vacío en vez de 0: cómo forzar el resultado numérico Caso
  Un miembro trabaja con funciones CUBO en Excel y se encuentra con un problema molesto: cuando VALORCUBO no encuentra datos que coincidan con
• Obtener la última tarifa (valor más a la derecha) con 5 fórmulas distintas Caso
  Interesante problema planteado por un miembro de la comunidad que trabaja con una tabla de artículos textiles (manteles, delantales, etc.) c