Index Function Help

VictoriaL

I am trying to index data by date and have applied the formula to the column. The problem I am running into is the last data entry is repeating to the end of the dates I have listed for the year even though those dates have not occurred and are not even listed on the sheet I am looking to.

=INDEX({Census}, MATCH(Date@row, {Date}))

What am I doing wrong or is there an easier way to pull this data? I pulled it to a separate sheet so that I could chart it by day without extra noise. Any advice is appreciated.

Paul Newcome

Try this adjustment:

=IF(Date@row < TODAY(), INDEX({GSRIRFAdultCensus}, MATCH(Date@row, {AdmissionDate}, 0)))

VictoriaL

Does anyone have a better alternative to: =IF(Date@row < TODAY(), (INDEX({GSRIRFAdultCensus}, MATCH(Date@row, {AdmissionDate}))), 0)

Paul Newcome

Are you able to provide some screenshots for reference?

VictoriaL

This is the sheet of submissions where you can see there is no data for 11/21/23

This is the sheet where I am pulling the above data to house separately in order to chart by date and you can see that the 11/21/23 date is pulling the 11/20/23 data.

Paul Newcome

Try this adjustment:

=IF(Date@row < TODAY(), INDEX({GSRIRFAdultCensus}, MATCH(Date@row, {AdmissionDate}, 0)))

VictoriaL

Beautiful, it worked! I was even able to take out the IF Today piece to give me a "NO MATCH" which is perfect for charting as I wanted it to show nothing vs a zero. Thank you!!

Paul Newcome

Happy to help. 👍️

The IF function in my formula would have output the blank instead of the error.