find a match using vlookup (but not just first entry)

  • I am trying to find when a payment has been received that matches either an individual amount or a total amount. I receive a list of daily payments from customers along with their individual customer code. I also have a list of all outstanding customer invoices. I am using a simple lookup to say "lookup this customer code, find from this list of outstanding invoices this amount for this customer". I have tried using a lookup but it only seemed to find the first occurrence of the customer code for a match. If the amount that has been received matches the 2nd or 3rd invoices on the list then it does not find it. The invoices are also subtotaled so if the payment matches the total (eg paid £100, 3 invoices £50,£30,£20 should match the £100 subtotal) The invoices are sorted into columns for each month. I have attached an example to try and explain things better.

    Any help would be greatly appreciated.

    James

  • It will only find the first occurrence.


    This question comes up frequently and it is not simple to solve.


    This workbook contains a UserForm to show suggestions.


    If you open this workbook and your workbook, from your workbook run the macro BalanceWiz. This will bring up a userform that has 3 steps to follow. The macro will list all possible combinations