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]

Hi & welcome to MrExcel.
Not sure if there is a better way with sheets, but this should work

=index(filter('update student information'!E$2:E$10105,('update student information'!$C$2:$C$105=A3)*('update student information'!E$2:E$10105<>"")),COUNTA( filter('update student information'!E$2:E$10105,('update student information'!$C$2:$C$105=A3)*('update student information'!E$2:E$10105<>""))))

 

[kathleenburke93]

Hi & welcome to MrExcel.
Not sure if there is a better way with sheets, but this should work

=index(filter('update student information'!E$2:E$10105,('update student information'!$C$2:$C$105=A3)*('update student information'!E$2:E$10105<>"")),COUNTA( filter('update student information'!E$2:E$10105,('update student information'!$C$2:$C$105=A3)*('update student information'!E$2:E$10105<>""))))

Thank you for the quick reply but that returned 44340 and I would need it to return 5/24/2021 as that is the last non blank update under MET1 (column E)

 

[Fluff]

Just format the cell as a date.

 

[kathleenburke93]

Just format the cell as a date.

Thank you! That worked!

If it returns NA is it possible for it to reference "DG Initial Referral" and pull the information from there, and if it is still NA come back blank?

I feel like I would encapsule that whole formula you sent in =IFNA((IFNA(Your formula), (Formula to reference DG initial referral),"")

 

[kathleenburke93]

Thank you! That worked!

If it returns NA is it possible for it to reference "DG Initial Referral" and pull the information from there, and if it is still NA come back blank?

I feel like I would encapsule that whole formula you sent in =IFNA((IFNA(Your formula), (Formula to reference DG initial referral),"")

I currently have hearing for example as =IF('DG initial referral'!N3="yes",'DG initial referral'!M3,"X")) because it was put in during the initial referral but if it becomes updates through the update sheet I want to to reference the update not the initial

 

[Fluff]

As your current wont return N/A you can just use

=ifna(myformula, your formula)

 

[kathleenburke93]

As your current wont return N/A you can just use

=ifna(myformula, your formula)

that's what I thought but it still comes back with NA and tells me "Wrong number of arguments to ARRAYFORMULA. Expected 1 arguments, but got 2 arguments."

Looking at the hearing column:

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]

You don't need the arrayformula part it should just be

=IFNA(index(filter('update student information'!J$2:J$10105,('update student information'!$C$2:$C$105=$A2)*('update student information'!J$2:J$10105<>"")),COUNTA( filter('update student information'!J$2:J$10105,('update student information'!$C$2:$C$105=$A2)*('update student information'!J$2:J$10105<>"")))),IF('DG initial referral'!N3="yes",'DG initial referral'!M3,"X"))

 

[kathleenburke93]

You don't need the arrayformula part it should just be

=IFNA(index(filter('update student information'!J$2:J$10105,('update student information'!$C$2:$C$105=$A2)*('update student information'!J$2:J$10105<>"")),COUNTA( filter('update student information'!J$2:J$10105,('update student information'!$C$2:$C$105=$A2)*('update student information'!J$2:J$10105<>"")))),IF('DG initial referral'!N3="yes",'DG initial referral'!M3,"X"))

thank you so much it worked! Any idea how to make it do it with text instead of numbers ex: address or areas of concern?