Multi Dimensional Lookup

[coolkat]

Hi

I would appreciate some help to understand the formula I need to
undertake this calculation:


In one sheet I have a column of employee names and a row of process
codes. Below each process code is a value representing the number of
hours that process will take to complete. Each employee has a number
of hours assigned against each process.

In another sheet within the workbook I have a skills matrix. This
consists of a column of employee names and a row of process codes.
Each employee has a % assigned to each process. This shows their
effectiveness level in that process.

The solution I need is for excel to lookup the employee in the skills
matrix, match the process code that their hours have been assigned to,
read their effectiveness % from the skills matrix and re-calculate the
number of hours they have been assigned to that process in line with
their skill %.

Look forward to hearing from you guys asap as this one has really
baffled me.

Coolkat

 

[Bruce Sinclair]

I would appreciate some help to understand the formula I need to
undertake this calculation:
In one sheet I have a column of employee names and a row of process
codes. Below each process code is a value representing the number of
hours that process will take to complete. Each employee has a number
of hours assigned against each process.
In another sheet within the workbook I have a skills matrix. This
consists of a column of employee names and a row of process codes.
Each employee has a % assigned to each process. This shows their
effectiveness level in that process.
The solution I need is for excel to lookup the employee in the skills
matrix, match the process code that their hours have been assigned to,
read their effectiveness % from the skills matrix and re-calculate the
number of hours they have been assigned to that process in line with
their skill %.
Look forward to hearing from you guys asap as this one has really
baffled me.

Going back a bit in the process, you do realise this will give you a
theoretical number, not a real one in any sense of the word?
No one can accurately guess effectiveness when it comes to people, nor
usually IME, the number of hours a thing takes to do.

But good luck.