How to match a membership list to payment records

Status per member

Paid this year =SUMIFS(Payments[Amount], Payments[Member ID], [@[Member ID]],
                         Payments[Status], "paid", Payments[Date], ">=" & YearStart)
Failed         =COUNTIFS(Payments[Member ID], [@[Member ID]], Payments[Status], "failed", Payments[Date], ">=" & YearStart)
Status         =IF([@[Paid this year]] >= [@Fee], "Paid", IF([@Failed] > 0, "Payment failed", "Unpaid"))

Payments that match no member

On the payments sheet, =COUNTIFS(Members[Member ID], [@[Member ID]])=0 lists payments from people not on the list — new members not yet added, or members paying under a different email. Match those on name and amount before contacting anyone.

Renewals due

=FILTER(Members[[Member ID]:[Email]], (Members[Renewal date] >= TODAY()) * (Members[Renewal date] <= TODAY() + 30), "None due")

The pattern behind all of these — records on one list with no match on the other — is explained in finding matches between two lists.

Worked reconciliation

Every row of the payments export should land in exactly one of these groups. If the counts don't add up to the number of payments, some payments matched two members — usually a shared family email.

Keeping it clean

• Use a member ID in payment references (direct debit reference, payment link metadata), not just the email.
• Record lapsed members with an end date rather than deleting them, so a returning payment still finds a match.
• Run the check before renewal reminders go out, so paid members don't get chased.

Matching when there is no member ID

Payment providers often only know the payer's email. Normalise it on both sides before matching, and keep a second-choice match on surname and amount:

Email key   =LOWER(TRIM([@Email]))
Member ID   =XLOOKUP([@[Email key]], Members[Email key], Members[Member ID],
              XLOOKUP([@Surname] & "|" & [@Amount], Members[Surname] & "|" & Members[Fee], Members[Member ID], "No match"))

The fallback on surname and fee is weaker — two members called Smith on the same fee will collide — so mark those matches as Probable and check them by hand. Excel 2019 has no XLOOKUP; use IFERROR(INDEX(…, MATCH(…)), …) instead. In Google Sheets, XLOOKUP works as written with ranges.

Second example: instalments and a failed retry

M-0045 pays £60 in two instalments of £30. M-0031's direct debit failed twice, then succeeded on a retry. The payments export has one row per attempt:

With the status formula above, M-0031 now shows Paid (£60 paid, even though there were 2 failed attempts), because paid is checked first. M-0045 shows £30 paid: the July instalment is still pending, so it is a timing item rather than an arrear. Count pending payments separately so they aren't chased.

The same check in SQL

SELECT m.member_id,
       m.fee,
       COALESCE(SUM(CASE WHEN p.status = 'paid' THEN p.amount END), 0)   AS paid,
       COUNT(CASE WHEN p.status = 'failed' THEN 1 END)                   AS failed_attempts,
       COALESCE(SUM(CASE WHEN p.status = 'pending' THEN p.amount END), 0) AS pending
FROM members m
LEFT JOIN payments p
  ON p.member_id = m.member_id AND p.date >= DATE '2026-01-01'
GROUP BY m.member_id, m.fee
ORDER BY m.member_id;

The left join keeps members with no payment rows at all, which is exactly the Unpaid group. For more on left and anti joins, see joining two spreadsheets on a common column.

Troubleshooting

Checking the totals

Two totals prove the reconciliation: paid amounts across all members plus payments with no matching member should equal successful payments in the provider export, and the number of members in all status groups should equal the number of members on the list. Do the membership check first, then pass the paid total to whoever reconciles the provider's payouts with the bank.

Doing it in Google Sheets

Membership lists often live in Google Sheets fed by a form. Keep the form responses on their own tab and never sort them; build the member list on a second tab that refers to them, and the payments export on a third. SUMIFS, COUNTIFS and FILTER all work as above with ranges. If payments come from a provider that can send data to Sheets automatically, still keep a monthly snapshot so you can see what changed.

Common mistakes

• Chasing a member whose payment is only pending.
• Matching on name alone when the payer is a partner or parent.
• Treating a refunded payment as paid because the original row still says paid.
• Deleting lapsed members, so a returning payment can no longer be matched.

Getting the exports

Export the member list with member ID, name, email, membership type, fee, join date, renewal date and end date. From each payment provider (card processor, direct debit provider, bank), export transactions for the membership year with date, amount, payer email or reference, and status. Status names vary by provider — one may say succeeded where another says paid or confirmed — so map them to three values (paid, failed, pending) before matching.

If members can pay in several ways, stack the provider exports into one Payments table with a Source column first. That way a member who failed by direct debit and then paid by card shows as Paid, not as Payment failed.

Repeating it monthly

1. Export the member list and every provider's payments for the membership year to date.
2. Stack payments, map statuses and refresh the status formulas.
3. Work through the groups in order: unknown payers first (they may resolve unpaid members), then failed payments, then unpaid members.
4. Send renewal reminders only to members still Unpaid after that.
5. Save the month's workbook so you can see who moved between groups.

Frequently asked questions

How do I find members who haven't paid?

Sum each member's successful payments for the period with SUMIFS and flag those below the fee.

How do I list failed direct debits?

Count payments with a failed status per member, or filter the payments export by status and join it to the member list.

What if members pay with a different email?

Match on member ID where possible; otherwise review unmatched payments by name and amount.

How do I handle family memberships?

Give the membership one ID and list each family member under it, so payments match the membership rather than individual emails.

Related guides

• How to match Stripe payments to accounting invoices
• How to do a bank reconciliation in Excel
• How to reconcile delivery app payouts with your till
• How to reconcile expense claims with card statements
• How to reconcile PayPal transactions with your bank statement