INDEX & MATCH using multiple criteria

Brent Guhl

Thank you @Genevieve P. I appreciate you sticking it out and continuing to help.

That pro desk is a good idea as it is hard to explain everything in text. Conceptually, I'm trying to enter and then track cases. It doesn't really matter if it's sales or medical visits, manufacturing or mining. We have time 1 where the case is created, then multiple times when the case is followed and may be closed.

When a case is created (a medical visit, a site chosen for mining, an order placed for a car, a building construction started), time 1 starts. Time 2 might be when the medical case is closed, the car arrives, oil is struck, or the concrete is poured. Time 3 might then be when the customer picks up the car, when the site is in full production, framing begins, or the follow up appointment is to take place. And we can lay out many more times. Automations can be set up to remind patients of upcoming appointments, customers to come pick up their cars, or in a project management style to check if progress is on track. In this latter, automations can also be sent to the next team to do plumbing/electrical/storage/ transportation or whatever the next need is. The 20 automations I'm running on the tracker pertain to when the next visit is coming up and reporting the results to the patient once I have them.

The problem/key is to differentiate the opening of each case from multiples. If we're talking about Costco warehouses, that would be the equivalent of the patient name. We need another identifier such as the town the warehouse is going up in or the time/date of the medical visit.

Using the DATEONLY function does get just the date, and either the JOIN or your easier + code does get the date and email. Either of these should work as a unique identifier. However, I'm still getting the wrong date!

Here's the code for the Created + Email column on the Intake sheet:

=DATEONLY(Created@row) + " - " + Email@row

and here's the code for the INDEX and MATCH on the Tracker:

=IFERROR(INDEX({Intake Date of Case}, MATCH([Created + Email]@row, {Intake Created Helper}, 0)), "Email Not on Intake")

As I day, the output is giving the wrong date. This shouldn't happen as we do have a unique identifier. Note that in the above code, the Intake sheet column is called Created Helper, whereas the column using the code on the Tracker is Created + Email.