Calculate capacity of the resources based on Project Type, Current Project Duration

Omkar

Hi,

Sheet 1 - Contains following columns

• Resource Name
• Project Type
• Project Duration (Week Number)

Sheet 2 - Contains the following columns

• Project Type
• Project Duration (Week Number)
• % Allocation

% allocation is different for different combinations of Project Type and Project Duration (Week Number)

Ex. For Project Type A - % allocation is

• 15% if project duration is week 1
• 13% if project duration is Week 2
• 12% if project duration is week 3.

For Project Type B - % allocation is

• 25% if project duration is week 1
• 23% if project duration is Week 2
• 22% if project duration is week 3

For Project Type C - % allocation is

• 35% if project duration is week 1
• 33% if project duration is Week 2
• 32% if project duration is week 3.

I am trying to calculate % allocation for each project on 'Sheet 1' by referring to project type, project duration. It should return the appropriate % allocation value by referring to 'Sheet 2'

Please assist. Thank you!

Genevieve P.

Hi @Omkar

When you have two criteria to match (both the Project Duration and the Project Type), you will want to use the COLLECT function instead of MATCH.

Try this:

=INDEX(COLLECT({Sheet 2 - % Allocation}, {Sheet 2 - Project Type}, [Project Type]@row, {Sheet 2 - Project Duration}, [Project Duration]@row), 1)

This will bring back the % Allocation that's showing in your second sheet based on the Project Type and Duration listed in your first sheet. Is that what you were looking to do?

Cheers,

Genevieve