Obtener los números de fila de todas las coincidencias, no solo la primera

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 las apariciones, no solo la primera.

Con COINCIDIRX solo obtiene la primera coincidencia:

``
=COINCIDIRX(F6; C9:C11)
`

Esto devuelve 1 (la primera fila donde aparece "juan"), pero "juan" también está en la fila 3 y COINCIDIRX no lo devuelve.

Solución 1: FILTRAR + SECUENCIA (Nacho)

La idea es generar una secuencia de números 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 {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. Funciona en cualquier versión con funciones dinámicas.

Solución 2: AGRUPARPOR + MATRIZATEXTO (Miki)

Un enfoque diferente usando AGRUPARPOR para agrupar por fila y MATRIZATEXTO para concatenar:

`
=LET(
a; AGRUPARPOR(C9:C11; SECUENCIA(FILAS(C9:C11)); MATRIZATEXTO;; 0;; C9:C11=F6);
ENCOL(DIVIDIRTEXTO(TEXTODESPUES("; " & ELEGIRCOLS(a; -1); "; "; SECUENCIA(; 4)); "; "); 2)
)
`

Más elaborada, pero demuestra el poder de combinar AGRUPARPOR con MATRIZATEXTO para análisis de datos.

Solución 3: FILTRAR + SECUENCIA sin AGRUPARPOR (alternativa)

Para quienes no disponen de AGRUPARPOR:

`
=FILTRAR(
--(C9:C11=F6) SECUENCIA(CONTARA(C9:C11); 1);
--(C9:C11=F6) SECUENCIA(CONTARA(C9:C11); 1) <> 0
)
`

Multiplica la condición (0 o 1) por la secuencia para obtener 0 donde no coincide y el número de fila donde sí, y luego filtra los ceros.

Truco clave

FILTRAR(SECUENCIA(FILAS(rango)); rango=valor) es un patrón muy útil para obtener las posiciones de todas las coincidencias dentro de un rango. Funciona como un COINCIDIRX` que devuelve todas las filas, no solo la primera.

El problema: todas las coincidencias, no solo la primera

Juan tiene una base de datos en un rango y quiere buscar un valor (por ejemplo, "juan") para obtener los números de fila de todas sus apariciones, no solo la primera. El obstáculo es conocido: COINCIDIRX devuelve únicamente la primera coincidencia.

`` =COINCIDIRX(F6; C9:C11) ``

Si "juan" está en la fila 1 y también en la fila 3, esta fórmula devuelve solo el 1. La comunidad propuso tres formas de obtener todas las posiciones, y de paso un patrón que conviene tener siempre a mano.

Solución 1: FILTRAR + SECUENCIA (Nacho)

La idea es generar una secuencia de números 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 funciones dinámicas.

Solución 2: AGRUPARPOR + MATRIZATEXTO (Miki)

Un enfoque distinto que agrupa por fila 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; -1); "; "; SECUENCIA(; 4)); "; "); 2) ) ``

Es más elaborada, pero demuestra la potencia de combinar AGRUPARPOR con MATRIZATEXTO para preparar y desmenuzar resultados de análisis.

Solución 3: multiplicar la condición por la secuencia

Para quien no disponga de AGRUPARPOR, existe una alternativa clásica. La comparación C9:C11=F6 devuelve VERDADERO o FALSO; al multiplicarla por 1 (o aplicarle la función N) se convierte en 1 y 0. Multiplicando ese vector por una secuencia obtenemos el número de fila donde coincide y un cero donde no:

`` =FILTRAR( N(C9:C11=F6) * SECUENCIA(CONTARA(C9:C11); 1); N(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 automáticamente 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. Guárdalo, porque lo vas a usar más de lo que crees: contar duplicados, localizar registros repetidos, construir listas de posiciones para otras fórmulas...

Funciones clave

  • SECUENCIA: genera la lista de números de fila sobre la que trabajar.
  • FILTRAR: se queda con los elementos que cumplen la condición; su tercer argumento evita el error cuando no hay coincidencias.
  • FILAS: cuenta cuántas filas tiene el rango para dimensionar la secuencia.
  • AGRUPARPOR + MATRIZATEXTO: agrupan y concatenan resultados para un análisis más rico.
  • N: convierte VERDADERO/FALSO en 1/0 para poder operar con la condición.

Conclusión

De la limitación de COINCIDIRX a tres soluciones que devuelven todas las coincidencias: la más recomendable es FILTRAR + SECUENCIA por su claridad, mientras que las otras dos enseñan técnicas (AGRUPARPOR, la aritmética de booleanos) que suman a tu caja de herramientas. Un problema pequeño con una lección grande, del tipo que se comparte cada semana en la comunidad de Influexcel.

Más contenido de Excel en InflueXcel