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.
  • SECUENCIA y FILAS: 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