Drop down list with different values.

[Honey]

Hello!!!

Here is my problem...

I have a drop down list and I want each item on it to display a
differen value in another cell

So if my list is:



C2 Value
Tom 10
Mary 8
sarah 4
Michael 2

So If I selected Tom from the drop down list it would display 10 in C3
or if I selected mary from the drop down list then it would show 8 in
C3.

Any ideas if or how I can do this?

Thanks so much for your help....I'm always amazed by what people can do
on Excel!!!

Honey

 

[Guest]

Let's say: A2 is your dop down list, and your data in B1:C100

=VLOOKUP(A2,B1:C100,2,0)

Adjust to suit

 

[Debra Dalgleish]

You can use a VLookup formula to return the value matching the name that
is selected. There's information in Excel's Help, and here:

http://www.contextures.com/xlFunctions02.html

 

[Guest]

There are several ways to do this, but which one is best depends on where you
are getting the list of names for the dropdown list in C2 from? I presume
you used Data Validation to get them into it, but where do they come from?
1) Did you hand-type them when setting up the data validation, or
2) Do they come from a list of names somewhere else on the sheet?

If the list is only 4-names long you could use a nested IF statement like:
=IF(C2="Tom",10,IF(C2="Mary",8,IF(C2="sarah",4,2)))

If you have typed the names in during while setting up Data Validation, the
IF above would work, (but the IF is limited to 7 nest levels, or 8 names),
you could use this formula in C3
=CHOOSE(MATCH(C2,{"Tom","Mary","sarah","Michael"},0),10,8,4,2)

But if your list of names is coming from a list in cells elsewhere on the
worksheet, then this will work, assumes the list of names are in H2:H5
=CHOOSE(MATCH(C2,H2:H5,0),10,8,4,2)

Finally, if the list is on the sheet, and if you have an empty column next
to it, you could turn it into a table by entering the value for each name
into the column next to it, so it might look like this over at H2:I5
H I
2 Tom 10
3 Mary 8
4 sarah 4
5 Michael 2
and then you could use something like this in C3:
=VLOOKUP(C2,H2:I5,2,0)