help with index and match that returns value based on multiple criteria

Hello,

I have a "CALENDAR" tab which includes subject ID (column B), the date of visit (column A), and a list of tasks (rest of the columns). Under each column I record the date the subject completed the task. On the tab "TASK_COMPLETION_LIST" I have a list of all subject IDs in column A and the diferent tasks are the headers of the rest of the columns.

I want to write a lookup formula in each cell of the "TASK_COMPLETION_LIST" tab that will return the date the subject completed the task. The tricky part is that same subject may come in multiple times on different days and complete different tasks on different days so the lookup function needs to be able to find multiple matches and return only the cell that is not empty (in theory each subject will have to complete each task only once).

For example, I need a formula that I can put in cell B10 of the "TASK_COMPLETION_LIST" tab and drag across to fill th rest of the columns. I am trying an index and match function with multiple criteria but its not working for me.

Thank you for your help.

Miguel

Hello,

Thanks GeoffW283 for your response! I tried the formula but it doesnt produce the desired results. For example I entered it in cell B5 of the "TASK_COMPLETION_LIST" tab and it gives back a date which does not correspond to the date that subject completed the task. The date each subject completed each task is in the "CALENDAR" tab.I attached another excel file which I think is clearer. The desired results are in rows 2 and 3 of the "TASK_COMPLETION_LIST" tab.

yes, it produces the desired results, thank you! I had never used the sumproduct formula so I didnt catch the cell reference error. Thank you!