INDEX MATCH is overridden by new entries

Tobiah

Hello,

I'm trying to create a formula that will identify the [Unit] in Sheet 1 that have confirmed [Today Entry] from Sheet 2. The issue is when a new entry comes through Sheet 2 where [Unit] matches it will override the previous INDEX MATCH action, removing the entry from the list of [Today Entry] in Sheet 1 even though they still technically meet the [Today Entry] criteria (which is time based). Any way I can ensure [Today Entry] remains valid until that checkmark disappears?

Sheet 1

Sheet 2

Darren Mullen

@Tobiah

If you'll only ever have 1 matching Unit that also has the Today Entry box checked, then you can use the Collect() function to match the unit and that the Today Entry is check. The wrap the Collect() function with the Index and use an index value of 1.

=Index(Collect({Your criteria}), 1)

That will then only pull in the matching unit that also has the Today Entry box checked.

Tobiah

I tried this formula however whenever a specific unit met the matching criteria for both [Today Entry] and either [Tomorrow Entry] or [Yesterday Entry], the most recent entry that matches one of those columns takes precedence. For instance, Unit 1 met [Today Entry] criteria (using this formula:

=IFERROR(INDEX(COLLECT({Nursing Discharge Tracker_Today Entry Range}, {Nursing Discharge Tracker_Unit Range}, Unit@row), 1), "")

..but then later in the day another submission comes through which meets the [Tomorrow Entry] criteria which uses an identical formula that's adjusted for the appropriate columns), which eliminated the checkmark in the [Today Entry] column.

Is there not a way for these separate columns to be honored simultaneously?