FILTRAR que devuelve múltiples filas: por qué falla y cómo resolverlo

Interesante problema que aparece con frecuencia cuando se combinan FILTRAR con funciones iterativas como MAP o BYROW. Un miembro tiene una tabla de datos (claves en L y valores en M) donde algunas claves se repiten (por ejemplo, "a" aparece dos veces con valores 10 y 20). Al usar FILTRAR dentro de una iteración, las claves repetidas devuelven más de una fila, y Excel lanza el temido error #CALC!.

Nacho explica la causa raíz: FILTRAR puede devolver múltiples filas por cada coincidencia, pero funciones como MAP esperan un único resultado por iteración. Cuando el resultado "no cabe", Excel no sabe cómo encajarlo y devuelve error.

Dos caminos para resolverlo:

1. Reducir a una fila: concatenar los resultados de cada iteración (por ejemplo con UNIRCADENAS) para que FILTRAR siempre devuelva un valor escalar. Útil cuando solo necesitas una representación resumida.

2. Acumular con REDUCE: si necesitas que cada iteración genere tantas filas como encuentre y las vaya apilando, REDUCE permite ir acumulando resultados de tamaño variable con APILARV.

El archivo incluye ambas soluciones trabajadas. La primera usa un enfoque fila por fila con columna auxiliar:

``
=CONTAR.SI($D$4:D4; D4)
`

Este CONTAR.SI con rango expansivo cuenta cuántas veces ha aparecido cada clave hasta la fila actual, generando un índice de ocurrencia (1ª vez, 2ª vez...). Luego ELEGIRFILAS selecciona la fila correcta:

`
=ELEGIRFILAS(
FILTRAR($L$4:$M$12; $L$4:$L$12=D4);
E4
)
`

La segunda solución lo resuelve todo en una única fórmula con REDUCE:

`
=SOLTAR(
REDUCE(
"";
D4:D12;
LAMBDA(a; i;
APILARV(a;
LET(
_id; CONTAR.SI($D$4:i; i);
ELEGIRFILAS(
FILTRAR(L4:M12; L4:L12=i);
_id
)
)
)
)
);
1
)
`

REDUCE itera sobre cada clave, CONTAR.SI con rango expansivo calcula la enésima ocurrencia, ELEGIRFILAS(FILTRAR(...), _id) extrae la fila correcta, y APILARV va acumulando el resultado. SOLTAR(…; 1) elimina la semilla vacía inicial.

Solución elegante con ELEGIRFILAS + MAP — John propone un tercer enfoque que evita tanto la concatenación como REDUCE:

`
=LET(
b; B4:B12;
g; G4:G12;
ELEGIRFILAS(
G4:I12;
COINCIDIRX(b; g) +
MAP(b; LAMBDA(i; SUMA(N(B4:i=i)))) - 1
)
)
`

En vez de filtrar, calcula directamente el número de fila que necesita. COINCIDIRX(b; g) encuentra la primera aparición de cada valor. Luego MAP con SUMA(N(B4:i=i)) cuenta cuántas veces ha aparecido ese mismo valor hasta la fila actual, generando un offset progresivo. Al sumar ambos y restar 1, obtiene el índice exacto de cada fila.

Caso muy didáctico con tres estrategias para superar las limitaciones de FILTRAR en contextos iterativos: columna auxiliar con CONTAR.SI, fórmula única con REDUCE, o indexación directa con ELEGIRFILAS+MAP`.

El error #CALC! que aparece al mezclar FILTRAR con MAP

Es uno de los tropiezos más frecuentes cuando empiezas a combinar funciones iterativas con FILTRAR. Un miembro de la comunidad tiene una tabla con claves en una columna y valores en otra, y algunas claves se repiten: por ejemplo, "a" aparece dos veces, con valores 10 y 20.

Al usar FILTRAR dentro de una iteración, esas claves repetidas devuelven más de una fila, y Excel lanza el temido #CALC!.

Nacho explicó la causa raíz, que es más simple de lo que parece: FILTRAR puede devolver múltiples filas por cada coincidencia, pero funciones como MAP esperan un único resultado por iteración. Cuando el resultado no cabe en la celda que le corresponde, Excel no sabe cómo encajarlo y devuelve error.

A partir de ahí hay dos caminos, y la comunidad recorrió los dos.

Camino 1: reducir cada iteración a una sola fila

Si con un resumen te vale, basta con colapsar los resultados de cada iteración a un valor escalar. Concatenar con UNIRCADENAS es la vía más directa: FILTRAR puede devolver tres filas, pero al unirlas en un texto vuelve a ser un único valor y MAP queda contento.

Es la solución rápida, útil cuando lo que buscas es una representación resumida y no los datos por separado.

Camino 2: acumular filas de tamaño variable con REDUCE

Si de verdad necesitas que cada iteración genere tantas filas como encuentre, MAP no sirve. La herramienta correcta es REDUCE, que sí permite ir acumulando resultados de tamaño variable con APILARV.

Primero, la versión con columna auxiliar

El fichero de la comunidad incluye una solución intermedia muy didáctica, que resuelve el problema fila a fila. La clave es un CONTAR.SI con rango expansivo:

=CONTAR.SI($D$4:D4; D4)

Fíjate en el anclaje: el inicio del rango está fijado con dólares y el final no. Al arrastrar hacia abajo, el rango crece, y el resultado es el número de veces que ha aparecido cada clave hasta la fila actual. Es decir, un índice de ocurrencia: 1 la primera vez, 2 la segunda, 3 la tercera.

Con ese índice, ELEGIRFILAS selecciona la fila correcta del resultado de FILTRAR:

=ELEGIRFILAS(
    FILTRAR($L$4:$M$12; $L$4:$L$12=D4);
    E4
)

FILTRAR sigue devolviendo varias filas, pero ya no importa: nos quedamos exactamente con la que toca.

Y ahora, todo en una sola fórmula

La misma lógica, sin columnas auxiliares, encapsulada en un REDUCE:

=SOLTAR(
    REDUCE(
        "";
        D4:D12;
        LAMBDA(a; i;
            APILARV(a;
                LET(
                    _id; CONTAR.SI($D$4:i; i);
                    ELEGIRFILAS(
                        FILTRAR(L4:M12; L4:L12=i);
                        _id
                    )
                )
            )
        )
    );
    1
)

Merece la pena desglosarlo:

  • REDUCE itera sobre cada clave arrastrando un acumulador.
  • CONTAR.SI($D$4:i; i) reproduce el rango expansivo dentro de la fórmula: el rango va desde la primera celda hasta la celda que se está procesando.
  • ELEGIRFILAS(FILTRAR(...); _id) extrae la enésima coincidencia.
  • APILARV va pegando cada resultado debajo del anterior.
  • SOLTAR con el argumento 1 elimina la fila de la semilla vacía inicial.

Camino 3: no filtrar, calcular la fila directamente

John propuso un tercer enfoque que esquiva por completo el problema. En lugar de filtrar y quedarse con una fila, calcula directamente el número de fila que necesita:

=LET(
    b; B4:B12;
    g; G4:G12;
    ELEGIRFILAS(
        G4:I12;
        COINCIDIRX(b; g) +
            MAP(b; LAMBDA(i; SUMA(N(B4:i=i)))) - 1
    )
)

La aritmética es bonita. COINCIDIRX(b; g) localiza la primera aparición de cada valor. Después, MAP con SUMA(N(B4:i=i)) cuenta cuántas veces ha aparecido ese mismo valor hasta la fila actual, generando un desplazamiento progresivo: 1 para la primera repetición, 2 para la segunda. Al sumar ambos y restar uno, se obtiene el índice exacto.

Sin FILTRAR y sin REDUCE: solo indexación directa. Es la más rápida de las tres, porque no construye matrices intermedias.

Funciones clave

  • FILTRAR — devuelve todas las filas que cumplen la condición. Su naturaleza de tamaño variable es justo lo que choca con MAP.
  • MAP — aplica una LAMBDA a cada elemento y espera un resultado escalar por elemento. Ideal para transformaciones uno a uno.
  • REDUCE — acumula. La única de las iterativas que admite resultados de tamaño variable, gracias a que el acumulador puede crecer.
  • ELEGIRFILAS — extrae filas por posición. La pieza que convierte "varias coincidencias" en "la coincidencia que quiero".
  • CONTAR.SI con rango expansivo — el clásico contador de ocurrencias acumuladas.
  • COINCIDIRX — devuelve la posición de la primera coincidencia.

Conclusión

El #CALC! no es un bug: es Excel diciéndote que la función que has elegido no admite resultados de tamaño variable. La solución pasa por decidir qué quieres realmente.

Si te vale un resumen, colapsa a un escalar. Si necesitas todas las filas, cambia MAP por REDUCE. Y si puedes calcular la posición en vez de filtrar, hazlo: suele ser el camino más eficiente. Tres estrategias que aparecen una y otra vez en los casos de la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel