Returning multiple values with vlookup (array)

[rudders]

Hi All,


Development on my last post, where Hui directed me to a great site and I found my own detailing how to return multiple values with an array formula


(http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/)


The formula I use now is:

{=INDEX(Data!$P$2:$P$4866, SMALL(IF($A$2=Data!$G$2:Data!$G$4866, ROW(Data!$G$2:Data!$G$4866)-MIN(ROW(Data!$G$2:Data!$G$4866))+1, ""), COLUMN(A1)))}

It works exactly how it should....however I need to make a couple of changes.


1: I need to make it not return any duplicate values. So if "Apples" occurs 5 times, I only want to see it once (without going back and manually sorting/removing data)


2: Is it at all possible to make this all occur in one cell? Or would i just have to use Concatenate to join it all up.


Any help would be greatly appreciated,


Thanks in advance.

I'll keep playing around with it in the meantime

 

[rudders]

Somehow I missed an article in that link that looks like it can help me solve problem 1...

 

[rudders]

Ok so I am now able to list unique values vertically...but am wondering how to rearrange it to do so horizontally? I can make it list horizontally, with a different formula but that one returns duplicates.

=INDEX(Data!$P$2:$P$4866, SMALL(IF(($A$11=Data!$G$2:Data!$G$4866)*(COUNTIF($G$10:G10, Data!$P$2:$P$4866)=0), ROW(Data!$G$2:Data!$G$4866)-MIN(ROW(Data!$G$2:Data!$G$4866))+1, ""), 1))

Input cell for this formula is A11, and the above is in cell H11, and copied down 3 rows. On the Data spreadsheet, column P is where the data i want to find is.