multiple value returned from vlookup into a data validation list

hi folks,

i am probably pushing the envelope a little too far here. i have a situation as follows:

clmA clmB
a 1
b 3
b 2
b 4
c 4
c 2
d 1

for certain reasons (i can go into details if asked), i would like to do the following.

the above values in clmA are available as a data validation list elsewhere within the same spreadsheet (different tab, though). when user select a value, say 'c', from the picklist in a particular cell, in the adjacent cell i would like the data validation list to present the two values 4 and 2 (VLOOKUP returns only the first value it finds in the array, 4). i tried to use INDEX, but got stumped because i could not figure out how to use CTRL+SHFT+ENTER within the Data Validation > List > Source.

much obliged if i can get some help on this.

kind regards.

Assuming A2:A8 contains {a;b;b...}. This list has been sorted.
B2:B8 contains {1;3;2;...}
E2 contains data validation from A2:A8
F2 validation:

Please Login or Register  to view this content.

Originally Posted by icestationzbra

hi quang,
this is exactly what i was looking for!!! awesome!
cảm ơn bạn rất nhiều (courtesy google translator)...
best regards.

Very suprise and happy to hear Vietnamese words from you.
Further query, feel free to contact!

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.