Buscar todos los valores coincidentes cuando BUSCARX solo devuelve uno
Juan se encuentra con un problema frecuente: tiene una tabla de búsqueda con claves duplicadas (la A aparece varias veces con valores distintos) y BUSCARX solo le devuelve la primera coincidencia. Además, al intentar forzar varios resultados, se encuentra con errores #CALC! (de desbordamiento) que no puede seleccionar fácilmente para borrarlos.
La comunidad propone varios enfoques para resolver los dos problemas:
MAP + FILTRAR + MATRIZATEXTO (Gerson): para cada clave, filtra todos los valores coincidentes y los junta en una sola celda con MATRIZATEXTO:
``
=MAP(E4:E7; LAMBDA(x; MATRIZATEXTO(FILTRAR(B14:B17; x = A14:A17; "No encontrado"))))
`
Así cada celda contiene todos los resultados separados por coma, sin riesgo de desbordamiento.
UNIRCADENAS para evitar el #CALC! (Miki): cuando la fórmula devuelve varios resultados pero la celda no tiene espacio para desbordarse, UNIRCADENAS es otra forma de condensarlo todo en una celda:
`
=UNIRCADENAS(", "; 1; FILTRAR(B14:B17; A14:A17 = E4))
`
ENCOL con argumento 2 o 3 para limpiar errores (Leo): si ya tienes una matriz con errores #CALC! mezclados con valores válidos, ENCOL puede quitarlos:
`
=ENCOL(rango_con_errores; 2)
`
El segundo argumento de ENCOL controla qué ignorar: 1 = vacíos, 2 = errores, 3 = ambos.
Un buen recordatorio de que BUSCARX está diseñado para devolver un único resultado, y de que para búsquedas con varias coincidencias la combinación FILTRAR + MAP + LAMBDA es la herramienta adecuada.
Los números de fila de todas las coincidencias, no solo la primera
La misma limitación aparece cuando lo que se busca no es el valor sino la posición. En otro hilo, Juan tiene una base de datos en un rango (C9:C11) y quiere buscar un valor (por ejemplo, «juan») para obtener los números de fila de todas sus apariciones. Con COINCIDIRX solo obtiene la primera:
`
=COINCIDIRX(F6; C9:C11)
`
Devuelve 1 (la primera fila donde aparece «juan»), pero «juan» también está en la fila 3 y COINCIDIRX no lo devuelve. El fichero descargable trae este planteamiento con la solución esperada (1 y 3).
FILTRAR + SECUENCIA (Nacho): genera los números del 1 al total de filas con SECUENCIA y filtra los de las filas que coinciden:
`
=FILTRAR(SECUENCIA(FILAS(C9:C11)); C9:C11=F6; "Nada")
`
SECUENCIA(FILAS(C9:C11)) genera {1;2;3}. C9:C11=F6 genera {VERDADERO;FALSO;VERDADERO}. FILTRAR devuelve solo los números donde hay coincidencia: {1;3}. Fórmula limpia y directa, que funciona en cualquier versión con matrices dinámicas.
AGRUPARPOR + MATRIZATEXTO (Miki): un enfoque distinto, que agrupa con AGRUPARPOR y concatena con MATRIZATEXTO:
`
=LET(
a; AGRUPARPOR(C9:C11; SECUENCIA(FILAS(C9:C11)); MATRIZATEXTO;; 0;; C9:C11=F6);
ENCOL(DIVIDIRTEXTO(TEXTODESPUES("; " & ELEGIRCOLS(a; COLUMNAS(a)); "; "; SECUENCIA(; 4)); "; "); 2)
)
`
Más elaborada, pero muestra la potencia de combinar AGRUPARPOR con MATRIZATEXTO.
Sin AGRUPARPOR: multiplica la condición por la secuencia (al multiplicar, VERDADERO y FALSO pasan a ser 1 y 0), de modo que queda el número de fila donde coincide y un 0 donde no, y luego filtra: FILTRAR toma como verdadero cualquier valor distinto de cero, así que los ceros desaparecen.
`
=FILTRAR(
(C9:C11=F6) SECUENCIA(CONTARA(C9:C11); 1);
(C9:C11=F6) SECUENCIA(CONTARA(C9:C11); 1)
)
`
El truco clave: FILTRAR(SECUENCIA(FILAS(rango)); rango=valor) funciona como un COINCIDIRX` que devuelve todas las filas, no solo la primera.
El problema: BUSCARX y COINCIDIRX se paran en la primera coincidencia
BUSCARX es la función de búsqueda por excelencia del Excel moderno, pero tiene una limitación de diseño: devuelve un único resultado. Si tu tabla tiene la misma clave repetida varias veces con valores distintos, BUSCARX se queda con la primera y descarta el resto. A COINCIDIRX le pasa lo mismo con las posiciones: te dice en qué fila está la primera aparición y se para ahí.
En la comunidad salieron las dos caras del problema. Juan necesitaba todos los valores asociados a una clave repetida, y al intentar forzar varios resultados aparecían errores #CALC! de desbordamiento que además costaba seleccionar para limpiarlos. En otro hilo, la pregunta era la otra: los números de fila de todas las apariciones de un valor. Esta página reúne las soluciones de los dos.
Todos los valores, enfoque 1: MAP + FILTRAR + MATRIZATEXTO (Gerson)
La idea es recorrer cada clave de búsqueda y, para cada una, filtrar todas sus coincidencias y condensarlas en una sola celda:
=MAP(E4:E7; LAMBDA(x; MATRIZATEXTO(FILTRAR(B14:B17; x = A14:A17; "No encontrado"))))FILTRAR devuelve todos los valores cuya clave coincide con x, y MATRIZATEXTO los une en un texto separado por comas. Como cada celda recibe un texto y no una matriz, no hay riesgo de desbordamiento: cada fila del resultado contiene todas sus coincidencias juntas.
Todos los valores, enfoque 2: UNIRCADENAS para esquivar el #CALC! (Miki)
Cuando la fórmula devuelve varios resultados pero la celda no tiene sitio para desbordarse, UNIRCADENAS es otra forma limpia de agruparlo todo:
=UNIRCADENAS(", "; 1; FILTRAR(B14:B17; A14:A17 = E4))El primer argumento es el separador, el segundo indica que se ignoren los vacíos y el tercero es el FILTRAR con todas las coincidencias. Resultado: una celda con todos los valores separados por coma, sin errores de espacio.
Todos los valores, enfoque 3: ENCOL para limpiar errores ya existentes (Leo)
Si ya tienes una matriz donde conviven valores válidos y errores #CALC!, ENCOL puede depurarla:
=ENCOL(rango_con_errores; 2)El segundo argumento de ENCOL controla qué descartar: 1 ignora los vacíos, 2 ignora los errores y 3 ignora ambos. Con 2 te quedas solo con los valores buenos, apilados en una columna limpia.
Los números de fila de todas las coincidencias, no solo la primera
Ahora la otra cara. Juan tiene una base de datos en C9:C11 y quiere buscar un valor (por ejemplo, «juan») para obtener los números de fila de todas sus apariciones. Con COINCIDIRX no hay manera:
=COINCIDIRX(F6; C9:C11)Si «juan» está en la fila 1 y también en la 3, esta fórmula devuelve solo el 1. El fichero descargable de este caso trae justo ese planteamiento, con la solución esperada (1 y 3). La comunidad propuso tres formas de obtener todas las posiciones.
Números de fila con FILTRAR + SECUENCIA (Nacho)
La idea es generar una secuencia del 1 al total de filas con SECUENCIA y luego filtrar esos números por las filas que coinciden:
=FILTRAR(SECUENCIA(FILAS(C9:C11)); C9:C11=F6; "Nada")SECUENCIA(FILAS(C9:C11)) genera la lista {1;2;3}. La comparación C9:C11=F6 genera {VERDADERO;FALSO;VERDADERO}. FILTRAR devuelve solo los números donde hay coincidencia: {1;3}. Fórmula limpia, directa y compatible con cualquier versión que tenga matrices dinámicas. Fíjate en que es el mismo FILTRAR del enfoque 1, solo que filtra posiciones en vez de valores.
Números de fila con AGRUPARPOR + MATRIZATEXTO (Miki)
Un enfoque distinto, que agrupa con AGRUPARPOR y concatena con MATRIZATEXTO:
=LET(
a; AGRUPARPOR(C9:C11; SECUENCIA(FILAS(C9:C11)); MATRIZATEXTO;; 0;; C9:C11=F6);
ENCOL(DIVIDIRTEXTO(TEXTODESPUES("; " & ELEGIRCOLS(a; COLUMNAS(a)); "; "; SECUENCIA(; 4)); "; "); 2)
)ELEGIRCOLS(a; COLUMNAS(a)) se queda con la última columna del agrupamiento, la del texto con las posiciones, y el resto de la fórmula lo vuelve a trocear en una columna. Es más elaborada, pero demuestra la potencia de combinar AGRUPARPOR con MATRIZATEXTO para preparar y desmenuzar resultados. Y vuelve a salir ENCOL con el 2, el mismo truco de Leo para descartar errores.
Números de fila sin AGRUPARPOR: multiplicar la condición por la secuencia
Para quien no disponga de AGRUPARPOR, hay una alternativa clásica. La comparación C9:C11=F6 devuelve VERDADERO o FALSO, y al multiplicarla se convierte en 1 y 0. Multiplicada por una secuencia, da el número de fila donde coincide y un cero donde no:
=FILTRAR(
(C9:C11=F6) * SECUENCIA(CONTARA(C9:C11); 1);
(C9:C11=F6) * SECUENCIA(CONTARA(C9:C11); 1)
)Como las filas sin coincidencia quedan en cero, el segundo argumento de FILTRAR (que interpreta cualquier valor distinto de cero como verdadero) descarta esos ceros y deja solo las posiciones válidas.
El truco clave
El patrón FILTRAR(SECUENCIA(FILAS(rango)); rango=valor) es uno de esos comodines que resuelven mil situaciones. Funciona como un COINCIDIRX que, en vez de la primera coincidencia, te devuelve todas las filas donde aparece el valor. Y si lo que quieres son los valores, cambia la secuencia por la columna de resultados: es el FILTRAR de los enfoques de arriba.
Funciones clave
FILTRAR: el motor real de las dos soluciones; devuelve todo lo que cumple la condición, no solo lo primero, y su tercer argumento evita el error cuando no hay coincidencias.MAP: aplica la lógica de búsqueda a cada clave de una lista de una sola pasada.MATRIZATEXTO/UNIRCADENAS: condensan varios resultados en una única celda, evitando el desbordamiento.ENCOL: apila y, de paso, descarta vacíos o errores según su segundo argumento.SECUENCIAyFILAS: generan la lista de números de fila sobre la que filtrar.AGRUPARPOR: agrupa y concatena resultados para un análisis más rico.
Conclusión
BUSCARX y COINCIDIRX están pensados para devolver un resultado, y empeñarse en exprimirlos para varios lleva a errores de desbordamiento. Cuando quieres todas las coincidencias, sean valores o posiciones, el motor es FILTRAR: con MAP y LAMBDA para recorrer varias claves, con MATRIZATEXTO o UNIRCADENAS para meterlo en una celda y con SECUENCIA para sacar los números de fila. Seis soluciones para dos caras del mismo problema, el pan de cada día en la comunidad de InflueXcel.
Más casos con estas funciones
Más contenido de Excel en InflueXcel
- Categorizar automáticamente conceptos con BUSCARX REGEX y MAP CasoUn miembro de la comunidad tiene una lista de compra (conceptos como "leche entera", "pan integral", etc.) y una tabla de categorías donde c
- Readmisión de pacientes en 48h: Power Query, LAMBDA/MAP y AGRUPARPOR CasoAndrés Rojas plantea un reto real de datos clínicos: a partir de una tabla con más de un millón de registros de urgencias (IdPaciente, Fecha
- Buscar prefijos de longitud variable en otra columna: BYROW, MAP, REGEX y COINCIDIRX CasoInteresante problema planteado por un miembro: tiene una columna A con ~1.200 referencias de longitud variable y una columna C con ~276 text
- Reto de agrupación y pivotado: AGRUPARPOR vs ARCHIVOMAKEARRAY vs MAP CasoLeandro trae un reto de internet al grupo: a partir de una tabla con claves repetidas y valores, generar una tabla pivotada donde cada clave
- Filtro multicriteria dinámico con LET, FILTRAR y LAMBDA CasoHéctor comparte con la comunidad una fórmula avanzada para filtrar una tabla de productos/servicios por múltiples criterios opcionales (clav
- Reestructurar datos apilados con LAMBDA y PIVOTARPOR CasoUn miembro de la comunidad comparte un archivo de revisión de Seguridad Social con una tabla amplia (34 columnas x 1540 filas) en formato "a
- Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa TutorialTe 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
- Funciones personalizadas en Excel: LET, LAMBDA y recursividad TutorialCómo pasar de una fórmula escrita a mano a una función propia que puedes llamar por su nombre en cualquier libro. Los tres vídeos de esta pá
- Crea gráficas interactivas en Excel: Crossfiltering TutorialCrea gráficos interactivos en Excel utilizando el poder de las funciones dinámicas para modificar los datos de origen y generar efectos avan
- ¿Por qué nadie te contó esto? Funciones CUBO en Excel Tutorial¿Sabías que puedes acceder a los datos del modelo en Excel sin usar tablas dinámicas? En este tutorial aprenderás cómo usar las funciones CU