Associating Labels in a Dropdown List with Data

**pandaman** · 12-15-2010, 03:34 AM

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!

**JBeaucaire** · 12-15-2010, 04:05 AM

A MATCH() can accomplish this. If your list of fruits is B2:B10, then let your B1 drop down display those items, then the A2 formula would be:

=9*MATCH($B$1, $B$2:$B$10, 0)

**pandaman** · 12-15-2010, 04:49 AM

Thanks for your suggestion JB, but unfortunately that's not quite what I was looking for.

Although the MATCH function does work for the example I gave, it was a bit of a coincidence. I didn't mean for my B Column values to start from 1 and go in sequential order.

Allow me to revise the scenario.

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

At first I thought LOOKUP would be the right answer, but I can't seem to get it to work. Though it's possible I'm doing it wrong.