Multiple References and Criteria INDEX/MATCH

RuthieRooks

Hello everyone,

I am working to connect several sheets to create a master "Tracker" for 600+ projects used across the department. I've been able to get pretty far using INDEX/MATCH/COLLECT functions to match by project numbers (unique ID) and pull in dates and status indicators from other sheets used by specialized teams working on various areas of the projects. I'm getting stuck trying to pull in the completion dates from a sheet ("Processing") where the same project number may be used multiple times to submit work requests, via form, over the course of a couple months. We want to Match the individual tasks to pull back the completion dates for each task from the Processing sheet into the matching date columns in the Tracker.

Processing work requests can be made for 4 distinct tasks,

• Foot and Spell Check – Letter
• Foot & Spell Check – Statements
• Processing - letter
• Processing - statement

and can be requested in separate submissions for individual tasks at different times, all 4 tasks at the same time, or a variation on those (image below). These tasks can also be requested repeatedly. We need:

• to pull in the completion dates only for the most recent submissions
• to track the completion dates for each task separately

I am currently using this formula (with 4 different tasks in the "FSProcessing")

but it's only catching the earliest submissions matched by project number, and I'm not catching dates for tasks where the submissions include more than one relevant task is selected. I think I need to add in a OR/CONTAINS and MAX formula to pull in the dates only for the most recent processing task submissions and allow the dates to flow back to the correct date columns in the tracker sheet and also catch the additional entries. Am I on the right track? If yes, I'm not getting the syntax correct - HELP!

Many thanks!

Nick Korna

Hi @RuthieRooks,

You would need to change the 1 (the row reference) to something more suitable. If the last row is always the relevant one, you could use a COUNTIFS. If it's a date (for example the latest entry), then a MAX/COLLECT would work.

Hope this gives you some ideas, but let us know if you have problems/questions still!

RuthieRooks

Thank you, @Nick Korna. I will troubleshoot with those suggestions.