Too many IF-AND, can LOOKUP do this?

[Guest]

Resource Development Level Integration Level

Mike 1 2
Joe 2 3
Bill 3 0
Jane 0 2
Jack 0 3

Task Type Work Resource Duration

Dev 1 D 5 Mike
Dev 2 D 3 Joe
Integ 1 I 3 Jack
Integ 2 I 2 Jane

I have information in a worksheet as above (apologies for the bad alignment).

I would like to calculate "Duration" based on the resource, their respective
development / integration level, and the work value. Duration for "Dev1"
with "Mike" assigned, for instance, should be 5. Duration for "Dev2" with
"Joe" assigned should be 6. All in all, duration should equal work times the
Dev or Int level for each resource, depending on the D or I indicator in
"type".

I have basic functionality for this working using the following formula in
the Duration cell:
=IF(AND(D3="Mike",B3="D"), C3*1,IF(AND(D3="Mike",B3="I"),C3*2,0))

I believe I have too many possible combinations to be supported with
repeating IF-ANDs for each possibility, however.

Can this be done using LOOKUP instead? Any other ideas on how to accomplish
what I need to do?

Thanks,

Tom

 

[vezerid]

Tom,

Try this formula:

=VLOOKUP(D2,Sheet1!A:C,2+(B2="I"),0)*C2

HTH
Kostis Vezerides

 

[Bob Phillips]

I will assume that the first table is on Sheet1, the second on Sheet2.

On Sheet1, B2 enter this formula, copy across to C and down

=IF(ISNA(MATCH(1,(Sheet2!$A$2:$A$200=B$1)*(Sheet2!$D$2:$D$200=$A2),0)),0,
INDEX(Sheet2!$C2:$C200,MATCH(1,(Sheet2!$A$2:$A$200=B$1)*(Sheet2!$D$2:$D$200=
$A2),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)