HELP!! I need to write a formula and don't know how!

[Sara]

I have created a combo box that lists text items and then assigns it a
number(I guess that is what it is doing) and posts its value in
another cell. For example the drop down may say; blue, green, red,
yellow and if yellow is chosen then it populates the cell with the
number 4. I have two combo boxes that store information that I need
to write a formulat to interact. So combo box may have a range of
quantity 1-10,10-20,20-30. If the uses picks Yellow and 20-30 I need
to have a formula that takes those choices and goes and looks up a
pricing menu and posts the price for that range. I have no idea how
to do this. The pricing menu looks something like this:

Blue Green Red Yellow
1-10 .05c .06c .04c .07c
10-20 .04c .05c .03c .07c
20-30 .04c .04c .03c .05c

How can I write a formula that takes the two choices from my combo
boxes and spits back a price? I want someone who picks Yellow in the
20-30 range to see .05c as a price. PLEASE HELP!!!!!!!! I am a
beginner with a deadline!!

 

[Frank Kabel]

Hi
Assumptions:
- your lookup range is in A1:E4 (as in your example): column a stores
the ranges (as text values), row 1 the colors
- cell E1 returns the color from your combo box
- cell F1 returns the range value (also as text value)

use the following formula
=INDEX(A1:E4,MATCH(F1,A1:A4,0),MATCH(E1,A1:E1,0))

 

[Tom Ogilvy]

=Offset(A1,Cell1,Cell2)

Assume Cell1 holds the number of the selection from the combobox with the
number ranges
Assume Cell2 hold the number of the selection from the combobox with the
colors.


Assumes your data has Blue in B1 and 1-10 in A2

 

[Tom Ogilvy]

then it populates the cell with the

Number 4.

 

[Frank Kabel]

Hi Tom
jut saw your answer
somehow thought the OP used data validation listboxes