Hello,
I am looking for a pointer in the right direction.
We have been asked to provide a transaction report with a breakdown of each payment/receipt and I'm struggling with the best way to do this.
Pulling data from Xero, doesn't actually provide what we are looking for, which is a massive pain. The data the reports provide will give bits, but I'm then pulling another 4 or 5 reports to find the rest of what I'm looking for and it's getting confusing and very messy!!
I've decided to make this a daily task of entering the data, which I know will be clean and can create the reports as required, but looking for Excel to do some of the entry for me.
The below table is a start, with a formula to work out the VAT amount. What I'm looking for is to recognise that DVLA, Pensions, HMRC etc, do not have VAT on the transaction. I have a separate worksheet with a list of the companies that would always return a zero amount, but not sure if the IF function is the way to go, or how it would work?
Any ideas appreciated.
| Payment Date | Payment Method | Payee | Total Paid | Net | VAT | Code | Code Name | |
| 01/08/2024 | Current | DVLA | 28.00 | 23.33 | 4.67 | 454 | Vehicle Road Tax | |
| 01/08/2024 | Current | DVLA | 28.00 | 23.33 | 4.67 | 454 | Vehicle Road Tax | |
| 31/07/2024 | Current | DVLA | 28.00 | 23.33 | 4.67 | 454 | Vehicle Road Tax | |
| 01/08/2024 | Current | 0.00 | 0.00 | #N/A | ||||
| 01/08/2024 | Current | 0.00 | 0.00 | #N/A | ||||
You can write a formula that looks up the Payee from the transactions list in the list of companies with zero VAT. If found, then apply 0%, otherwise apply 20%.
How exactly will depend on the the structure of your worksheets (not workbooks I presume). A small example is included in the attached file. One using the MATCH function (works an all Excel version. Another that uses XLOOKUP (works only on modern Excel versions.
Come back here if you get stuck.
Thank you Riny, got it working a treat!
