Help With Multiple If/And Statements

[slparadise]

TABLE 1
Widget	--Length of Widget--	--SF of Widget--	$$COST$$

<tbody>
</tbody>

TABLE 2
--Length of Widget--	$$COST$$
0-5 Feet	$10
6-10 Feet	$15

<tbody>
</tbody>

I need to create a formula that will calculate the cost in TABLE 1 assuming a minimum for the --SF of Widget-- cell. I also need that formula to calculate based on the amounts in TABLE 2. I have created a drop down box in TABLE 1 for --Length of Widget-- that corresponds to TABLE 2. I am struggling with the minimum component. I need the formula to use 500 as a multiplier if the --SF of Widget-- is LESS THAN 500 and to use the number input in the --SF of Widget-- cell if that number is GREATER THAN 500.

Essentially if the --Length of Widget-- is 0-5 feet and the --SF of Widget-- is 300; the equation would be $10 * 500 ($10=0-5 Feet; 500=--SF of Widget--Minimum). If the the --Length of Widget-- is 0-5 feet and the --SF of Widget-- is 600; the equation would be $10 * 600 ($10=0-5 Feet; 500=--SF of Widget--Minimum).

Thanks for any and all help. I really do appreciate it.

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H
1	Widget	Length	SF of Widget	Cost $		Length
2	A	4	300	5000		From	To	Cost
3	B	3	600	6000		0	5	$ 10.00
4	C	5	600	6000		6	10	$ 15.00
5	D	8	450	7500		11	15	$ 20.00
6	E	7	750	11250

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D2 =VLOOKUP(B2,$F$3:$H$5,3)*MAX(500,C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>