Okay. in Sheet1 you have something like this.

a 1
b 4
a 3
a 2
b 5
b 6

In Sheet2, column A you have a dropdown in A2. In B2 you will enter the
following formula.

=IF(ROWS(B$2:B2)<=COUNTIF(Sheet1!A$2:A$50,A$2),IND EX(Sheet1!B$2:B$50,SMALL(IF(Sheet1!A$2:A$50=A$2,RO W(Sheet1!B$2:B$50)-MIN(ROW(Sheet1!B$2:B$50))+1),ROWS(B$2:B2))),"")

CTRL+SHIFT+ENTER



"Zak" wrote in message
...
Sorry, i am a little confused.

Which sheet does the drop down list go into? i tried a few ways but i get
no
result.
Also, where do i put the formula into?

I have changed all the A's to B and the B's to A as it suits my needs
better
that way - i.e. i show the drop down list items first and then the results
to
be returned for them, like this:

column A column B
a 1
2
3
b 4
5

etc etc.


sorry, a little new to this!

thanks again.

"Gaurav" wrote:

=IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!B$2:B$126,A$1),IN DEX(Sheet1!A$2:A$126,SMALL(IF(Sheet1!B$2:B$126=A$1 ,ROW(Sheet1!A$2:A$126)-MIN(ROW(Sheet1!A$2:A$126))+1),ROWS(A$2:A2))),"")

A2 is where your drop down is.
Sheet1 column B is where you have items that you are selecting in drop
down.
Sheet1 column A is where you have items that you want to return.

This is an array formula and needs CTRL+SHIFT+ENTER not just ENTER.

Copy down as far as needed.

Hope this helps.

"Zak" wrote in message
...
Hi,

I want to make use of a vlookup (i think thats what i need!) but not
sure
how to get it working:

I have a drop down list - when user selects something from within list,
the
cell next to it would be populated with information relating to this
from
a
lookup sheet i am using. This bit is simple, i have created the vlookup
so
once something is selected it looks up in the sheet where that info is
stored
and it returns back what is required in the cells next to it.

But the problem is, the vlookup appears to be limited to returning one
cell
only.

If i have info displayed as:

a b
c
d

So if 'a' is selected in the drop down list then it should display
b,c,d
in
(which are all displayed in seperate cells in the lookup sheet), the
lookup
only returns the fisrt cell - which is 'b'. how can i get it to return
the
other items too? maybe something more comlex that a vlookup?

please help. thanks.