Matching payments and invoiced amounts|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi there,

I have a bit of a conundrum. I need to reconcile payments and invoiced amounts on a single spreadsheet that shows negative amounts for invoiced and positive amounts for payments received.

There are operational reason why I cannot do this through our bank statements and bank references, all I have is the date, account name and amounts.

I have tried using an array function determining matching number and it works where there are only one negative and one positive number but where there are more negative than positive amounts I hit a bit of roadblock.

If you take a look at the attached data, rows 2 - 4 contains exactly the problem.

I need the Test column to match row 2 and 3 as 1 and row 4 as 2 i.e. 1,1,2

Your help would be greatly appreciated.