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.
I'm sorry, but I'm not following very well what exactly you're trying to do. Your second sheet (called Sheet4 in your sample) is where I presume you have written your formula (please confirm), but it has no space (no column for such things as "Staff" although you say you're wanting to find the activity that maps to any given week and "relate this to a staff"--- is it the staff name that you're wanting to retrieve and place under the Week in the row of the corresponding Activity?
You say there are over 2,000 rows in the actual spreadsheet: is each Activity Name unique, or do they repeat? Do they repeat in such a manner that the combination of Activity and Week are unique?
For me at least a more complete description would be very helpful. Maybe you could upload the sample with your formula in the spot where you're wanting it, with even a note describing the expected result where the formula isn't producing it.