LOOKUP function troubles...please help!

[HTSNY_JACK]

HI - I am trying to work out using the proper function(s) to achieve my desired result, but I am severely stumped.

I need a spreadsheet that allows a user to make 3 decisions and have the result of those 3 decisions returned so that a formula can reference that amount to help me determine pricing. I will try to illustrate it and hopefully there is a genius out there that can help me.

There are 2 divisions, "1" and "2", each with different colors associated with them (3 colors in division 1, 2 colors in division 2), and each of these colors has 2 prices associated with it (an "H" price and an "RP" price) -- the first drop-down menu will contain a choice between "1" and "2", if the user selects division "1", then the options "blue", "green", and "red" must be visible for selection in the next cell. If they select division "2", then the options "black" and "blue" must be visible for selection in the next cell. Once the division is selected, then the color is selected, then the "H" or "RP" is selected, the corresponding amount for either the H or RP will be returned.

DIVISION COLOR H/RP PRICE
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]BLUE[/TD]
[TD]H[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RP[/TD]
[TD]$2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GREEN[/TD]
[TD]H[/TD]
[TD]$3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RP[/TD]
[TD]$4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RED[/TD]
[TD]H[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RP[/TD]
[TD]$6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BLACK[/TD]
[TD]H[/TD]
[TD]$7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RP[/TD]
[TD]$8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BLUE[/TD]
[TD]H[/TD]
[TD]$9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RP[/TD]
[TD]$10[/TD]
[/TR]
</tbody>[/TABLE]


Is something like this even possible??? i absolutely appreciate any help that anyone can offer. I am trying to run a very small business and I really could use a spreadsheet that works this way to help me keep things more organized. Thank you in advance!

-Jack

 

[etaf]

does this help explain how to do what you need

Excel Dependent Drop-down Lists - Easy Excel Tutorial

 

[HTSNY_JACK]

does this help explain how to do what you need

Excel Dependent Drop-down Lists - Easy Excel Tutorial

YES!! Thank you so much, that's exactly what I needed. I was just able to finish building the spreadsheet that I had envisions and it's working perfectly. I very much appreciate your help!