Populate Cell Based On A Dropdown Selection Of Another

[jlo]

Background: I have a worksheet that I use to generate job estimates. When it comes to calulating the warranty cost we multiple the SF of the job by a predetermined figure based on the warranty type. For examle, if it is a standard 10-year warranty, the square footage is multipled by $0.04. If the warranty is a standard 15-year warranty, the square footage is multipled by $0.07, etc. Three are 10 different warranty types that have their own predetermined cost figure.

Application: Cell A1 contains a dropdown list with all 10 warranty type options. The 10 warranty type options are pulled from a named list in another worksheet via the Data Validation routine. Cell B1 contains a locked cell, which is populated by a simple formula that pulls the square footage of the job from another part of the current worksheet. Cell C1 is the cell where we manually enter the warranty cost; $0.04, $0.07, etc. Of course, cell D1 is the cell that displays the results of its formula (B1 x C1).

Question: How do I populate cell C1 based on the dropdown selection in A1? For example, if I select from the dropdown list in cell A1 "Standard-10" I want cell C1 to display "0.04". If I select from the dropdown list in cell A1 "Standard-15" I want cell C1 to display "0.07". I need to create about 10 such conditions. This way my basic formula to multiply cell B1 (the square footage) by C1 (the warranty cost) will be accurate, based entirely on the selection in A1 (warranty type.)

VLOOKUP seems to work with just one option at a time and is asking for an adajent cell. I may be using it wrong. Please help.

(Sorry for the length of this post, but I've asked for help before and I think I didn't provide enough information the first time.)

[Dave Hawley]

Use a Lookup Cells Table, or see Lookup Value & Return Corresponding Result

[Bill Rockenbach]

Heres one way

See attached