Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa

Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa

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; con el filtro avanzado hay que abrir ventanas y preparar rangos de criterios. Y en ninguno de los dos casos el resultado sirve como entrada de otra fórmula.

Esta LAMBDA lo resuelve en una llamada: le pasas la tabla, en qué columna buscar y el rango con los valores a filtrar.

``
=FILTROCONRANGO(tabla; 1; lista_valores)
`

El montaje se apoya en tres funciones y una idea:

- UNIRCADENAS concatena todos los valores buscados en una sola cadena separada por un delimitador
- HALLAR busca el valor de cada fila dentro de esa cadena; devuelve un número si está y un error si no
- ESNUMERO convierte eso en VERDADERO/FALSO, que es lo que FILTRAR necesita como criterio
- INDICE sobre la tabla permite elegir la columna de búsqueda por número, en vez de fijarla

La inversa sale gratis: cambiando ESNUMERO por ESERROR obtienes el filtro contrario, quedarte con todo menos los valores de la lista. Entre las dos cubren casi cualquier situación de "estos sí" o "estos no".

⚠️ Un cuidado con el delimitador: elige uno que no pueda aparecer dentro de los propios valores, o HALLAR` dará coincidencias falsas.

La situación es cotidiana: alguien te pasa una lista de treinta números de albarán y tienes que sacar esas filas de una tabla de varios miles. El autofiltro te obliga a marcar treinta casillas a mano. El filtro avanzado funciona, pero hay que preparar un rango de criterios y pasar por un par de ventanas. Y ninguno de los dos deja un resultado que puedas usar como entrada de otra fórmula.

Lo que falta es una función. Excel no la trae, así que se construye.

=FILTROCONRANGO(tabla; 1; lista_valores)

Le pasas la tabla que quieres obtener, el número de columna donde está el valor a comparar y el rango con los valores buscados. Devuelve las filas completas, listas para copiar o para anidar en otro cálculo.

El problema de fondo

FILTRAR necesita como segundo argumento una matriz de VERDADERO y FALSO con una entrada por fila. Comparar una columna contra un valor es trivial. Compararla contra una lista no lo es tanto, porque no hay un operador "está contenido en" directo.

La solución del vídeo es un cambio de terreno: en lugar de comparar valor contra lista, se convierte la lista en un texto y se busca dentro.

Paso 1: la lista, en una sola cadena

=UNIRCADENAS(",";VERDADERO;lista_valores)

UNIRCADENAS pega todos los valores buscados separados por un delimitador. El segundo argumento en VERDADERO ignora las celdas vacías, lo que evita que un hueco en la lista genere separadores dobles.

Paso 2: buscar cada fila dentro de esa cadena

=HALLAR(INDICE(tabla;;columna);cadena_de_valores)

HALLAR busca el valor de cada fila dentro del texto gigante. Devuelve la posición si lo encuentra y un error si no, y esa asimetría es justo lo aprovechable.

El INDICE(tabla;;columna) merece atención: al dejar el argumento de fila vacío y pasar solo el de columna, devuelve la columna entera de la tabla. Eso es lo que permite que la función reciba el número de columna como parámetro en vez de tenerlo fijado dentro.

Paso 3: de error a booleano

=ESNUMERO(HALLAR(...))

ESNUMERO traduce "lo he encontrado" a VERDADERO y el error a FALSO. Ya tenemos la matriz que FILTRAR esperaba.

Ensamblado:

=LAMBDA(tabla;columna;filtro;
    FILTRAR(tabla;
        ESNUMERO(HALLAR(INDICE(tabla;;columna);
            UNIRCADENAS(",";VERDADERO;filtro)))
    )
)

La inversa, cambiando una función

Este es el remate, y es lo que convierte el truco en herramienta. La operación complementaria —quedarse con todo menos los valores de la lista— sale sustituyendo ESNUMERO por ESERROR:

ESERROR(HALLAR(...))

Donde antes había coincidencia ahora hay FALSO, y donde había error ahora hay VERDADERO. Entre las dos versiones cubres casi cualquier caso de "quiero estos" o "quiero todo salvo estos", que en la práctica es la mitad del trabajo con listados.

El cuidado que hay que tener

El método se apoya en buscar texto dentro de texto, y eso trae una trampa: HALLAR encuentra coincidencias parciales. Si buscas el albarán 123 y en la cadena está el 1234, lo dará por encontrado.

Dos precauciones:

  • Elegir un delimitador que no pueda aparecer dentro de los valores. Con códigos que llevan comas o guiones, la coma es mala elección; un carácter poco común es más seguro.
  • Si los códigos tienen longitudes distintas y unos son prefijo de otros, conviene rodear cada valor con el delimitador al construir la cadena, de forma que la búsqueda sea de ,123, y no de 123.

HALLAR además no distingue mayúsculas de minúsculas; si eso importa, ENCONTRAR hace lo mismo distinguiéndolas.

Funciones clave

  • UNIRCADENAS — convierte la lista de valores en una sola cadena buscable
  • HALLAR — busca dentro de ella; da número si está y error si no
  • ESNUMERO / ESERROR — el par que da el filtro y su inverso
  • INDICE(tabla;;columna) — devuelve la columna entera, y permite parametrizar cuál
  • FILTRAR — aplica la matriz booleana resultante

Conclusión

El valor de esta función no está en las piezas, que son básicas, sino en el cambio de enfoque: convertir un problema de pertenencia a un conjunto en un problema de búsqueda de texto. Guardada en el Administrador de nombres, deja de ser una fórmula de siete líneas y pasa a ser una llamada de una, que además se puede encadenar dentro de otros cálculos — algo que ni el autofiltro ni el filtro avanzado permiten.

LAMBDA, FILTRAR y UNIRCADENAS requieren Microsoft 365.

Más casos con estas funciones

Más contenido de Excel en InflueXcel