Excel If vloopup Return Values Query

Ok this is a tad complicated to explain and im guessing even harder to solve. Im trying to automate a debtor's list at my work so we don't have to manually change it every week which can take hours. Currently we have one master file that contains all amounts owed to us and another that lists all outstanding amounts and any information on that client relevant to the invoice.

The master file is automated from a CSV file however i am attempting to integrate the overdue list into this and make it automated as possible. This issue im having is if i automate the debtors list then any comments i have are also erased

Client-----Invoice #-----Amount-----Comment

X----------1-------------400--------Will not pay until (###)

Y----------2-------------500--------Having an issue with designs

Z----------3-------------600--------Something

What happens when i automate this is that the comments remain however all the other columns are replaced with the current data which messes the entries up. Essentially what i thought to do is the following however im not sure how to implement it.

• Have the master debtor list on one worksheet and another sheet for the debtor's and comments

• Using a formula the 2nd sheet will extract values from the master sheet based on the following:

  • If the invoice number is on the master list and not the 2nd sheet then all information will be copied to the 2nd sheet.
  • If the invoice number is on the master list and the 2nd sheet nothing will be done
  • If the invoice number is on the 2nd sheet but not on the master list the information will be deleted

I basically want the information to be retrieved if it isn't there, deleted if it isn't on the master list and left alone if it is on both already. This will therefore let people add comments but automatically delete once payment has been recorded.

Any help would be fantastic as ive tried a huge amount of If, vlookup, match etc and can't seem to get the result id like.