Dependent drop menu with formula

[gta1216]

I'm trying to create an excel sheet with 4 columns. Column A will have a dropdown list of choices. Column B will have a dropdown list of either yes or no. Column C will be the weight you need to enter. Column D will show the value after it auto calculate. On a separate tab, there would be different rate (example: minimum charge $500, 100-200 lbs: $400 per lb, 200-300 lbs: $300 per lb, maximum $1000) depending on the value you chose from column A, and whether you choose yes or no in column B.

 

[kweaver]

how does column A relate to the calculation and what does Yes or No do to the calculation?

 

[gta1216]

It's complicated to explain column A and B. So let's pretend I own a butcher shop (obviously I don't). Column A will list the diff types of meat (beef, chicken, pork, lamb). Column B will list whether the meat need to be trimmed. The user will choose the type of meat, trimmed or not, and enter the pounds purchased. Diff meat will have diff price per pound. Trimmed and untrimmed will be diff price. There's a min and max prices, and diff weigh range will cost diff per pound.

 

[kweaver]

My issue is how do column A and/or column B influence the weight lookup to determine the value?

If the Rate sheet looks like this and you enter a weight of, say, 275, the value is $400.

[TABLE="width: 192"]
<colgroup><col style="width:48pt" width="64" span="3"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Range1[/TD]
[TD="class: xl65, width: 64"]Range2[/TD]
[TD="class: xl65, width: 64"]Rate[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]99[/TD]
[TD="class: xl66, align: right"]$300.00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl66, align: right"]$500.00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65, align: right"]300[/TD]
[TD="class: xl66, align: right"]$400.00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]301[/TD]
[TD="class: xl67, align: right"]1.00E+06[/TD]
[TD="class: xl66, align: right"]$300.00[/TD]
[/TR]
</tbody>[/TABLE]
How does the yes/no change that, if it does?

If not, then the value column (D) would be the following (taking into consideration you said 1000 was the max): =MIN(1000,VLOOKUP(C2,Rates!$A$2:$C$5,3,TRUE))