Formula with Conditions

[kmpmba]

Require urgent assistance - Please assist with the required formula - TIA

[TABLE="width: 377"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]FILE 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Property[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Length[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Breath[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Requirement Nos.[/TD]
[TD](cell B6 - Answer required here)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rules to Calculate answer in B6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Multiply Length (B3) with Breath (B4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Divide by area (available in File 2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IF Property = D - Double the total at this stage[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Add 10% to Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Add 50 Nos. to Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FILE 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TYPE[/TD]
[TD]Area[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]139.75[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]156.95[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]163.67[/TD]
[/TR]
</tbody>[/TABLE]

 

[Finalfight40]

Im assuming File 2 is the name of sheet 2? And also the values in file 2 are in B2, B3 AND B4?

something like this?

=IF(B2="D", ((B3*B4)/INDEX('FILE 2'!B2:B4, MATCH('FILE 1'!B5, 'FILE 2'!A2:A4)))*2*1.1+50, ((B3*B4)/INDEX('FILE 2'!B2:B4, MATCH('FILE 1'!B5, 'FILE 2'!A2:A4)))*1.1+50)

 

[kmpmba]

Im assuming File 2 is the name of sheet 2? And also the values in file 2 are in B2, B3 AND B4?

something like this?

=IF(B2="D", ((B3*B4)/INDEX('FILE 2'!B2:B4, MATCH('FILE 1'!B5, 'FILE 2'!A2:A4)))*2*1.1+50, ((B3*B4)/INDEX('FILE 2'!B2:B4, MATCH('FILE 1'!B5, 'FILE 2'!A2:A4)))*1.1+50)

Mant thanks - used this instead =(B3*B4/VLOOKUP(B5,A18:B20,2,0)*IF(B2="D",2,1))*1.1+50