Vlookup - maybe?!

[Zak]

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.

 

[Gaurav]

=IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!B$2:B$126,A$1),INDEX(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.

 

[Gaurav]

Correction

A2 is where you are entering the formula.
A1 is where your dropdown 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.

 

[Zak]

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]

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),INDEX(Sheet1!B$2:B$50,SMALL(IF(Sheet1!A$2:A$50=A$2,ROW(Sheet1!B$2:B$50)-MIN(ROW(Sheet1!B$2:B$50))+1),ROWS(B$2:B2))),"")

CTRL+SHIFT+ENTER

 

[Ashish Mathur]

Hi,

You may check my article at the following link

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Zak]

Hi,

Sorry for keep having to bother you but ive tried exactly what you said and
nothing is working. I dont suppose you could create a dummy excel sheet for
me and attach here? If im asking for too much then you dont have to.

Also the data is not as you stated:
a 1
b 4
a 3
a 2
b 5
b 6
But it appears like i said earlier:

CA CB
a 1
2
3
b 4
5

So, one item in column A will have multiple corresponding values in column B
and i want all of them to show when the formula is in action.

Ashish, i went to the link you sent me but it just says how you do what i
want rather than give me an example, being new to this i really am finding it
hard to understand.

thanks again.

 

[Zak]

Hi,

I have tried a simpler way to bring across values, instead of using the
formula below i have just used a simple vlookup.

I have put the multiple values to be returned into one cell rather than a
few different ones, which just complicated things.

I have another problem now - the vlookup returns the values required fine
but i require this cell to be editable so the user can delete or add to what
was returned. But when clicking into the cell i just see the formula, no
obvoius way to edit the contents of the cell - is there a way i can edit the
contents of a cell in which a vlookup formula has been written?

awaiting your response.

many thanks.