I am trying to set up a dynamic link between tables in Excel such that I can have a formula that returns who does a task on what day.

The two reference tables are one which lists the task and who is responsible for it, and the second provides a list of dates on which that task is carried out.

this would then feed into the final table:

The idea is that the formula in the final table should check the activity in the first column to return who does the task and also check whether the date in the first row is found for that activity in the second table listing all the dates for each activity.

I can only get so far using

=IF(ISNUMBER(MATCH(date_in_final_table,range_in_second_table,0)),"person","")

which works, but the problem here is I am hardcoding the value for the person. The first two tables could be changed anytime and I would like everything to update automatically.

I guess the answer is going to feature INDEX, MATCH and maybe a look up or INDIRECT, but so far I can't link everything successfully.

Can anyone help please?

CodePudding user response：

Use:

=IF(ISNUMBER(MATCH(date_in_final_table,INDEX(full_range_in_second_table,0,MATCH(activity_in_final_table,title_row_in_second_table,0)),0)),VLOOKUP(activity_in_final_table,first_table,2,FALSE),"")