How to allocate payment receipts against invoices in Google Sheets? Using Vlookup in Google Sheets, we can effortlessly match single receipts of payments. When you receive a payment against a specific invoice, you can easily match it using the invoice number.
But what if you want to allocate payment receipts in Google Sheets without an invoice number or any other reference to match, other than the customer name? Things get a bit more complicated in that case. Let’s dive into how to properly allocate payment receipts against invoices using Google Sheets.
Invoice Details (Sample Data) for Allocation
Copy-paste the accounts statement (statement of account) in a new Google Sheets file. The table should look like this:
| Customer | Invoice Date | Invoice # | Debit | Credit | Balance |
|---|---|---|---|---|---|
Before allocating payments and finding the outstanding balance, let’s try to match receipts.
Match Single Payment Receipts Using Vlookup in Google Sheets
Assume you have received the following payments:
| Invoice # | Receipt Amount |
|---|---|
To compile both reports, insert the following formula in cell E2:
=ArrayFormula(IFNA(vlookup(C2:C,Sheet2!A2:B,2,0)))
The Vlookup formula above looks up Table #2 for the search keys (invoice numbers) from Table #1 and returns the corresponding receipt amount. You can keep adding new receipts in Table #2, and the formula will automatically match and insert the amount in Table #1.
Now let’s address the issue of on-account or partial payments.
Match Multiple Payment Receipts Using Vlookup and Query Combination in Google Sheets
For explanation purposes, let’s modify Table #2:
| Invoice # | Receipt Amount |
|---|---|
The earlier Vlookup won’t correctly allocate (match) the payments received. To solve this, summarize the receipts in Table #2 using a Query formula. Replace Sheet2!A2:B with the Query formula in the Vlookup formula:
=ArrayFormula(IFNA(vlookup(C2:C,query(Sheet2!A1:B,"Select A, Sum(B) Where A is not null Group by A",1),2,0)))
Now the payments received will be correctly allocated/matched in Table #1.
Allocation of Payment Receipts Against Invoices in Google Sheets
Assume you have a mix of the above examples and lump-sum receipts. To correctly get the customer account balance by allocating such payment receipts, follow these steps:
- Insert the following formula in cell E2 and copy-paste it down:
=min(D2,sumif(Sheet2!$A$2:$A,$A$2:$A,Sheet2!$B$2:$B)-sumif($A$1:A1,A2,$E$1:E1))
This formula allocates the payment receipts against invoices, taking into account full, partial, and on-account payments.
How does the formula work? It uses two Sumif formulas and one Min formula.
- Sumif Formula #1 sums the payment receipts in customer-wise.
- Sumif Formula #2 returns the cumulative sum (running total) of the receipt values up to the current row.
- The Min formula ensures that the allocation amount doesn’t exceed the debit amount in the case of on-account receipts.
What about the allocation of new payments? Simply insert them below the last non-blank row in Table #3, and the payment will automatically get allocated in the statement of account in Sheet1.
Google Sheets Formula for Outstanding Balance
To calculate the outstanding balance, insert the following formula in cell F2:
=ArrayFormula(if(len(A2:A),D2:D-E2:E,))
And that’s all you need to know about allocating payment receipts in Google Sheets. For more tips and tutorials, check out Crawlan.com.
