Saving data after using formulas to automatically transfer rows based on data validation

I have an issue with the attached spreadsheet. This might be a universal issue with no work around.

In the attached file. If I change the "Prospect" in Row 10 of the Contacts Tab to "Counseling", his information will automatically populate in the Counseling tab but I will lose the information in Column Q that was specific to the Prospect Tab.

I think the formula I have in here for automatically transferring data is amazing - I just can't seem to figure out how to save new data (like Column Q) so that it transfers independent of my ROW/MATCH/INDEX formulas. Sounds complicated but you can easily see it illustrated in the attachment.