say i've got 2 columns, 1 column is name, and the other is a set of numbers associated with the name.

now, i make a list in Excel (different column) using Data Validation tool (that's what pressing F1 told me), but how do I get it to populate the cell below this column automatically with the number associated with that name?

For example:
ABC | 123
XYZ | 234
NBC | 456

so i have a drop down box that's got ABC, XYZ, and NBC in another column. how do i get 123 automatically filled into the box below when i select ABC from the drop down box?

thank you for your help.

 

The only way I know is to write a macro?

 

you make an if statement , =if(targetcell="ABC","123",if(targetcell="DEF","45 6",if(targetcell='GHI','789',"N/A")))

didn't use your exact things cuz i didn't feel like scrolling back up

 

you put that formula at the cell to the right of your first abc/def/ghi, and then click in the bottom right of the cell with the formula and drag, or double click in the bottom right of the cell with the formula

 

what about =vlookup(a1,a1:b3, 2, true) in the column? if you use vlookup make sure you stratify column 2. if you are vlookup up text, also, use the trim( ) command to get rid of any spaces in the cell and dont forget quotes.

=vlookup(trim(a1),a1:b3, 2, true)

 

vlookup is too complicated for this situation, and he didn't say that he wants to have a key somewhere else on the sheet

 

i am assuming his list is more than 3 items long. if just 3 rows long, op can get away with if statements. but, if more data, vlookup is the only way to go, unless he loves writing if statements.

maybe i misunderstood the question.

if you just wanna be able to click on something in column 1 from a dropdown menu and have the associated column 2 data, its even simpler than if statements.

just go to DATA->filter->auto filter.

 

sounds like a vlookup situation to me.

 

oh gotcha, i assumed if it was a large list (greater than 5) he would've specified, i agree with you now

 

oh thank you all for the great input!

how can i forget vlookup?!! going to try that tomorrow

thanks again everyone.

the list is actually 8 variables long, each with a fixed value.