Thread: Drop Down List and Value Assignment

Hello,
I am relatively new in the use of Excel, and while I can figure out most stuff, this problem here I can't:

I have two Worksheets (Points and List). Now I have values in sheet one that are roughly:

A B
Apple 2
Orange 7
Banana 1
Cherry 5
Kiwi 6

What I am trying to do is the following:
Have a bank of values in sheet 1 (done), and in sheet two, I want to have drop downs with the values from sheet one (Apple, Orange, Banana, Cherry, Kiwi) in Column A. When I pick any of the values using the drop-down list (figured that out), I would like Excel to fetch the corresponding value from the other worksheet column B, and put it in sheet two column B (I select Apple from the drop-down and automatically Excel puts a 2 into the column next to it). I am completely lost what formula to use, and how. Any help would be appreciated in that matter. I guess it is not horribly difficult, I am just too stupid to figure it out.


Regards,
Malarowski

Hi,

Assuming your using the range name 'List' for your fruit names in sheet1 (and presumably for the Data Validation drop down), if your dropdown is in A1 of sheet 2 on sheet 2 use

Code:

INDEX(OFFSET(List,0,1),MATCH(A1,List,FALSE),1)

Look here:

Thank both of you. Worked like a charm. One last question: How can I have the fields have a default value being blank before the user chooses a value?

Regards,
Malarowski

P.S Figured it out, even though I thought it would be easier.