Google sheets last non zero index and match

[kathleenburke93]

I am working on setting up a more organized system for keeping track of dates for a school. I have 2 forms set up (initial referral and update existing student). Each of these forms populates into an excel sheet and I am collecting the data into a 3rd. I don't know if I can clearly explain this but going to try... What I am trying to do:

Ex: student Kathleen Burke was referred after having vision screened but not hearing and for multiple areas of concern. This info goes from the form into excel sheet "DG initial referral" I have a formula =IF('DG initial referral'!W3="yes",'DG initial referral'!V3,"X") that pulls the vision date over and similar formula for other areas. This student then goes through the special education process and they fill out the "update student information" form now that they have a date for hearing screen and set up a date for a MET1 (column header). I have a formula that pulls this information over =ArrayFormula(INDEX('update student information'!E$2:E$10105,MATCH(1,--('update student information'!$C$2:$C$105=A3)*--('update student information'!E$2:E$10105<>""),0),1)).

However, I am running into an issue because if we go to update student information again (meeting date changed or new eligibility etc) I am trying to get it to pull the last non zero information from "update student information" or to be able to override "DG initial referral" (ex it's been over a year and did a new vision screen and have a new date coming in through the update form)

Here is a copy of the workbook:

Copy of Discovery Gardens Initial referral

update student information Timestamp,Who is filling out this form?,Who are you updating? (Student first and last name),Who is the student's case manager?,Date for MET1,Date for MET2,Date for IEP,Eligibility category,Comments,Hearing screening date,Vision screening date,Did this student pass hear...

docs.google.com

 

[Fluff]

It should work with text or numbers.