# Calculate % utilization of the resources based on Project Type, Current Project Duration

Options
✭✭

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'

---------------------------------------------

Tried this formula and it seems to be working.

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

Please review and advise if you have any other ideas. Thank you!

• ✭✭✭✭✭✭
Options

It sounds like you're trying to INDEX off of multiple criteria, so you'll need to use the COLLECT function to customize your INDEX range. If I can do it in my head with your variables, it'd be:

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

The last 1 in the INDEX formula assumes that there will only ever be one combination of Project Type/Duration to return.

Jason Tarpinian - Sevan Technology

Smartsheet Aligned Partner

• ✭✭✭✭✭✭
Options

It sounds like you're trying to INDEX off of multiple criteria, so you'll need to use the COLLECT function to customize your INDEX range. If I can do it in my head with your variables, it'd be:

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

The last 1 in the INDEX formula assumes that there will only ever be one combination of Project Type/Duration to return.

Jason Tarpinian - Sevan Technology

Smartsheet Aligned Partner

• ✭✭
Options

Hi Jason,

Brilliant! This worked. Thanks a lot for your help.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!