Forum Discussion
Index and Match
Added the formulae that I believe to give the outcome you are after in Sheet4 (file attached). Note that I changed the Week-headers to plain numbers. In the shaded area, I kept the intermediate steps that I took in building the formulae in the weekly schedule above it. Just my way of going through such a task, step-by-step, in order to create a bigger formula. I'm sure others will have a different approach and can come up with a more elegant solution.
At the same time, I have added a pivot table as it seems to achieve a similar and more flexible) result without complicated formulae.
Peter, as variant, assuming we repeat list of activities here,
=IF((weekNum=ActivityTbl[Week]),ActivityTbl[Staff],"")
Agreed. If the activity name on the result sheet is a simple copy of the column on the activity sheets then the entire result is given by your formula, spilling from cell B3. Just the way dynamic array formula should behave!
In fact, I would go further: it is just what spreadsheets should always have been.
If not a copy but some activities, that could be
=IF(weekNum=XLOOKUP(SomeActivities,ActivityTbl[Activity Name],ActivityTbl[Week]), XLOOKUP(SomeActivities,ActivityTbl[Activity Name],ActivityTbl[Staff]), "")