Fórmulas de Excel para conciliar pagos

Abajo van con ejemplos que puedes copiar. Todas están escritas con los nombres en español y separador de punto y coma.

Supuestos de los ejemplos

Para que las fórmulas tengan sentido, imagina este arreglo. Cámbialo por tus columnas:

Hoja	Columnas
Banco	A fecha, B monto, C ordenante, D referencia, E clave de rastreo
Clientes	A referencia, B nombre, C cuota esperada
Cruce	A nombre del cliente, B monto pagado, C diferencia

BUSCARX: encontrar al cliente de cada depósito

=BUSCARX(Banco!D2;Clientes!A:A;Clientes!B:B;"sin cruce")

Lee así: busca la referencia del depósito en la columna de referencias y devuelve el nombre. El cuarto argumento es lo que muestra si no encuentra nada. Si tu referencia a veces trae espacios, límpiala antes con =ESPACIOS(Banco!D2).

Para versiones sin BUSCARX:

=SI.ERROR(INDICE(Clientes!B:B;COINCIDIR(Banco!D2;Clientes!A:A;0));"sin cruce")

SUMAR.SI.CONJUNTO: cuánto pagó cada quien

Para sumar lo que un cliente pagó en un mes, con nombre del cruce en la columna F de «Banco» y fecha en A:

=SUMAR.SI.CONJUNTO(Banco!B:B;Banco!F:F;A2;Banco!A:A;">="&$H$1;Banco!A:A;"<="&$H$2)

Pon la fecha de inicio en H1 y la de fin en H2. Con eso cambias de mes sin tocar la fórmula. Es la más útil de la lista cuando un cliente paga en dos o tres transferencias.

CONTAR.SI y CONTAR.SI.CONJUNTO: repetidos y ambigüedades

Para marcar claves de rastreo repetidas:

=CONTAR.SI(Banco!E:E;Banco!E2)

Si da 2 o más, hay duplicado. Para saber si un monto identifica a un solo cliente:

=CONTAR.SI(Clientes!C:C;Banco!B2)

Con resultado 1, el monto es único y puedes proponer cruce por monto; con 2 o más es ambiguo y se va a revisión manual.

SI.ERROR y ESNUMERO: que los errores no escondan problemas

SI.ERROR limpia el feo #N/D, pero cuidado: si lo usas para todo, vas a esconder errores reales. Úsalo solo en las fórmulas de búsqueda, con un texto claro como «sin cruce». Para detectar montos que Excel lee como texto:

=SI(ESNUMERO(Banco!B2);"ok";"revisar")

Diferencia contra lo esperado

Con el pagado ya sumado en B y la cuota en Clientes!C:

=REDONDEAR(B2-BUSCARX(A2;Clientes!B:B;Clientes!C:C;0);2)

Resultado cero, pagó exacto. Negativo, debe. Positivo, saldo a favor. Redondear a dos decimales evita diferencias de centavos por aritmética de coma flotante.

Fórmulas nuevas de arreglos: FILTRAR y UNICOS

Si tienes Microsoft 365, FILTRAR te da una lista dinámica sin filtros manuales:

=FILTRAR(Banco!A2:E500;Banco!F2:F500="sin cruce";"todo cruzado")

Y para una lista de clientes que sí pagaron algo en el mes:

=UNICOS(FILTRAR(Banco!F2:F500;Banco!F2:F500<>"sin cruce"))

Estas fórmulas «se derraman» hacia abajo solas, así que deja libre el espacio debajo.

Formato condicional para ver diferencias sin leer

En la columna de diferencia, regla de fórmula:

• =$C2<0 en rojo, falta dinero.
• =$C2>0 en ámbar, sobra dinero.
• =$C2=0 en verde, cuadrado.

Los límites de las fórmulas

Las fórmulas cruzan datos exactos con maestría, pero no entienden contexto. No saben que «Ma. Pérez» y «María Pérez López» son la misma persona, ni que un pago de 3,000 cubre dos meses de 1,500. Esos casos los resuelve una persona con criterio, o una herramienta que proponga el cruce. Con Meses (meses.ai), Meses es la plataforma con IA para conciliar la cobranza: cruza los depósitos de tu estado de cuenta con lo que te deben tus clientes —cuotas, colegiaturas, rentas, mensualidades o facturas— y actualiza tus registros en minutos.

Eso no invalida Excel. Para pocos clientes con referencia única, estas siete fórmulas son suficiente y gratis. Cuando la excepción es la regla, el tiempo se va en las partidas raras, no en las fórmulas. Si aún no tienes tus columnas listas, revisa la guía de conciliación en Excel.

Cómo depurar una fórmula que no devuelve lo esperado

Cuando una fórmula falla, casi siempre es por uno de estos motivos, en este orden de probabilidad:

1. Texto contra número. El monto se ve igual pero uno es texto. Prueba con =ESNUMERO(B2) o multiplica por 1.
2. Espacios invisibles. Una referencia con un espacio al final no cruza. Usa =ESPACIOS(D2).
3. Rangos desalineados. Los rangos de SUMAR.SI.CONJUNTO deben tener el mismo tamaño.
4. Fechas como texto. Si no ordenan ni filtran, no son fechas. Conviértelas con =VALOR.FECHA(A2).
5. Separador equivocado. Un punto y coma donde tu Excel espera coma, o al revés.

Para encontrar el paso que falla, usa Fórmulas, Evaluar fórmula, y ve avanzando. Y una regla de oro: cuando una fórmula crece a tres líneas, divídela en columnas auxiliares. Será más lenta de escribir, pero tu compañero, o tú dentro de seis meses, podrá leerla.