Index and Match

[Machala Sentance]

Hi

I am trying to write a formula and I think that Index and match should work, but I am having problems.

Essentially I need to find an activity from a long list and match it to a week number, I then need to relate this to a staff member.  The data I need to look at is over 2000 rows and I need to be able to track each activity into weeks

I have written this - {=INDEX(Sheet1!$I$1:$I$20555,MATCH(Sheet2!$C2,Sheet1!$C$1:$C$20555,MATCH(Sheet2!AI$1,Sheet1!$E$1:$E$20555,0)))}

I get a result but it stll returns a value when there is no week allocted to the activity.

I have attached a small copy of the spreadsheet

Any help would be great

Thanks

[#2]

@Machala Sentance 

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.

[#3 · Accepted solution]

@Machala Sentance 

 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.

[#4]

@Machala Sentance 

In same assumptions as @Riny_van_Eekelen I added Power Query variant.

Bu I agree with @mathetes - formula is not linked with the sample and that's not clear what shall be the result - show only assigned activities or that could be unassigned in the list, show only weeks with assignments or all 52 weeks, and in general what to show.

[#5]

@Riny_van_Eekelenthank you for your reply, exactly what I was looking for.

I did think of a pivot but this data may be used for other things that a pivot would not be suitable for.

Thank you again for saving my sanity.

Machala

[#6]

@Sergei Baklan  & @mathetes thank you all for your input

Much appreciated

Machala

[#7]

@Machala Sentance 

If you have access to Office 365, then

= XLOOKUP( 1, 
        (ActivityTbl[Activity Name]=@activity) * 
            (ActivityTbl[Week]=@weekNum), 
         ActivityTbl[Staff],
        "" )

should work. 

[#8]

@Peter Bartholomew 

Peter, as variant, assuming we repeat list of activities here,

=IF((weekNum=ActivityTbl[Week]),ActivityTbl[Staff],"")

[#9]

@Sergei Baklan 

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.

[#10]

@Peter Bartholomew 

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]),
"")