Index Function Help

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.

Find more posts tagged with

Healthcare

Formulas

Accepted answers

Paul Newcome

Try this adjustment:

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.