Associating Labels in a Dropdown List with Data

Greetings!

(I have edited this post to in an attempt to explain things better.)

Say in B1 I have a drop-down list with the following as it's data source:

B2=Apple
B3=Pear
B4=Orange
B5=Mango

A1 has the formula "=25*B1", with the intent that depending on what the user chooses in B1, A1 will change it's value.

Obviously the formula in A1 won't work, since none of the choices available to the user in B1 are actual numerical values.

Now say in the C column I have the values:

C2=9.5
C3=1.3
C4=8.4
C5=12.4

Is there a way to make it so when the user chooses a B value from the drop-down list, A1 will instead use whatever is in the B1 cell to refer to a corresponding C value which can then be multiplied in the formula?

For example the User chooses "Orange" in the B1 drop-down list. A1 will utilize "Orange" (B4) to refer to the value in (C4) which is 8.4. Now the formula will work, (=25*8.4). Is there a function or a method to accomplish this?


I use Excel 2007.


Thank you very much!