Google sheets last non zero index and match

kathleenburke93

New Member
Joined
May 19, 2021
Messages
6
Platform
  1. Web
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:
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi & welcome to MrExcel.
Not sure if there is a better way with sheets, but this should work
Excel Formula:
=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<>""))))
 
Upvote 0
Solution
Hi & welcome to MrExcel.
Not sure if there is a better way with sheets, but this should work
Excel Formula:
=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)
 
Upvote 0
Just format the cell as a date.
 
Upvote 0
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),"")
 
Upvote 0
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
 
Upvote 0
As your current wont return N/A you can just use
Excel Formula:
=ifna(myformula, your formula)
 
Upvote 0
As your current wont return N/A you can just use
Excel Formula:
=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:
 
Upvote 0
You don't need the arrayformula part it should just be
Excel Formula:
=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"))
 
Upvote 0
You don't need the arrayformula part it should just be
Excel Formula:
=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?
 
Upvote 0

Forum statistics

Threads
1,223,516
Messages
6,172,777
Members
452,477
Latest member
DigDug2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top