Lookup multiple condition

[Thomas Kuriakose]

Respected Sirs,

We have a calculation file with three tabs -

1. Input tab which has all the inputs and the corresponding Values
2. Milestone tab which has dates and "P Date" from this tab has a reduction matrix in Input tab (Table 7).
3. Calculation tab which has freight calculation based on the inputs selected in data validation.

We need to multiply the reduction factor to the calculated values in B12,B13,B19,B20 based on the milestone date B7 for "P Date". kindly guide on how to get result along with the calculated values in B12,13,19 and 20.

We also need to simplify the formula in B20 based on inputs in cells B15,B16 and B17. The current example has only one value in B15.

Kindly find attached the sample data for your reference.

Thank you very much for your support always,

with regards,
thomas

 

[bosco_yip]

Try.......

1] Add a new line for Milestone in row 7, and Data Validation dropdown list in B7

2] In D13, formula copied to D14, D20 and D21:

=B12*LOOKUP(VLOOKUP(B$7,Table6,2,0),Table7[Start Date],Table7[Rate])


3] "To simplify B20" in B21, formula :

=(Calculation!B9+Calculation!B10)*IF(B15<>"",VLOOKUP(B15,Input!A38:B44,2,0)+IF(B16<>"",VLOOKUP(B16,Input!A38:B44,2,0)+IF(B17<>"",VLOOKUP(B17,Input!A38:B44,2,0))))+(B9<>0)*Input!B48*Calculation!B9+(B10<>0)*Input!B49*Calculation!B10

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided on this query.

Much appreciated,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

One observation in B21, if the input selected is only in B16, the total Value in B21 should be 4000 in the attached example, but it is calculating only 1000.

I also input value only in B17 and the value calculated is 1000, here also the value should be 4000.

Sir, one request, how can we get the calculated value in D12, D13, in one formula in B12, B13.

Thank you very much once again,

with regards,
thomas

 

[bosco_yip]

Respected Sir,

One observation in B21, if the input selected is only in B16, the total Value in B21 should be 4000 in the attached example, but it is calculating only 1000.

I also input value only in B17 and the value calculated is 1000, here also the value should be 4000.............thomas

A bit of adjustment in B21 :

=(Calculation!B9+Calculation!B10)*(IF(B15<>"",VLOOKUP(B15,Input!A38:B44,2,0))+IF(B16<>"",VLOOKUP(B16,Input!A38:B44,2,0))+IF(B17<>"",VLOOKUP(B17,Input!A38:B44,2,0)))+(B9<>0)*Input!B48*Calculation!B9+(B10<>0)*Input!B49*Calculation!B10

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this revision and your support always.

Much appreciated,

with regards,
thomas